Macro to run only on select text

[andrewb90]

I am trying to run a macro but only want it to execute if the text matches.

here is what I am currently using:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address(False, False) = "B2" Then
        If Len(Target.Value) >= 2 And Len(Target.Value) <= 2 Then
            MsgBox "Please proceed to fill out the order for " & Target.Value & "." & vbNewLine & "Have a nice day."
            Rows("4:58").Hidden = False
        Else
            MsgBox "Type in FG  for Forest Grove, or Tu for Tualatin or Gr for Gresham. Nothing Else", vbExclamation
            Rows("4:58").Hidden = True
        End If
    End If
End Sub

What I would like is to have is if somebody types in 'FG' then a message box appears if 'Tu' or 'Gr' are typed in the same thing would happen, then the appropriate cells would hide/unhide.

 

[Joe4]

Do you mean something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = "$B$2" Then
        If (Target = "FG") Or (Target = "Tu") Or (Target = "Gr") Then
            MsgBox "Please proceed to fill out the order for " & Target & "." & vbNewLine & "Have a nice day."
            Rows("4:58").Hidden = False
        Else
            MsgBox "Type in FG  for Forest Grove, or Tu for Tualatin or Gr for Gresham. Nothing Else", vbExclamation
            Rows("4:58").Hidden = True
        End If
    End If
    
End Sub

 

[andrewb90]

Pretty close, but I was trying to see if it was possible to have a different message appear depending on what was typed in. then the rows would unhide regardless of the text.

 

[Joe4]

Pretty close, but I was trying to see if it was possible to have a different message appear depending on what was typed in.

Sure its possible. Something like this (chanegs your message boxes to suit).

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$2" Then
        Select Case Target.Text
            Rows("4:58").Hidden = False
            Case "FG"
                MsgBox "Say this if FG"
            Case "Tu"
                MsgBox "Say this if Tu"
            Case "Gr"
                MsgBox "Say this if Gr"
            Case Else
                MsgBox "Type in FG  for Forest Grove, or Tu for Tualatin or Gr for Gresham. Nothing Else", vbExclamation
                Rows("4:58").Hidden = True
        End Select
    End If
    
End Sub

 

[andrewb90]

I seem to get an error whenever I type into B2:

Compile Error:
Statements and labels invalid between select Case and first Case

 

[Joe4]

OK, let's just move that hidden rows line up a few, and it should work fine:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = "$B$2" Then
        Rows("4:58").Hidden = False
        Select Case Target.Text
            Case "FG"
                MsgBox "Say this if FG"
            Case "Tu"
                MsgBox "Say this if Tu"
            Case "Gr"
                MsgBox "Say this if Gr"
            Case Else
                MsgBox "Type in FG  for Forest Grove, or Tu for Tualatin or Gr for Gresham. Nothing Else", vbExclamation
                Rows("4:58").Hidden = True
        End Select
    End If
    
End Sub

 

[andrewb90]

It does work, however when any incorrect text is entered, the cells all reappear and the message pops up and then they hide again. Any way to solve this?

 

[Joe4]

OK, the message box is interrupting the procedure halfway through. So let's use variable and capture whether or not we should hide the rows and capture the message box statement, and wait until the very end to apply them, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim myHide As Boolean
    Dim myMsgBox As String
 
    If Target.Address = "$B$2" Then
        myHide = False
        Select Case Target.Text
            Case "FG"
                myMsgBox = "Say this if FG"
            Case "Tu"
                myMsgBox = "Say this if Tu"
            Case "Gr"
                myMsgBox = "Say this if Gr"
            Case Else
                myMsgBox = "Type in FG  for Forest Grove, or Tu for Tualatin or Gr for Gresham. Nothing Else"
                myHide = True
        End Select
        Rows("4:58").Hidden = myHide
        MsgBox myMsgBox
    End If
    
End Sub

 

[andrewb90]

Thank you so much! I don't get what you did on this last part, but it works!!

 

[Joe4]

Note the last two lines (besides the "End" statements):

        Rows("4:58").Hidden = myHide
        MsgBox myMsgBox

These are the two lines that physically hide/unhide our rows, and return our message box to the screen. Before, they were interspersed into our code, so they could occur at different points/times. I just used variables to "capture" what we wanted to do, and waited until the very end to apply it, after it got through all the steps, so we don't get caught "mid-step" like before.