open worksheet with a macro (combobox)

[matthiasmorath]

Hi, well as usual simple things you can't find in the user manual
I was looking forward to create a simple macro using a combobox
to select one of 100 worksheet in a workbook.

How can i open a worksheet is there any kind of command for doing so,
how can i put all worksheets into the combobox without programming, or changing the code everytime (user creates a new worksheet for example)

Thank you very much

Matthias

[Norman Jones]

Hi Matthias,

Try assigning the following macro, posted by Jim Rech, to a toolbar button:

'==================>>
Sub ShowSheetList()
'Jim Rech
On Error Resume Next
If ActiveWorkbook.sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs"). _
ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs"). _
Controls("More Sheets...").Execute
End If
On Error GoTo 0
End Sub
'<<==================

For an alternative approach, try a suugestion by Bob Phillips:

'=============>>
Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption
Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton

Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = Activesheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg
.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13

Next i

.buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.buttons("Button 2").BringToFront
.buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .show Then
For Each cb In thisDlg.OptionButtons
If cb.value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
.Delete

End With

End Sub
'<<=============

---
Regards,
Norman


"matthiasmorath"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi, well as usual simple things you can't find in the user manual
> I was looking forward to create a simple macro using a combobox
> to select one of 100 worksheet in a workbook.
>
> How can i open a worksheet is there any kind of command for doing so,
> how can i put all worksheets into the combobox without programming, or
> changing the code everytime (user creates a new worksheet for example)
>
>
> Thank you very much
>
> Matthias
>
>
> --
> matthiasmorath
> ------------------------------------------------------------------------
> matthiasmorath's Profile:
> http://www.excelforum.com/member.php...o&userid=34970
> View this thread: http://www.excelforum.com/showthread...hreadid=547071
>