EXCEL VBA Filter user input

Question

MY inputbox allow user to input a value into a cell. Therefore I wish to filter some input like =SUM(A:A) and ' which will show nothing, and other formula or possibly entry that will affect the actual value. Thanks in advance.

Private Sub testInputBox_Click()
    Dim x As String
    Dim y As String
    Dim yDefault As String
    Dim found As Range

    x = InputBox("Enter Parts No.", "Edit Description")

    If (x <> "") Then
        If (WorksheetFunction.CountIf(Worksheets("Sheet1").Range("E2:E27"), x) > 0) Then
            Set found = Worksheets("Sheet1").Range("E2:E27").Find(x, LookIn:=xlValues)
            yDefault = found.Offset(0, 1).Text
            y = InputBox("Amend Description", "Edit Description", yDefault)
        Else
            MsgBox ("Not found!")
        End If

        If (y <> "") Then 'Filter should be done here
            If MsgBox("Proceed to edit?", vbYesNo, "Confirmation") = vbNo Then
            Else
                found.Offset(0, 1).Value = CStr(y)
            End If

        End If
    End If
End Sub

what type of inputbox you use? show us that simple part of your code and how you enter data later to cell — Kazimierz Jawor
– Kazimierz Jawor, Commented Apr 23, 2013 at 6:00

Kazimierz Jawor · Accepted Answer · 2013-04-23 06:15:31Z

1

You could use different attempts to filter some or all required values. Keep in mind that you y variable is string type. Therefore here are some of ideas with some comments:

'tests for starting characters
If y <> "'" And y <> "=" Then
    'test for formulas
    If UCase(Left(y, 4)) <> "=SUM" Then
        'test for any string within other string
        If InStr(1, y, "sum", vbTextCompare) = 0 Then
            '...here your code
        End If
    End If
End If

you could combine them all into one if...then statement using and or or operators.

answered Apr 23, 2013 at 6:15

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

4 Leave Cover Over a year ago

Thanks to your answer! I guess that filter '=' will be enough to prevent user input formula into the cell. But I was wondering why ' will not show if it is the first character.

Collectives™ on Stack Overflow

EXCEL VBA Filter user input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related