VBA if in text is in textbox then do something

Question

I've written the following code so that if a certain text exists in my listbox and "ok" button is clicked a certain thing is done.

Private Sub CommandButton3_Click()

If (Me.ListBox2.Text) <> ("PA") Then

Call macro1

ElseIf (Me.ListBox2.Text) <> "menu" Then

Sheets("menu").Visible = xlSheetVisible

Worksheets("menu").Activate

Else
 MsgBox "Nothing is selected"

End If 

End Sub

The problem is that when "ok" is clicked all events are still carried out even if the specified text isn't in the textbox.

<> is not equal to operator. In other words, <> is not equal to operator. — dotNET
– dotNET, Commented Nov 17, 2017 at 3:18
I tried replacing it with an "=" operator but now I get "Nothing is selected" as an output no matter which text is in the textbox. — AIS
– AIS, Commented Nov 17, 2017 at 3:21

dotNET · Accepted Answer · 2017-11-17 03:54:24Z

1

You probably want to use = operator, and not <> operator. Also note that ListBox.List(i) is the correct way of getting selected item for single selection mode:

Private Sub CommandButton3_Click()
  Dim SelectedItem = ListBox1.List(ListBox1.ListIndex)

  If SelectedItem = "PA" Then   
    Call macro1    
  ElseIf SelectedItem = "menu" Then  
    Sheets("menu").Visible = xlSheetVisible    
    Worksheets("menu").Activate    
  Else
    MsgBox "Nothing is selected"    
  End If     
End Sub

Edit

Following your comment, you can create a function that looks for the existence of that item:

Private Function TextExists(text as String) as Boolean
  Dim i as Long
  For i = 0 To ListBox1.ListCount - 1
    If ListBox1.List(i) = text Then
      TextExists = True
      Exit Function
    End If
  Next

  TextExists = False
End Function

And then use this function in the main code like this:

Private Sub CommandButton3_Click()
  If TextExists("PA") Then   
    Call macro1    
  ElseIf TextExists("menu") Then  
    Sheets("menu").Visible = xlSheetVisible    
    Worksheets("menu").Activate    
  Else
    MsgBox "Nothing is selected"    
  End If     
End Sub

N.B. I have written this manually here, without an IDE. Please check for indexes and other little things.

edited Nov 17, 2017 at 3:54

answered Nov 17, 2017 at 3:25

dotNET

35.8k29 gold badges177 silver badges277 bronze badges

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

3 Comments

AIS Over a year ago

This works if the text is selected but I want it so that if the text "exists" in the listbox then do something.

AIS Over a year ago

Thank you, that works perfect. Just missing " Dim i as Integer " at the top.

YowE3K Over a year ago

@AIS Use Dim i As Long rather than Dim i As Integer. (Excel uses Long data types internally now, so it reduces the need for a lot of implicit type conversions.)

Collectives™ on Stack Overflow

VBA if in text is in textbox then do something

1 Answer 1

Edit

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related