Solved

Excel VB Help

Posted on 2016-11-03
4
54 Views
Last Modified: 2016-11-03
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
Comment
Question by:RichardAtk
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41871875
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
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41871883
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
 

Author Closing Comment

by:RichardAtk
ID: 41871888
Perfect Thanks
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41871892
You're welcome. Glad to help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question