How to Keep the ActiveX Checkbox keep checked on Excel file opens

[Sandeep H C]

Heloo All,

How can i make the check box to keep always in the checked state when i open the excel workbook.

Thank You
Sandeep H C

 

[Luke M]

I would suggest having the checkbox linked to a cell (could be a hidden cell somewhere). This way, the checkbox's value is stored into a "spot", and when the workbook re-opens, it looks at the linked cell, and goes "Oh, I should be True right now!" and does so.

 

[Sandeep H C]

Luke thanks for replying.

Please look into the below file i require to hide the comments section on every close of the excel so that when a user open the dashboard the comments section should be hidden it should be open only when the check box is clicked.

 

[Luke M]

Ah, I see what you mean. In the ThisWorkbook module of your code, put this snippet:

Private Sub Workbook_Open()
'Make sure page is properly setup
Application.ScreenUpdating = False
With Worksheets("Dashboard")
    .Range("L:S").EntireColumn.Hidden = True
    .OLEObjects("CheckBox1").Object = False
End With
Application.ScreenUpdating = True
End Sub

This will run when the workbook is opened, and makes sures that the comments are hidden.

 

[Sandeep H C]

Luke boss.


I tried to place this snippet in the Workbook module but this does not worked can you please help again.


I will explain the case.

User: Open the Dashboard. Looks at the value on a selected date. If the Values for Dept comes in the red color the dept has to provide the reason in the data, so if the user want to see those comment he has to check the check box so that columns with the comments has to be displayed.

so after the user as completed the data visualization once he close the Dashboard. I want the checkbox to uncheck automatically irrespective of last user inputs.

Luke thanks for your time and support.

Regards
Sandeep

 

[Luke M]

What happened when you re-opened the workbook? Did the code not run, or did something unexpected?

 

[Sandeep H C]

When I opened the workbook and ran the code.and opened the workbook the check box was in the checked state.

 

[Luke M]

Can you verify that you put the code in the ThisWorkbook module, and not in a regular module?

Also check if macros are disabled for some reason?