Excel Macros Help

[Guest]

What I am trying to do is have a workbook setup with a menu on the first
worksheet. (I have done this before, but I can't seem to get the macro to
work.)

The following is the macro written in the code for one button on the menu.
This doesn't seem to work because I keep getting an error.

Sub WorkBook_Open()
WorkSheets("Sheet2").Activate
End Sub


I would also like to have a back button and a print button on the actual
worksheet.

 

[Dave Peterson]

Do you have a sheet named "sheet2"?

If yes (after you check the spelling!), post back with the error message. (if
it's subscript out of range, then check your spelling once more).

If you need a macro to print, then record a macro when you print what you want.
You'll have the code.

I'm not sure what a back button would do.

 

[Guest]

There is a sheet named "sheet2". The error is:
Compile Error: Ambiguous Name detected: Workbook_Open

 

[Dave Peterson]

You can only have one workbook_open event. You'll have to throw the others away
-- or combined them into one procedure.

Maybe you can just add this line to the bottom of the other:

WorkSheets("Sheet2").Activate
(right before the "End Sub" line)

 

[Guest]

That was it. It worked...thanks much

You can only have one workbook_open event. You'll have to throw the others away
-- or combined them into one procedure.

Maybe you can just add this line to the bottom of the other:

WorkSheets("Sheet2").Activate
(right before the "End Sub" line)

 

[Gord Dibben]

For starters, workbook_open sub is used in Thisworkbook module and just runs
when the workbook is opened.

Change to............

Sub Sheet2_active()
WorkSheets("Sheet2").Activate
End Sub

I prefer Bob Phillips' browsesheets macro that allows you to select sheets from
a form.

One button on a Toolbar is accessible from any sheet.

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

For alternative see Debra Dalgleish's site

http://www.contextures.com/xlToolbar01.html


Gord Dibben MS Excel MVP