+ Reply to Thread
Results 1 to 6 of 6

How evaluate clicked commandbuttons on form

Hybrid View

  1. #1
    Registered User
    Join Date
    10-09-2019
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    2

    How evaluate clicked commandbuttons on form

    I have a form with a group of three commandbuttons: if a user clicks just one of the commandbuttonvalues is True, the other two are false. While the form is still open I have to evaluate which button has been clicked, so a sub procedure can be started. I have two questions:
    1. I managed evaluate the clicked button indirectly by declaring three Public variables, but I think that that is a very poor method. Is there a better and more robust way to do that?
    2. I want to change the color of the caption of the clicked button, but my attempts sofar failed.
    See my attachment.
    Can someone help me out? Thanks very much in advance.
    Attached Files Attached Files
    Last edited by HJWNap; 08-21-2023 at 03:50 PM. Reason: typing mistake

  2. #2
    Registered User
    Join Date
    07-26-2023
    Location
    Oklahoma
    MS-Off Ver
    365
    Posts
    57

    Re: How evaluate clicked commandbuttons on form

    Very quick and brief response here:

    Can you just use Option Buttons?

    See revised code attachedForm with group buttons.xlsmForm with group buttons.xlsm

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this ...


    The Public variable bo??? are useless, just checking the text color button …

    To class module CommandButtonClass :

    PHP Code: 
    Sub CommandButtonGroup_Click()
        
    UserForm.cmdButtonAAA.ForeColor 0
        UserForm
    .cmdButtonBBB.ForeColor 0
        UserForm
    .cmdButtonCCC.ForeColor 0
        CommandButtonGroup
    .ForeColor vbBlue
    End Sub 
    To module UserForm :

    PHP Code: 
    Private Sub cmdValues_Click()
        
    MsgBox "Button AAA : " & (cmdButtonAAA.ForeColor vbBlue) & vbLf vbLf _
               
    "Button BBB : " & (cmdButtonBBB.ForeColor vbBlue) & vbLf vbLf _
               
    "Button CCC : " & (cmdButtonCCC.ForeColor vbBlue), , "Selected status"
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,410

    Re: How evaluate clicked commandbuttons on form

    See the MouseUp & MouseDown events in the Class Module.
    Also attached a demo of option buttons constructed at runtime in groupings using similar Class module technique.
    Attached Files Attached Files
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  5. #5
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,525

    Re: How evaluate clicked commandbuttons on form

    Many roads lead to Rome.
    This is another one, a bit more winding than the road proposal shown by Marc L, but it works on a similar principle. I'm just listing the revised procedures.

    In UserForm module:
    Private Sub cmdValues_Click()
      Dim i As Long
      Dim strVal As String
        
        For i = 1 To UBound(Buttons)
          strVal = strVal & CBool(Buttons(i).CommandButtonGroup.Tag) & vbLf
        Next i
        
        MsgBox "The Values of the boxes are: " & vbLf & strVal
    
    End Sub
    
    
    Private Sub UserForm_Initialize()
      
        Dim Ctrl        As Control
        Dim Count       As Integer
    
        For Each Ctrl In Me.frmButtons.Controls
            If TypeName(Ctrl) = "CommandButton" Then
                Count = Count + 1
                ReDim Preserve Buttons(1 To Count)
                Set Buttons(Count).CommandButtonGroup = Ctrl
                
                'Set each button to False (0)
                Buttons(Count).CommandButtonGroup.Tag = 0
            End If
        Next Ctrl
    End Sub
    In class module :
    Sub CommandButtonGroup_Click()
    
        Dim varButtonChoice As Variant
        Dim Ctrl        As Control
        Dim Fram As MSForms.Frame
    
        varButtonChoice = CommandButtonGroup.Caption
        Set Fram = CommandButtonGroup.Parent
        
        MsgBox "You pressed " & varButtonChoice
        
        'Set all buttons to False (0)
        For Each Ctrl In Fram.Controls
          If TypeName(Ctrl) = "CommandButton" Then
            Ctrl.Tag = 0
          End If
        Next Ctrl
        
        'Set clicked button to True (-1)
        CommandButtonGroup.Tag = -1
        
    End Sub
    Artik

  6. #6
    Registered User
    Join Date
    10-09-2019
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    2

    Re: How evaluate clicked commandbuttons on form

    Lastjp, Marc L, torachan and Artik, thank you all very much for your replies. I combined a few of the answers and it works fine: the buttons as well as the changing of the color. Why not OptionButtons? The form in my attachment is just al smal part of a larger form with several types of controls and it looked quite chaotic. But this is perfect ;-) - thanks, Jan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Captions of CommandButtons disappear after buttons are clicked
    By elfsprin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-10-2024, 11:34 AM
  2. [SOLVED] How do i test if a form (not MsgBox) button is clicked?
    By Platempole in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-21-2022, 05:12 AM
  3. Is there a way to save a form without its CommandButtons?
    By Wouldsman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2015, 12:05 PM
  4. How to disable a Form Control Burron once Clicked?
    By vhache in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2015, 02:55 PM
  5. Automatic attachment of a form to a particular email when clicked....
    By jaysakle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-04-2014, 03:18 AM
  6. [SOLVED] How to pop a form when clicked on cell
    By meprad in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-22-2013, 12:25 AM
  7. Using a Form Button to add 1 to a cell each time it is clicked
    By a.mack123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-14-2010, 03:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1