Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

show message if item is not matched

0

Hello 

I  try to  show  message  when  I  write  item in combobox1  should match with column B in sheet . if  the  item is  not  matched when write  in combobox1 ,then should  show  the  message and  should  clear the  combobox1 after close  message 

Private Sub ComboBox1_Change()

    Dim ws As Worksheet, rng As Range, cb
    Set ws = Sheets("sheet1")
   cb= Me.ComboBox1.Value
    If cb<> "" Then
        Set rng = ws.Columns(2).Find(cb, LookAt:=xlWhole)
        If rng <> cb Then
        MsgBox "not matched"
    End If
 Me.ComboBox1.Value=""

any chance  to  do  that.,please?

Answer
Discuss

Answers

0
Selected Answer

Maklil 

Initial answer (untested since I'm on my mobile phone so no macros run in Excel)...

I've never launched a MsgBox from a UserForm but Range.Find returns a Range object so try this test instead:

If rng is Nothing Then

You also need to move:

Me.ComboBox1.Value=""

before End If (or any value will be overwritten!) 

Hope this works but if not please let me know and I'll look later. 

Discuss

Discussion

thanks John for  guiding  me . it  works  greatly !
MAKLIL (rep: 34) Aug 14, '22 at 6:29 pm
Glad it worked and thanks for selecting my answer Maklil.
John_Ru (rep: 6142) Aug 15, '22 at 2:07 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login