Excel VB Help

Hi have this in Excel the first  lump of code works but I guess I need to do something like else if  - or put all the if formulas in the first section ??   Basically want sheets to be hidden or shown depending on the value in K3.   As below the UK Trade bit works the rest not.

Private Sub Worksheet_Change(ByVal Target As Range)
If [K3] = "UK Trade" Then
Sheets("Terms UK").Visible = True
Else
Sheets("Terms UK").Visible = False
End If
End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)
If [K3] = "US Trade" Then
Sheets("Terms US").Visible = True
Else
Sheets("Terms US").Visible = False
End If
End Sub

Private Sub Worksheet_Change3(ByVal Target As Range)
If [K3] = "CAD Trade" Then
Sheets("Terms CAD").Visible = True
Else
Sheets("Terms CAD").Visible = False
End If
End Sub

Thanks
RichardAtkAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The above procedure can be written as below.....
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("K3")) Is Nothing Then
    Sheets("Terms CAD").Visible = False
    Sheets("Terms UK").Visible = False
    Sheets("Terms US").Visible = False
    Select Case Target.Value
        Case "CAD Trade"
            Sheets("Terms CAD").Visible = True
        Case "UK Trade"
            Sheets("Terms UK").Visible = True
        Case "US Trade"
            Sheets("Terms US").Visible = True
    End Select
 End If
 End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, [k3]) Is Nothing Then
    Select Case [k3]
        Case "CAD Trade"
            Sheets("Terms CAD").Visible = True
            Sheets("Terms UK").Visible = False
            Sheets("Terms US").Visible = False
        Case "UK Trade"
            Sheets("Terms CAD").Visible = False
            Sheets("Terms UK").Visible = True
            Sheets("Terms US").Visible = False
        Case "US Trade"
            Sheets("Terms CAD").Visible = False
            Sheets("Terms UK").Visible = False
            Sheets("Terms US").Visible = True
    End Select
 End If
 End Sub

Open in new window

Regards
0
 
RichardAtkAuthor Commented:
Perfect Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.