Conditional Formatting on Userform in VBA

[Bealmj]

I have a user form that has a text box, and if a value is entered that matches a value in a column on another sheet I want the text box background color to turn red. If I do it in a cell on a worksheet I would use a VLookup:

=IF(ISNA(VLOOKUP($C$5,Revocation!$F$2:$F$271,1,FALSE)), "", "REVOKED")

How can I accomplish this using a Userform where the Text box identifies to the user that the value in this text box matches a value in the list so they are immediately notified that their ID that they just entere has been revoked?

Here is what I tried but it is not working:

Private Sub IDTextBox_Change()
If IDTextBox.Value = Sheet2.Range("F2:F271") Then
IDTextBox.BackColor = vbRed
Else
IDTextBox.BackColor = vbWhite
End If
End Sub

 

[dmt32]

Hi,
welcome to forum

You have not made it clear if the data in Column F is Text or Numeric?

try following

For text

Private Sub IDTextBox_Change()
    Dim m As Variant
    m = Application.Match(Me.IDTextBox.Value, Sheet2.Columns(6), 0)
    Me.IDTextBox.BackColor = IIf(Not IsError(m), vbRed, vbWhite)
End Sub

or if your data is Numeric only

Private Sub IDTextBox_Change()
    Dim m As Variant
    m = Application.Match(Val(Me.IDTextBox.Value), Sheet2.Columns(6), 0)
    Me.IDTextBox.BackColor = IIf(Not IsError(m), vbRed, vbWhite)
End Sub

and see if either will do what you want

Dave

 

[Bealmj]

Private Sub IDTextBox_Change() Dim m As Variant m = Application.Match(Me.IDTextBox.Value, Sheet2.Columns(6), 0) Me.IDTextBox.BackColor = IIf(Not IsError(m), vbRed, vbWhite) End Sub

As a newbie to VBA, can you tell me the purpose for the "m" and the "Me."? Are these aliases? Is it some sort of fully qualified naming convention? I'm getting 424 runtime error object required.

 

[Bealmj]

Hi,
welcome to forum

You have not made it clear if the data in Column F is Text or Numeric?

try following

For text

Private Sub IDTextBox_Change()
    Dim m As Variant
    m = Application.Match(Me.IDTextBox.Value, Sheet2.Columns(6), 0)
    Me.IDTextBox.BackColor = IIf(Not IsError(m), vbRed, vbWhite)
End Sub

or if your data is Numeric only

Private Sub IDTextBox_Change()
    Dim m As Variant
    m = Application.Match(Val(Me.IDTextBox.Value), Sheet2.Columns(6), 0)
    Me.IDTextBox.BackColor = IIf(Not IsError(m), vbRed, vbWhite)
End Sub

and see if either will do what you want

Dave

 

[Bealmj]

It didn't work but I think it will if I can get the answer to my second follow up question. Waiting for your reply. Thank you in advance Dave!

Michael

 

[Bealmj]

It didn't work but I think it will if I can get the answer to my second follow up question. Waiting for your reply. Thank you in advance Dave!

Michael

Actually it did work!!! Thank you very much!!!

 

[dmt32]

Actually it did work!!! Thank you very much!!!

One of the versions will work but which one you use is dependant on the type of data you are searching for in Column F.

To answer your questions

- m is just a declared variant variable used to hold the returned result of match method. If no match is found an error is returned - variants hold all data types including error values which is needed for this line of code to execute correctly - IIf(Not IsError(m), vbRed, vbWhite)

- Me keyword in Excel VBA allows you to refer (in the appropriate code page only) to either the current worksheet, workbook, or userform without having to use the objects name. So instead of writing code with your forms name like Useform1.TextBox1.Text you can just use Me keyword - Me.TextBox1.Text. It also means that if you change your forms name you do no need to update all your code.

Most answers to your questions can be found in the VBA helpfile which is always worth searching.

Dave