enabling a checkbox based on conditions

[Nikhil2803]

Hi Team, I have a checkbox (units of) that needs to be automatically enabled if users provides numeric values in D14,D15 and D16 cells.

I have written the below code in my worksheet, It is somehow working, but its throwing below error exception.

'Vol basic Life Unitodd checkbox'

If Not IsEmpty(Range("D14")) And Not IsEmpty(Range("D15")) And Not IsEmpty(Range("D16")) Then

Range("vbl_unitsof_indicator").Value = True

End If

Your any valuable inputs will be highly appreciated. Thank you

 

[RobP]

Hi, I'm not sure why you get that error, but try using this line of code instead to activate it and see how it goes ?

ActiveSheet.CheckBoxes("vbl_unitsof_indicator").Value = True

 

[Nikhil2803]

Hi, I'm not sure why you get that error, but try using this line of code instead to activate it and see how it goes ?

ActiveSheet.CheckBoxes("vbl_unitsof_indicator").Value = True

No. Its the same. So, basically what the requirement is. Once the user inputs values in D14,D15 and D16, then this checkbox should automatically get checked. Now what happening is, when I am giving any value in, suppose D14 and while going to enter value for D15, the cursor is just moving for few seconds and then getting' out of stack issue error' . basically after giving cells values, It is invoking the "Sub Worksheet_Change(ByVal Target As Range)" method. under this sub method, I have written the required code. If you can provide any other code snippets, it will be very beneficial for me.

 

[RobP]

ok, so sounds like its more to do with the code you have written inside this

Sub Worksheet_Change(ByVal Target As Range)

might you be able to share it ?
thanks
Rob

 

[Nikhil2803]

ok, so sounds like its more to do with the code you have written inside this

Sub Worksheet_Change(ByVal Target As Range)

might you be able to share it ?
thanks
Rob

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormControl As CheckBox
Dim myFormControl1 As CheckBox
Dim answer As Integer

Set myFormControl = ActiveSheet.Shapes("hmo_checkbox").OLEFormat.Object
Set myFormControl1 = ActiveSheet.Shapes("ny_dbl_checkbox").OLEFormat.Object

Unprotect
'Vol basic Life Unitodd checkbox'

If Not IsEmpty(Range("D14")) And Not IsEmpty(Range("D15")) And Not IsEmpty(Range("D16")) Then

Range("vbl_unitsof_indicator").Value = True
'ActiveSheet.CheckBoxes("vbl_unitsof_indicator").Value = True'

Else
Range("vbl_unitsof_indicator").Value = False

End If

I do have some other conditions below.

 

[RobP]

I can't get mine to fail with the same error sorry.

To get it to run, I've used the following code as I dont know anything about your setmyFormControl lines.

Seems to work just fine.

Do you have many worksheets open at the same time for example ? perhaps some corruption - maybe a reboot ?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormControl As CheckBox
Dim myFormControl1 As CheckBox
Dim answer As Integer

'Set myFormControl = ActiveSheet.Shapes("hmo_checkbox").OLEFormat.Object
'Set myFormControl1 = ActiveSheet.Shapes("ny_dbl_checkbox").OLEFormat.Object

Unprotect
'Vol basic Life Unitodd checkbox'

If Not IsEmpty(Range("D14")) And Not IsEmpty(Range("D15")) And Not IsEmpty(Range("D16")) Then

'Range("vbl_unitsof_indicator").Value = True
ActiveSheet.CheckBoxes("vbl_unitsof_indicator").Value = True '

Else
'Range("vbl_unitsof_indicator").Value = False
ActiveSheet.CheckBoxes("vbl_unitsof_indicator").Value = False
End If

End Sub