CODEWITHSUNDEEP
excelif-statementfindexcel-2013vba
Peter Mogford
Peter Mogford

Reputation: 496

Find and select value in range

I am hoping this is reasonably easy to answer. I have a userform with a textbox and command button used as a password entry.

What i want to know is, can i wrap/edit the below macro into an if statement that checks to see if the value inputted into textbox1is in range first? If the value IS in the list then run the below macro, if NOT then return error message. This will run off the submit command button.

Dim FindString As String
Dim Rng As Range
FindString = Password.TextBox1.Value
If Trim(FindString) <> "" Then
    With Sheets("CC Number").Range("A:A")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
        End If
    End With
End If

Upvotes: 0

Views: 102

Answers (1)

CMArg
CMArg

Reputation: 1567

This may be close to your needs. When UserForm initializes, submit button is disabled. When users starts entering his/her password, you check if what is being entered is registered in a worksheet named "Passwords". If entered characters are in the list, submit button is enabled. Submit button will run your code. Edition: I added the else statement, for disableling submit button otherwise (and the critical Exit Sub, that I forgot in my first answer). And, just for fun, you can add a label (Label1) next to the input Textbox for the user to be aware of whats happening while typing...

Private Sub UserForm_Initialize()
CommandButton1.Enabled = False
End Sub

Private Sub TextBox1_Change()
Dim sPW As String
Dim lLastRowPasswords As Long
Dim i As Integer

lLastRowPasswords = Worksheets("Passwords").Cells(Rows.Count, 1).End(xlUp).Row
sPW = TextBox1.Text

For i = 1 To lLastRowPasswords
    If Worksheets("Passwords").Cells(i, 1).Value = sPW Then
        CommandButton1.Enabled = True
        Label1.Caption = "Got it!"
        Label1.Font.Bold = True
        Label1.ForeColor = RGB(0, 102, 0)
        Exit Sub
    Else
        CommandButton1.Enabled = False
        Label1.ForeColor = RGB(179, 0, 0)
        Label1.Font.Bold = True
        Label1.Caption = "Unregistered password"
    End If
Next i

End Sub

Upvotes: 1

Related Questions