Use same UserForm

[Soniya]

Hi all,

Is it possible to use the same UserForm for several
actions?
For eg. I have a userform with a TextBox and Two
commandButons "OK" and "Cancel"

I am using several macros in which the action when
clicking OK button is different but the design of the
UserForm is same. so in order to perfom these action I
have created several UserFoms which look like the same.

Is there a way that i can use the same userfom and
perform the action based on the sub routine from which it
is called?

TIA
Soniya

 

[David Hager]

You can set the Tag property of the CommandButton when the procedure is run.
Then, use a Select Case statement to use this value to obtain the desired
action.

David Hager
Excel MVP

 

[Soniya]

Hi David,

Thanks for your reply.

How could I do this? Could you please be more specific
with an example?

Thanks
Soniya

 

[Harald Staff]

Hi Soniya

A slight variation, using a public variable instead of the tag.

Userform code:

Option Explicit 'top of module
Public ActionNumber As Long

Private Sub CommandButton1_Click()
Select Case ActionNumber
Case 1
MsgBox "I run macro 1"
Case 2
MsgBox "I run macro 2"
Case 3
MsgBox "I run macro 3"
Case Else
MsgBox "I dunno"
End Select
Unload Me
End Sub


Sample macro in a standard module:

Sub test()
UserForm1.Caption = "ready for code 1"
UserForm1.ActionNumber = 1
UserForm1.Show
MsgBox "That's it"

UserForm1.Caption = "ready for code 3"
UserForm1.ActionNumber = 3
UserForm1.Show
MsgBox "That's it"

UserForm1.Caption = "Something else"
UserForm1.ActionNumber = 0
UserForm1.Show
MsgBox "That's definitely it"
End Sub

 

[Dianne]

I use a form to select criteria to run one of three different reports.
For one report, I disable certain checkboxes. When I load the form I set
the tag to "FReport", "BReport" or "UpdateSummary".

With frmSetCriteria
.Tag = "FReport" (or "BReport" or "UpdateSummary" or whatever)
.Show
End With

Then on the form itself --

Private Sub UserForm_Activate()

'If the type of report is Summary then disable some checkboxes
Select Case frmSetCriteria.Tag
Case "FReport"
Case "BReport"
Case "UpdateSummary"
Me.chkSeparateDates.Enabled = False
Me.chkSeparateTasks.Enabled = False
Case Else
MsgBox "Unknown Report", vbInformation, "Error"
End Select

End Sub

On this form, I have an OK button. when you click it, it checks the Tag
property of the form and runs different macros:

Private Sub cmdOK_Click()

Select Case frmSetCriteria.Tag
Case "FReport"
Call GenerateForemanReport
Case "BReport"
Call GenerateBillingReport
Case "UpdateSummary"
Call CreateSummarisedForemanReport
Case Else
MsgBox "Unknown Report"
End Select

End Sub

I hope this gives you some ideas.