Drop down box using sheet names

[Mattyads2011]

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?

 

[Mattyads2011]

Hi Trevor.

The code is working fine, thank you very much!

The only thing it is not doing is excluding certain sheets (the ones i listed above) am I forgetting to do something do you think?

 

[Trevor G]

Did you use this code

http://www.mrexcel.com/forum/showpost.php?p=2787994&postcount=8

I created some sheets with there names on and it worked for me.

 

[Mattyads2011]

Yeah, I used that code to the letter, it gives me the drop down box, allows me to jump to the sheet I select, I just need to exclude a few sheets from the list now.

 

[Trevor G]

Have you looked at the current names in the array?

[FONT=Courier][COLOR=#007f00]'Add more search strings if you need[/COLOR][/FONT]
[FONT=Courier]   myStrings = Array("Home Screen", "Database", "Talent Charts", "Sheet Names")[/FONT]
[FONT=Courier][COLOR=#007f00]'Home Screen, Database, Talent Charts[/COLOR][/FONT]

And I hope you aren't using this anymore.

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

 

[Mattyads2011]

Ah yes I was, thank you.

However, I now have no options in the drop down menu.

 

[Trevor G]

Have you created a worksheet called Sheet Names

 

[Mattyads2011]

I have

 

[Trevor G]

Can you post back all of the code you are using, as it worked fine for me.

 

[Mattyads2011]

Private Sub ComboBox1_Change()
End Sub
Private Sub CommandButton1_Click()
Sheets(Me.cboSheetNames.Value).Select
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub

Private Sub cboSheetNames_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", "What's Next", "Talent Charts", "Useful Links", "Collections Total", "AA Total", "CA Total", "CB Total", "AB Total", "LookUpLists", "Word", "Sheet Names", "Talent Menu")
'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

 

[Trevor G]

This works fine for me, but lets see if you followed the steps.

Open your workbook and use Alt + F11 to go into VBA
Left side select your form
Click Once on the Command Button, In the properties change the name to cmdOK
Click back on the form
Double click the form (that will open the code screen), highlight everything you see and delete it, yes delete it.
Next, select first drop down at the top of the screen on the left and Select UserForm, then select the drop down to the right and select Initialize
Copy and Paste the code below.

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", "What's Next", "Talent Charts", "Useful Links", "Collections Total", "AA Total", "CA Total", "CB Total", "AB Total", "LookUpLists", "Word", "Sheet Names", "Talent Menu")
'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

Then Press F5 to test the code, if should load the form in the workbook and fill the combo box, I think when you copied the code originally you didn't select the drop downs to get the correct parts.

Once you see the form and check the Combo and it should show you the names, then close the form.

Next place the code into the cmdOK button but double clicking the button (You might have to go to the left back to the form then double click)

Paste in this code

Sheets(Me.cboSheetNames.Value).Select
Unload Me

Then all you need to do is test the form.

You should still have the code in the module sheet to show the form.

Post back once done.