PDA

View Full Version : [SOLVED] KB: Group All Command Buttons on a Userform



Paul_Hossler
07-13-2014, 06:02 PM
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
07-14-2014, 12:51 AM
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_invoercontrole_en.html

Aflatoon
07-14-2014, 01:11 AM
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
07-14-2014, 04:00 AM
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
07-14-2014, 04:02 AM
Snoopy Dance! That was my three-thousandth post! (mostly of dubious value...)

Aflatoon
07-14-2014, 04:33 AM
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
07-14-2014, 04:46 AM
Or if you want to change some properties, depending on whether it has been clicked .visible.backgroundcolor, enabled etc.

GTO
07-14-2014, 05:17 AM
Mostly clumsy dancing, and thank you both :-)

Paul_Hossler
07-14-2014, 05:52 AM
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
07-14-2014, 06:25 AM
see the attachment

GTO
07-15-2014, 01:12 PM
...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
07-15-2014, 06:32 PM
Hi Mark --



Is that anything like what you were wanting?


VERY much so!! :thumb

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 :thumb :thumb :beerchug:

GTO
07-15-2014, 07:42 PM
:friends: Cheers back and glad that helped :cloud9: