Solved How to unhide a specific worksheet from a dropdown list

May 8, 2013 at 11:58:18
Specs: Windows 7
Hi there,
I have a workbook with an increasing amount of worksheets. Each worksheet is titled by a persons name, except for the 'Dashboard'
I have then 'hidden' the worksheets
I have created a dropdown list of all the ws sheet names
I would like to look up the name of a person and then that ws to appear
I have been able to do this via recording a macro but by doing it this way, I am only able to do the ws that I used to record the macro
The dropdown list is in a specific cell on the 'Dashboard'
How can I when selecting a name in that cell, then via a control button, go to that ws

See More: How to unhide a specific worksheet from a dropdown list

Report •

✔ Best Answer
May 9, 2013 at 02:58:37
If you want something in VBA, here is a very quick example, but obviously you will need to modify to work for your spreadsheet

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
    
        Dim WorksheetName
        WorksheetName = Target.Value
        
        Sheets(WorksheetName).Visible = True
        Sheets(WorksheetName).Select
            
    End If

End Sub



#1
May 8, 2013 at 13:03:30
I have then 'hidden' the worksheets

Why have you hidden the worksheets?

What is to prevent anyone from simply UN-hiding the sheets?

If the sheets were NOT hidden you could use a simple =HYPERLINK() function to jump to the desired sheet.
Unfortunately, you can not =HYPERLINK() to a hidden sheet.

MIKE

http://www.skeptic.com/


Report •

#2
May 8, 2013 at 13:58:48
It seems like a good way to tidy up the tabs at the bottom of the ws. I will rarely look at the hidden sheets, but if i want to look at a specific sheet, then referencing by the dropdown list would be an efficient way of doing this.
I am pretty close i think, but cant get the right instruction to go to the sheetname referenced in the dropdown box ( cell B5)

Any ideas?


Report •

#3
May 8, 2013 at 14:12:34
Are you using VBA? if so, post what you have so we can take a look!

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

Related Solutions

#4
May 8, 2013 at 14:28:51
If you have Excel 2007,
and do NOT want to look at a group of sheet tabs,
then try this:

Click the Microsoft Office button
Click on the Excel Options button to bring up the Excel Options dialog box (at the bottom of window)
Click on the Advanced button in the left hand pane
Scroll down to the section headed: Display Options for This Workbook
Un-check the line that read: Show Sheet Tabs

The Tabs in your workbook will magically disappear.

Edit Added:

Sorry, forgot to mention that using this method you CAN use the =HYPERLINK() function to jump from sheet to sheet no VBA required.

MIKE

http://www.skeptic.com/


Report •

#5
May 9, 2013 at 02:58:37
✔ Best Answer
If you want something in VBA, here is a very quick example, but obviously you will need to modify to work for your spreadsheet

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
    
        Dim WorksheetName
        WorksheetName = Target.Value
        
        Sheets(WorksheetName).Visible = True
        Sheets(WorksheetName).Select
            
    End If

End Sub


Report •

#6
May 10, 2013 at 10:26:36
Alwayswillingtolearn, sorry I haven't got back to you earlier but have had to go to work! ha.
The vba you posted looks great but as a newbie to vba, I think I need this as a 'sub' and not a 'private sub'. I am having difficulty in changing the instruction' , probably a very easy thing but help would be appreciated.
The idea behind this is a form control button to view a specific worksheet. I have the vba to close the selected ws and return to the 'Dashboard'

Report •

#7
May 13, 2013 at 00:59:37
Hi Tony,

Try this:

1) Open Excel
2) Create a drop down in Cell A1 (using data validation list)
a) Click Data
b) Click Data Validation
c) Allow Value = 'List'
d) Source Value will be your sheet names. You can either enter them in this field
or enter them into a column on a sheet and link the source here
3) Rename your sheets accordingly
4) Open VBA Editor (Ctrl+F11) or Developer tab > Visual BAsic
5) Double click 'Sheet1' from the VBA Project window on the left
6) Copy paste the code provided.

Doh i just realised that you are using a userform? if so, you can rename the sub and pass it the sheetname (combo Value) Then unhide the selected sheet and hide the rest, something like this:

Private Sub UserForm_Activate()
    ComboBox1.AddItem "Sheet2", 0
    ComboBox1.AddItem "Sheet3", 1
    
End Sub

Private Sub ComboBox1_Change()
    
    UnhideSheets ComboBox1.Value
    
End Sub

Sub UnhideSheets(SheetName As String)
        
    Dim ws As Worksheet
    
    Sheets(SheetName).Visible = True
    
    For Each ws In ThisWorkbook.Worksheets
    
        If ws.Name <> SheetName Then
            ws.Visible = xlSheetHidden
        End If
    
    Next
        
End Sub


Report •

#8
May 13, 2013 at 10:27:02
Hi there, seem to be getting a bit ott with the formula.
The following is a recorded VBA of what I almost am after

Sub showscorecards()
'
' showscorecards Macro
'

'
    Range("B24").Select
                  Sheets("DASHBOARD").Select
    Sheets("Tony Gibb").Visible = True
End Sub

From the above, this is what happens
Range B24 is a dropdown list of all the worksheet names
The second line is probably insignificant as I have to click on a tab to show the hidden worksheets
The third line is then the worksheet selected to unhide
What I require is for Range B24 to be confirmed as the worksheet name, and then

Sheets(=worksheetname).Visible = True or something along these lines.

Is this possible?

Forgot to mention, I am using a forms control button to initiate the VBA


Report •

#9
May 14, 2013 at 01:56:17
The code i dont think is ott, obviously we dont have access to your workbook, so therefore what we give you in terms of code is a guide for you to adjust as it suits. My code can very easily be modified to cater your dropdown list, i guess all you need to do is follow my instructions and play with the code.

Report •

#10
May 15, 2013 at 12:40:14
hi there,
Thanks very much for your help. I came up with the following which does exactly as required

Sub View_Scorecards()
    
    WorksheetName = Range("B24")
        
        Sheets(WorksheetName).Visible = True
        Sheets(WorksheetName).Select

End Sub

I am very knew to VBA and learning by trial and error. Again thankyou for your help


Report •

Ask Question