CODEWITHSUNDEEP
vbaexcel
AIS
AIS

Reputation: 13

VBA if in text is in textbox then do something

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.

Upvotes: 0

Views: 2097

Answers (1)

dotNET
dotNET

Reputation: 35450

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.

Upvotes: 1

Related Questions