Command Button

[Gee]

I'm real new at programing in Excel...Access I've done.
I need to know the "on click" code for a command button to
open a spreadsheet in a workbook. I've got a table of
contents and I want to be able to click the button to go
to the proper sheet within that book. Say the sheet is
named 24.
Thank you in advance for your help.
Gee

 

[Guest]

Sheets("24").Activate ' Excel doesnt allow numbers for sheetnames
S
Sheets("Sheet24").Activat


----- Gee wrote: ----

I'm real new at programing in Excel...Access I've done
I need to know the "on click" code for a command button to
open a spreadsheet in a workbook. I've got a table of
contents and I want to be able to click the button to go
to the proper sheet within that book. Say the sheet is
named 24
Thank you in advance for your help
Ge

 

[Gee]

Now it's not letting me push the button...any suggestions?
Do I have to convert to a form or something?
Sorry I'm so ignorant.

 

[Gee-dope]

OK that was too stupid...I had it in design view.
That worked, by the way!
Thanks.

 

[David Adamson]

Worksheets("24").activate

Will get you there. If you are running the code from a userfrom and you
want that invisible. Then use

userformname.hide

as the first line

 

[Gord Dibben]

Gee

From Bob Phillips...

Sub BrowseSheets()
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg 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

Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

iBooks = 0

TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
iBooks = iBooks + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i

PrintDlg.Buttons.Left = 240

CurrentSheet.Activate

With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With

PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
MsgBox "Worksheet " & Worksheets(cb.Caption).Name & "
Selected"
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
PrintDlg.Delete

End Sub

Gord Dibben Excel MVP