PDA

View Full Version : Macro Code for referring to a value in a drop down list



86buickgn
11-18-2011, 01:00 PM
First time poster here, but I did use the search tool.

I have written a code that hides worksheets based on what cell is clicked. Now, I need to be able to hide/unhide worksheets based on the option chosen in a drop-down list.

Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myPick As String
'This code go's in the List Sheet, (Sheet1) module.
'Sheet1 is the list sheet, in cells A2:A5
'The case names, like: 1st, 2nd, ...
'are in the Sheet1 List, in cells A2:A5.
'When one of these cells is selected, its case code runs.
myPick = Selection.Value
Select Case myPick
Case "1st"
'Below is the Sheet Name you want to un-hide.
Sheets("Sheet2").Visible = True
'Below are the Sheet Names you want to hide.
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
Sheets("Sheet6").Visible = False
Sheets("Sheet7").Visible = False
Sheets("Sheet8").Visible = False
Sheets("Cameron").Visible = False
Sheets("Webco").Visible = False
Sheets("Jag").Visible = False
'Make the Sheet below active.
Sheets("Sheet2").Select

Case Else
'If any other cell selected, do nothing.
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
Sheets("Sheet6").Visible = False
Sheets("Sheet7").Visible = True
Sheets("Sheet8").Visible = True
Sheets("Cameron").Visible = False
Sheets("Webco").Visible = False
Sheets("Jag").Visible = False
Sheets("Sheet1").Select
Exit Sub
End Select
End Sub

What I need is for the worksheets "Cameron" and "Webco" to hide when I choose "Jag" in the drop down list.

Is this possible?

Rob342
11-18-2011, 02:11 PM
welcome to vbax
Please use the VBA Buttons & post you code between the buttons, this helps others to read your code correctly.

set the value of the list box for eg if the listbox is in cell D10 then off the cuff

With Worksheets(Sheet1")
If Range("D10").Value="jag" then
Worksheets("Cameron").Visible = False
Worksheets("Jag").Visible = False
end if
End with

86buickgn
11-21-2011, 07:59 AM
How can I modify that code so that excel will recognize the contents of a cell without clicking off the cell and clicking back on it?

That code will work Rob342, just as one of my "Case" codes that I wrote but they both require you to choose the option from a drop down list, click out of the drop down cell, then click back on it the cell before Excel recognizes it.

Rob342
11-21-2011, 04:17 PM
How is it going to recognise the cell value without selecting the item.
There is no cell value until you press return or enter.

Rob

Simon Lloyd
11-21-2011, 05:36 PM
How can I modify that code so that excel will recognize the contents of a cell without clicking off the cell and clicking back on it?

That code will work Rob342, just as one of my "Case" codes that I wrote but they both require you to choose the option from a drop down list, click out of the drop down cell, then click back on it the cell before Excel recognizes it.Use the Worksheet_Change event instead :)