Reference Command Button from User Form when it's clicked

jewkes6000

Board Regular
Joined
Mar 25, 2020
Messages
60
Office Version
  1. 365
Platform
  1. Windows
I have a macro which calls a User Form to give the user the option to select from a list (from a listbox). On this UserForm, there is also a button to click ("CommandButton_NoChange") if they don't want to select from the listbox. After the user selects something from the user form, it then comes back to my original macro. If they choose something from the listbox, I assign it to "Location". This works fine. But if they don't choose from the listbox and click the button ("CommandButton_NoChange"), I can't seem to run an if statement to determine if the button was clicked or not. In the example below, I tried testing if the button value was "True", but even if I click the button, the button value remains false making this if statement worthless. How can I determine if the button was clicked in the UserForm and then use that information to continue my code?

Here is my code:
VBA Code:
    UserForm_Location_RiskItem.Repaint
    UserForm_Location_RiskItem.Show
    Location = UserForm_Location_RiskItem.ListBox_PrjList.Value
 
    If UserForm_Location_RiskItem.CommandButton_NoChange = True Then
        Range("Y39").Select
    End If



I have also tried setting the Command Button value to true within the UserForm code, but this doesn't work either.


VBA Code:
Private Sub CommandButton_NoChange_Click()
    UserForm_Location_RiskItem.Hide
    UserForm_Location_RiskItem.CommandButton_NoChange = True
    
    
End Sub
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Create a global Boolean variable, set to false by default. Have it made true by the button. Create the if statement that looks for that true value, and as part of the Then section set the value back to false.
Just an idea.
 
Upvote 0
I often use the Userform's .Tag property for things like this.

VBA Code:
    With UserForm_Location_RiskItem
        .Tag = "False"
        .Repaint
        .Show
        Location = .ListBox_PrjList.Value
        
        If .Tag = "True" Then
            Range("Y39").Select
        End If
    End With


VBA Code:
Private Sub CommandButton_NoChange_Click()
    Me.Tag = "True"
    UserForm_Location_RiskItem.Hide
End Sub
 
Upvote 0
Solution
I often use the Userform's .Tag property for things like this.

VBA Code:
    With UserForm_Location_RiskItem
        .Tag = "False"
        .Repaint
        .Show
        Location = .ListBox_PrjList.Value
       
        If .Tag = "True" Then
            Range("Y39").Select
        End If
    End With


VBA Code:
Private Sub CommandButton_NoChange_Click()
    Me.Tag = "True"
    UserForm_Location_RiskItem.Hide
End Sub
This is a really smart idea (assuming you aren't using the tag for anything else)! I'm excited to use this idea myself.
 
Upvote 0
@rlv01 - Using the Tag function seems like the way to go. I was using this as a workaround, but it seems it's the best way for a situation like this. Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top