Situation I have two form control check boxes. I am trying to write a code that will allow only either of them to be true.
my code is
Sub CheckBox2_Click()
If CheckBox1.Enabled = True Then
CheckBox2.Enabled = False
Else
If CheckBox2.Enabled = True Then
CheckBox1.Enabled = False
End If
End If
End Sub
I have this code in module and have assigned the same macro for both the checkboxes. I get run-time error 424. I beleive this is very basic problem but I unable to dela with it. Thank you
CodePudding user response:
Please, test the following way. Form check boxes do not have a click event
, as ActiveX ones have. You should associate the next sub to both of them. The check boxes I tested, have their names as "Check Box 1" and "Check Box 2". You have to change yours according to the reality in your case, Please, copy the next code in a standard module and then associate it to both used check boxes:
Option Explicit
Sub FormCheckBoxChange()
If ActiveSheet.CheckBoxes(Application.Caller).value = 1 Then
Select Case Application.Caller
Case "Check Box 1": ActiveSheet.CheckBoxes("Check Box 2").value = -4146
Case "Check Box 2": ActiveSheet.CheckBoxes("Check Box 1").value = -4146
End Select
End If
End Sub
Use instead of the used check box names, the appropriate ones for your case.
In case of Form text boxes, their value is not True
and False
as in case of ActiveX ones. It is 1
and -4146
...
CodePudding user response:
Are you sure you want to enable/disable the checkboxes.
Following code makes sure that either one of both boxes is checked.
Public Sub checkbox2_onClick()
Dim oCb1 As Object
Set oCb1 = Table1.Shapes("Checkbox1").OLEFormat.Object
Dim oCb2 As Object
Set oCb2 = Table1.Shapes("Checkbox2").OLEFormat.Object
If oCb2.Value = 1 Then oCb1.Value = 0
End Sub
Public Sub checkbox1_onClick()
Dim oCb1 As Object
Set oCb1 = Table1.Shapes("Checkbox1").OLEFormat.Object
Dim oCb2 As Object
Set oCb2 = Table1.Shapes("Checkbox2").OLEFormat.Object
If oCb1.Value = 1 Then oCb2.Value = 0
End Sub