Customizing userforms on button clicks

[shumwaymeister]

I have a series of buttons all using the same Userform. However, I
would like to be able to personalize the title of the userform -
depending on what button is clicked.

The long way to do it, would be to have a short macro for each button,
that would each generate the userform and accordingly change the
caption details.

But I am assuming there should be a shorter way, to enable a large set
of buttons to use the same userform and be able to personalize the
title as required. Perhaps a series of case statements or even somthing
simpler??

 

[Ken Puls]

Hi there,

These are buttons inside the userform? The long way to do it would be
calling a function to evaluate what button was clicked. It's a simple
one liner to change the caption in your Button_Click event:

Me.Caption = "MyCustomTitle"

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[shumwaymeister]

yes - the buttons are in the userform

Hi there,

These are buttons inside the userform? The long way to do it would be
calling a function to evaluate what button was clicked. It's a simple
one liner to change the caption in your Button_Click event:

Me.Caption = "MyCustomTitle"

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)www.excelguru.ca

 

[Ken Puls]

Well, here's one way.

Private Sub CommandButton1_Click()
Call ChangeCaption("CommandButton1")
End Sub

Private Sub ChangeCaption(sCaptionName As String)
Select Case sCaptionName
Case Is = "CommandButton1"
Me.Caption = "MyCustomTitle"
Case Else
Me.Caption = "Something else"
End Select
End Sub

The only benefit that I can see to doing this, though, is to have a
place to centrally manage the naming. It's actually more code than just
changing it in the Button_Click event.

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[shumwaymeister]

Well, here's one way.

Private Sub CommandButton1_Click()
Call ChangeCaption("CommandButton1")
End Sub

Private Sub ChangeCaption(sCaptionName As String)
Select Case sCaptionName
Case Is = "CommandButton1"
Me.Caption = "MyCustomTitle"
Case Else
Me.Caption = "Something else"
End Select
End Sub

The only benefit that I can see to doing this, though, is to have a
place to centrally manage the naming. It's actually more code than just
changing it in the Button_Click event.

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Actually centralizing the coding is important, as I may need to add
additional functions later.

I actually came up with an approach similar to the one suggested but
using string parsing.

Is there away to refer to the title of each button in a uniform manner?

E.g.

buttonTitle = me.name

I tried the above but it seemed to be extracting the name of the sheet.
If there was a way generic way to extract the caption or name field
from the list of properties (as opposed to writing CommandButton1 etc),
I could simply pass a generic reference - extract some data and use
that to manipulate the title.

 

[Ken Puls]

Me.Name will actually return the userform's caption.

The best I can do for you is that you could pass it as an MSForms
button, then query the caption. You'll still need to assign the
specific CommandButton object at the beginning of each button though:

Private Sub CommandButton1_Click()
Call CheckButton(Me.CommandButton1)
End Sub

Private Sub CommandButton2_Click()
Call CheckButton(Me.CommandButton2)
End Sub

Private Sub CheckButton(cButton As msforms.CommandButton)
MsgBox "You clicked " & cButton.Caption
End Sub

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Bob Phillips]

Create a contyrol array for the button.


Insert a cass module and name it clsUserFormEvents

Option Explicit

Public WithEvents mButtonGroup As msforms.CommandButton

Private Sub mButtonGroup_Click()
Select Case mButtonGroup.Caption
Case "CommandButton1" : Userform1.Caption = "Title1"
Case "CommandButton2" : Userform1.Caption = "Title2"
etc.
End Sub


In the userform add

Option Explicit

Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cBtnEvents As clsUserFormEvents
Dim ctl As msforms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "CommandButton" Then
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mButtonGroup = ctl
mcolEvents.Add cBtnEvents
End If
Next

End Sub


Then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)