Hello:

I am using a usewrform in VBA with two option buttons. Then I have in a
sheet a Worksheet_BeforeDoubleClick code which I want to work depending on
the users selection for the option button. How can I refer to check which
option the user has selected?

Please help.

Thanks.

In a general VBA module in your workbook, declare a Public variable to keep
track of which option is selected. Assign the appropriate value to the
variable in the Change event for each option button. Then have your
Worksheet_BeforeDoubleClick code check the current value of the Public
variable.

Hope this helps,

Hutch

"art" wrote:

Hello:

I am using a usewrform in VBA with two option buttons. Then I have in a
sheet a Worksheet_BeforeDoubleClick code which I want to work depending on
the users selection for the option button. How can I refer to check which
option the user has selected?

Please help.

Thanks.

You dont need to check just use the commandbutton_click event to run the
code, other wise you could do something like this in a standard module:
Public CB1Val As Integer, CB2Val As Integer
then in your commandbutton_click for command button1
CB1Val=1
'Call your code
CB1Val=0

then in your code:
If CB1Val=1 then
'do something
ElseIf CB2Val=1 then
'do something else
End If

or something along those lines.

Regards,
The Code Cage Team
www.thecodecage.com/forumz

"art" wrote:

Hello:

I am using a usewrform in VBA with two option buttons. Then I have in a
sheet a Worksheet_BeforeDoubleClick code which I want to work depending on
the users selection for the option button. How can I refer to check which
option the user has selected?

Please help.

Thanks.

Thanks both for responding. But I can't figure out how to get this done here.
This is my code for the userform:

Private Sub CancelButton_Click()
Unload UserForm1
End Sub
Sub OKButton_Click()
If RightAdd Then
MyOption = 1
Else: MyOption = 2
End If
Unload UserForm1
End Sub

And the following is the code I have for the doubleClick:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If UserForm1.LeftAdd = True Then
If Target.Cells.Count 1 Then Exit Sub
Cancel = True 'stop editing in cell
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End If
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True 'stop pop up from showing
If UserForm1.LeftAdd = True Then
If ActiveCell.Value <= 1 Then
ActiveCell = ""
Else
If IsNumeric(Target.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
End If
End If
End If
End Sub

I want to make the DoubleClick and the RightClick work only if the user
selected one of the options.

Please help me do this.

Thanks.



"The Code Cage Team" wrote:

You dont need to check just use the commandbutton_click event to run the
code, other wise you could do something like this in a standard module:
Public CB1Val As Integer, CB2Val As Integer
then in your commandbutton_click for command button1
CB1Val=1
'Call your code
CB1Val=0

then in your code:
If CB1Val=1 then
'do something
ElseIf CB2Val=1 then
'do something else
End If

or something along those lines.

Regards,
The Code Cage Team
www.thecodecage.com/forumz

"art" wrote:

Hello:

I am using a usewrform in VBA with two option buttons. Then I have in a
sheet a Worksheet_BeforeDoubleClick code which I want to work depending on
the users selection for the option button. How can I refer to check which
option the user has selected?

Please help.

Thanks.

Art.

Tom and The Code Cage Team are both correct.

On your userform your code is unloading the form. This means that the
beforedoubleclick event cannot see what option the user has chosen. You
therefore have two options:-
1. Store the value of MyOption in a global variable before unloading the form.
2. Don't unload the form when the user clicks OK, just hide it with me.Hide

If you need this option to be used the next time that the user opens Excel
then you obviously need a routine to store it in an ini file or the registry
maybe, and then another routine to collect the value when opening Excel.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"art" wrote:

Thanks both for responding. But I can't figure out how to get this done here.
This is my code for the userform:

Private Sub CancelButton_Click()
Unload UserForm1
End Sub
Sub OKButton_Click()
If RightAdd Then
MyOption = 1
Else: MyOption = 2
End If
Unload UserForm1
End Sub

And the following is the code I have for the doubleClick:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If UserForm1.LeftAdd = True Then
If Target.Cells.Count 1 Then Exit Sub
Cancel = True 'stop editing in cell
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End If
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True 'stop pop up from showing
If UserForm1.LeftAdd = True Then
If ActiveCell.Value <= 1 Then
ActiveCell = ""
Else
If IsNumeric(Target.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
End If
End If
End If
End Sub

I want to make the DoubleClick and the RightClick work only if the user
selected one of the options.

Please help me do this.

Thanks.



"The Code Cage Team" wrote:

You dont need to check just use the commandbutton_click event to run the
code, other wise you could do something like this in a standard module:
Public CB1Val As Integer, CB2Val As Integer
then in your commandbutton_click for command button1
CB1Val=1
'Call your code
CB1Val=0

then in your code:
If CB1Val=1 then
'do something
ElseIf CB2Val=1 then
'do something else
End If

or something along those lines.

Regards,
The Code Cage Team
www.thecodecage.com/forumz

"art" wrote:

Hello:

I am using a usewrform in VBA with two option buttons. Then I have in a
sheet a Worksheet_BeforeDoubleClick code which I want to work depending on
the users selection for the option button. How can I refer to check which
option the user has selected?

Please help.

Thanks.