Drop down box using sheet names

Mattyads2011 · Jul 13, 2011

Hi All.

I need to create a button that displays a drop down box that allows a user to jump to a specific worksheet in an excel document.

The main sticking point is that there may be new sheets added with various names throughout the year that, once created, will need to form part of the drop down menu.

Can this even be done?

Trevor G · Jul 13, 2011

Create a userform (in the vba screen, use Alt + F11, then Insert UserForm) and add a combo box and name it something like cboSheetNames

Double click the form and from the code screen at the top select on the right Initialize then add this code.

Dim wksht As Worksheet
For Each wksht In Worksheets
Me.cboSheetNames.AddItem wksht.Name
Next

This will give you a list in the drop down of all the sheet names and when you add more sheets it will then show them in the list the next time the form is run.

To run the form you would Insert a Module sheet and then place in a macro

Sub OpenSampleForm()
frmSample.Show
End Sub

Add a command button in the form to go to the worksheet. Double click the button and add this code

Private Sub cmdOK_Click()
Sheets(Me.cboSheetNames.Value).Select
Unload me
End Sub

VoG · Jul 13, 2011

Or just right click on the navigation arrows, bottom left.

Sektor · Jul 13, 2011

Right-click any arrow to the left side of sheet tabs.

Mattyads2011 · Jul 13, 2011

Thanks guys.

Once the workbook has been finished the sheet tabs wont be displayed as I do not want everyone using it to have access to all sheets so I am using a series of menu's for users.

Thanks very much for the code, I havent tried it yet, but can I ask, if i wanted to exclude a few sheets from the selection could I do that? If so how please?

Trevor G · Jul 13, 2011

You could get all the sheet names into a worksheet and then run some code to delete the names you don't want to appear then fill the combo box with the list.

How does that sound?

Please give some idea of sheet names you don't want to include.

If OK post back and a solution will be provided.

Mattyads2011 · Jul 13, 2011

Hi Trevor.

I have some code that a user can 'add' a new page any time they want (actually it is just copying a sheet and the user can rename it to refer to the member of staff)

I then need this drop down box to allow that user to go back to the sheet they created in order to edit the information on there whenever they need to, I thought having a drop down menu would probably be the most user friendly way, as i have said before i cannot allow users access to the sheet tabs for various reasons.

Sheet names I do not want to appear in the list are, but not limited to, are; Home Screen, Database, Talent Charts

I hope this makes sense and thank you for your time.

Trevor G · Jul 13, 2011

Ok this will need more code. Create the form with the objects as mentioned, then add an extra sheet called, Sheet Names. Next behind the form on the initialise area you will need to add the following code

Private Sub UserForm_Initialize() Dim ws As Worksheet Dim S As Integer For S = 1 To Worksheets.Count With Worksheets("Sheet Names") Set ws = Worksheets(S) .Cells(S, 1).Value = ws.Name End With Next S Dim calcmode As Long Dim ViewMode As Long Dim myStrings As Variant Dim FoundCell As Range Dim I As Long Dim myRng As Range Dim sh As Worksheet With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can also use Sheets("MySheet") Set sh = ActiveSheet 'We search in column A in this example Set myRng = sh.Range("A:A") 'Add more search strings if you need myStrings = Array("Home Screen", "Database", "Talent Charts", "Sheet Names") 'Home Screen, Database, Talent Charts With sh 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'We will search the values in MyRng in this example With myRng For I = LBound(myStrings) To UBound(myStrings) Do Set FoundCell = myRng.Find(What:=myStrings(I), _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) 'Use xlPart If you want to search in a part of the FoundCell 'If you use LookIn:=xlValues it will also delete rows with a 'formula that evaluates to "Ron" If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If Loop Next I End With End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = calcmode End With Range("A1").Select Do Until ActiveCell.Value = "" Me.cboSheetNames.AddItem ActiveCell.Value ActiveCell.Offset(1, 0).Select Loop Application.ScreenUpdating = True End Sub

Mattyads2011 · Jul 13, 2011

Thanks Trevor.

Something has come up at work so may not get to try this today now but it certainly looks good. I might have 1 or 2 questions tomorrow if that is ok?

Thanks again.

Trevor G · Jul 13, 2011

Ask away when you ready.

Drop down box using sheet names

Mattyads2011

New Member

Excel Facts

Trevor G

Well-known Member

VoG

Legend

Sektor

Well-known Member

Mattyads2011

New Member

Trevor G

Well-known Member

Mattyads2011

New Member

Trevor G

Well-known Member

Mattyads2011

New Member

Trevor G

Well-known Member

Similar threads

Forum statistics

Share this page

Drop down box using sheet names

New Member

Excel Facts

Well-known Member

Legend

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock