• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

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
0
RichardAtk
Asked:
RichardAtk
  • 2
1 Solution
 
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
 
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
 
RichardAtkAuthor Commented:
Perfect Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now