Solved

Excel VB Help

Posted on 2016-11-03
4
39 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 48

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 28

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 28

Expert Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now