CODEWITHSUNDEEP
vbaexcelcheckbox
elfakrs
elfakrs

Reputation: 11

Disable checkboxes when sheet opened in vba

I have 3 checkboxes. When a user opened my sheet, he/she must not check checkboxes. I want them to be disabled. How can I do that?

Upvotes: 0

Views: 2911

Answers (2)

Origamer7
Origamer7

Reputation: 345

You have to write some VBA code in order to do that.

Let's suppose that you have 3 CheckBoxes in your first sheet.

enter image description here

By holding the "Alt" key on your keyboard and the pressing one time the "F11" key, opens Microsoft Visual Basic. ( Alt + F11 )

At your left hand you can see the "VBAProject" tree.

Double click on the "ThisWorkbook" file and copy the following code in the window it will appear:

Private Sub Workbook_Open()
   void = uncheckAllCheckboxes()
End Sub


Function uncheckAllCheckboxes()
   ThisWorkbook.Worksheets(1).CheckBox1.Value = False
   ThisWorkbook.Worksheets(1).CheckBox2.Value = False
   ThisWorkbook.Worksheets(1).CheckBox3.Value = False
End Function

Save the excel file as "Excel 97-2003 Workbook" type (.xls)

Close your excel.

Open the file you have previously saved and everything will work fine.

;)

P.S.: It is important to enable macros from your excel settings

Upvotes: 0

Meir Gabay
Meir Gabay

Reputation: 3316

Not sure if you meant ActiveX or FormControl, so here you go

Code

Private Sub Worksheet_Activate()
  Dim myActiveX As Object
  Set myActiveX = Sheet1.OLEObjects("CheckBox1")
  myActiveX.Object.Value = True
  myActiveX.Object.Locked = False ' Make it False if you wish to enable it
  myActiveX.Object.Enabled = False ' Another option to disable

  Dim myFormControl As CheckBox
  Set myFormControl = ActiveSheet.Shapes("Check Box 1").OLEFormat.Object
  myFormControl.Value = True
  myFormControl.Enabled = False ' Make it True if you wish to enable it
End Sub

Live GIF demo enter image description here

Upvotes: 2

Related Questions