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??

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

(E-Mail Removed) wrote:
> 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??
>

yes - the buttons are in the userform

On Oct 30, 4:32 pm, Ken Puls <ken.p...@nospampleasegmail.com> wrote:
> 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
>
> shumwaymeis...@gmail.com wrote:
> > 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??

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

(E-Mail Removed) wrote:
> yes - the buttons are in the userform
>
> On Oct 30, 4:32 pm, Ken Puls <ken.p...@nospampleasegmail.com> wrote:
>> 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
>>
>> shumwaymeis...@gmail.com wrote:
>>> 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 wrote:
> 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.

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

(E-Mail Removed) wrote:
> Ken Puls wrote:
>> 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.
>

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)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> yes - the buttons are in the userform
>
> On Oct 30, 4:32 pm, Ken Puls <ken.p...@nospampleasegmail.com> wrote:
> > 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
> >
> > shumwaymeis...@gmail.com wrote:
> > > 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??
>