KB: Group All Command Buttons on a Userform

[Paul_Hossler]

ref: http://www.vbaexpress.com/kb/getarticle.php?kb_id=85


I must be missing something obvious

I can do the example and when I click on one of the buttons I get the message

Private Sub CommandButtonGroup_Click() 
     
    MsgBox "You pressed " & CommandButtonGroup.Caption 
     
End Sub

I don't want to show a message, but I'm stuck on how to return which button was Clicked back to the calling program. A Public variable just seems 'not right'

[snb]

What do you want to do once you know which button has been clicked ? (the checking alternative of a msgbox is debug.print and the immediate window).

Some more background information in:

http://www.snb-vba.eu/VBA_Userform_i...ntrole_en.html

[Aflatoon]

Normally I would use a property of the form that the calling code can read (though a Public member variable in the form will work too).

[GTO]

Hi All:

Admittedly not well thought through, but I am missing the advantage of a Class for CommandButtons. Leastwise unless we were to be using them as radio buttons (that is, some argument/value would change).

Otherwise, if we are doing different things with each command button - what would be the advantage of instantiating a bunch of classes and figuring out what to do, depending on which button was 'pressed'?

Utterly no offense to Jacob's code - I am just missing it by miles more than Paul :-)

Thank you so much,

Mark

[GTO]

Snoopy Dance! That was my three-thousandth post! (mostly of dubious value...)

[Aflatoon]

Hi Mark,

Nice dancing.

If the buttons all do different things, there would be no advantage to using a class. It would only be useful if they all performed the same action but on say a different range, or were acting as option buttons as you mention.

[snb]

Or if you want to change some properties, depending on whether it has been clicked .visible.backgroundcolor, enabled etc.

[GTO]

Mostly clumsy dancing, and thank you both :-)

[Paul_Hossler]

The userform has a 6x7 array of command buttons representing a monthly calendar, or least it will be when I get done

The non-class version has 42 events to return the date associated with the clicked command button, and I was trying to simplify the maintenance

The class version has the 42 command button enteries in a 42 element array. Each command button runs the same event, and I store indentifying information in a class property.

BUT .... I thought I could just use a Property Get to return which command button was clicked, and so far I haven't figure out how

[snb]

see the attachment

[GTO]

...I don't want to show a message, but I'm stuck on how to return which button was Clicked back to the calling program. A Public variable just seems 'not right'

...BUT .... I thought I could just use a Property Get to return which command button was clicked, and so far I haven't figure out how

Hi Paul,

I might still be misreading what you are asking, but are you wanting to avoid a Public variable in a Standard Module (to hold the value returned by the button) and would rather store the value in the Form?

Maybe like:

UserForm code:

Option Explicit
  
Private cButtons(1 To 42) As clsButton
  
Private lButtonVal As Long
  
Public Property Let ButtonValue(bv As Long)
  lButtonVal = bv
End Property
  Public Property Get ButtonValue() As Long
    ButtonValue = lButtonVal
  End Property
  
Private Sub CommandButton1_Click()
  If Me.ButtonValue > 0 Then
    Me.TextBox1.Value = ButtonValue
  Else
    Me.TextBox1.Value = "No button pushed"
  End If
End Sub
  
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim n As Long
  
  n = 0
  For Each ctl In Me.Controls
    If ctl.Name Like "cmdDAY_##" Then
      n = n + 1
      Set cButtons(n) = New clsButton
      Set cButtons(n).ParentForm = Me
      Set cButtons(n).ThisCommandButton = ctl
      cButtons(n).ButtonValue = n
    End If
  Next
  
End Sub

Class:

Option Explicit
  
Public WithEvents ThisCommandButton As MSForms.CommandButton
  
Private objPF As Object
Private lButtonVal As Long
  
Public Property Set ParentForm(pf As Object)
  Set objPF = pf
End Property
  Public Property Get ParentForm() As Object
    Set ParentForm = objPF
  End Property
Public Property Let ButtonValue(bv As Long)
  lButtonVal = bv
End Property
  Public Property Get ButtonValue() As Long
    ButtonValue = lButtonVal
  End Property
  
Private Sub ThisCommandButton_Click()
  ParentForm.ButtonValue = Me.ButtonValue
End Sub

Is that anything like what you were wanting?

Mark

[Paul_Hossler]

Hi Mark --

Is that anything like what you were wanting?

VERY much so!!

I have a philosophical objection to having a class just throw a value at a global variable

I single stepped through your example (OK, it did take several times) to see the technique and I can integrate it into my little calendar popup

So thanks again

[GTO]

Cheers back and glad that helped