How to count Option button clicks on same userform?

danbates

Active Member
Joined
Oct 8, 2017
Messages
312
Office Version
2016
Platform
Windows
Hi,

Please can someone help me?

I have a userform called OPTIONS.

I have 6 different options on the userform.

What I would like is if an operator selects an option it counts the number of times that selection has been selected in a textbox on the same userform.

For example:

If OptionButtonADD is selected then an 1 appears in TextBoxADD and if it is selected again then a 2 appears in the relevant textbox and so on.

Once I get the first code for the ADD I will be able to do the rest.

I would like a reset count code as well please to reset all the textboxes.

Any help would much appreciated.

Thanks

Dan
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

gsbelbin

Active Member
Joined
Aug 29, 2008
Messages
336
There are various events associated with the option button such as Click, Mouse Down etc. So I would suggest using Click to increment a counter for each option button
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,383
Office Version
365
Platform
Windows
Try this

Code:
Private Sub OptionButtonAdd_Click()
    With TextBoxADD
        If .Value = "" Then .Value = 0
        .Value = .Value + 1
    End With
End Sub
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
312
Office Version
2016
Platform
Windows
Hi Yongle,

Unfortunately its not working.

I found a code very similar to yours earlier and that didn't work either and I don't know why?

The only way I've been able to get it to work so far is by using a cell on the worksheet as the counter and then using the control source from the textbox options and have it look at that cell, but it isn't the way I would like it.

Any further ideas would be appreciated.

Thanks

Dan
 

gsbelbin

Active Member
Joined
Aug 29, 2008
Messages
336
Probably need to use the MouseDown event instead
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Try something like this.
Change control names if needed:

Code:
Private Sub OptionButton1_Click()
'Modified  4/16/2019  7:32:25 AM  EDT
If TextBox1.Value = "" Then TextBox1.Value = 1: OptionButton1.Value = False: Exit Sub
TextBox1.Value = TextBox1.Value + 1
OptionButton1.Value = False
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,383
Office Version
365
Platform
Windows
Unfortunately its not working.
I found a code very similar to yours earlier and that didn't work either and I don't know why?
The only way I've been able to get it to work so far is by using a cell on the worksheet as the counter and then using the control source from the textbox options and have it look at that cell, but it isn't the way I would like it.
Any further ideas would be appreciated.
Thanks
Dan
I tested it before posting and it does work
- so there is something different in your form (or its code)

I assumed you were resetting your option button elsewhere. If you are not doing that
Code:
Private Sub OptionButtonAdd_Click()
    With TextBoxADD
        If .Value = "" Then .Value = 0
        .Value = .Value + 1
    End With
    [COLOR=#ff0000]OptionButtonAdd.Value = False[/COLOR]
End Sub
Is OptionButtonADD an OptionButton or a CommandButton?
(I have it working with both)
 
Last edited:

danbates

Active Member
Joined
Oct 8, 2017
Messages
312
Office Version
2016
Platform
Windows
Hi,

Right, what I think it is, is when I close the userform to open another it is clearing the count.

I saw it because of My Answer Is This's code. With your code I have to select the ADD option twice but after the first click I saw the counter work.

Here is my code as it is now:

Code:
Private Sub OptionButtonADD_Click()

If TextBoxADD.value = "" Then TextBoxADD.value = 1: OptionButtonADD.value = False: Exit Sub
TextBoxADD.value = TextBoxADD.value + 1
OptionButtonADD.value = False

' With TextBoxADD
'        If .value = "" Then .value = 0
'        .value = .value + 1
'    End With
'    OptionButtonADD.value = False


'Unload Me


Me.Hide

OpenNewProduct

End Sub

I prefer Yongle's code because I only have to select the option once.

Is there any issues with me having the userfom hidden instead of being closed?

Thanks guys I really appreciate your help.



gsbelbin, thank you as well for your advice but I am going to persist with the click event, I think we are nearly there.

Thanks

Dan
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
If you hide the UserForm the values in the UserForm are maintained.
But those values will be lost when you close the Workbook.
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
312
Office Version
2016
Platform
Windows
Hi,

There is no way around it apart from linking the userform textbox to a cell on the worksheet?

I will lose the counter qty if I either unload the userform or close the worksheet as it is now?

Thanks

Dan
 

Watch MrExcel Video

Forum statistics

Threads
1,102,842
Messages
5,489,204
Members
407,678
Latest member
Matt1989

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top