Use same UserForm

  • Thread starter Thread starter Soniya
  • Start date Start date
S

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
 
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
 
Hi David,

Thanks for your reply.

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

Thanks
Soniya
 
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
 
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.
 
Back
Top