How to assign a macro

M

Mike

I have a workbook with 30-40 different sheets, i have a command button on
each sheet that start a macro called Sub Producedwatersystem().

Is it possible that a form can appear at the same time as the workbook is
opened?
(a opening page).

Is it possible that the user can choose what sheet to be active and start
the macro from that form?
 
M

Mike

Hi Bob, thanks for helping me!

I dont want the form to do anything, But i want 2 command buttons on it, one
called Cancel and one called New input, both of them shall hide the form and
the last one should also start the macro assign to the worksheet
 
B

Bob Phillips

I would add a listbox, and two buttons to the form. The listbox will list
all the sheets, the buttons will be a Cancel and a New button. The userform
code would then be


Private Sub CommandButto2_Click()
Unload Me
End Sub

Private Sub CommandButton1_Click()
If Me.ListBox1.ListIndex = -1 Then
MsgBox "Select a sheet"
Else
Worksheets(Me.ListBox1.Value).Activate
Call myMacro
Unload Me
End If
End Sub

Private Sub UserForm_Activate()
Dim sh As Worksheet

With ActiveWorkbook
For Each sh In .Worksheets
Me.ListBox1.AddItem sh.Name
Next sh
End With

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Michael

Thanks Bob.

It works:)

Bob Phillips said:
I would add a listbox, and two buttons to the form. The listbox will list
all the sheets, the buttons will be a Cancel and a New button. The userform
code would then be


Private Sub CommandButto2_Click()
Unload Me
End Sub

Private Sub CommandButton1_Click()
If Me.ListBox1.ListIndex = -1 Then
MsgBox "Select a sheet"
Else
Worksheets(Me.ListBox1.Value).Activate
Call myMacro
Unload Me
End If
End Sub

Private Sub UserForm_Activate()
Dim sh As Worksheet

With ActiveWorkbook
For Each sh In .Worksheets
Me.ListBox1.AddItem sh.Name
Next sh
End With

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top