enabling a checkbox based on conditions

Nikhil2803

New Member
Joined
Jul 18, 2023
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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.
1695190622574.png

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
1695190788693.png

Your any valuable inputs will be highly appreciated. Thank you
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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 ?

VBA Code:
ActiveSheet.CheckBoxes("vbl_unitsof_indicator").Value = True
 
Upvote 0
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 ?

VBA Code:
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.
 
Upvote 0
ok, so sounds like its more to do with the code you have written inside this

VBA Code:
Sub Worksheet_Change(ByVal Target As Range)

might you be able to share it ?
thanks
Rob
 
Upvote 0
ok, so sounds like its more to do with the code you have written inside this

VBA Code:
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.
 
Upvote 0
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 ?

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,914
Messages
6,175,351
Members
452,638
Latest member
Oluwabukunmi

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