Solved

Excel VB Help

Posted on 2016-11-03
4
51 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 49

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 29

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 29

Expert Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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 Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

776 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