Hiding Checkboxes?

jkharmer · Jul 20, 2008

Hi,

I am trying to use the following code:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> ActiveSheet.Shapes("Check Box 391").Visible = True

and modifying it to:

If Sheets("TPM").Range("al13").Value = 2 Then ActiveSheet.Shapes("Check Box 391").Visible = True

The first one works OK, but when I try to add an if / then statement, it gives me an error message.

Can this be done? Ideally I want to hide Check boxes 391 through to 426, using a single macro.

Any help would be great!

James

Tom Urtis · Jul 25, 2008

I do not get a run time error when I follow the steps you took, which are:

In cell AL13, some value gets manually entered, either by someone typing in the value or selecting it from data validation.

When the value entered into cell AL13 is 2 then 12 Forms checkboxes named Check Box 1, Check Box 2, etc up to Check Box 12 are hidden.

When the value entered into cell AL13 is anything other than 2 then 12 Forms checkboxes named Check Box 1, Check Box 2, etc up to Check Box 12 are made to be visible again.

I just created and named 12 forms checkboxes that way and this tested fine for me, no problem.

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$AL$13" Then Exit Sub
Dim i%
If Target.Value = 2 Then
For i = 1 To 12
ActiveSheet.Shapes("Check Box " & i).Visible = False
Next
Else
For i = 1 To 12
ActiveSheet.Shapes("Check Box " & i).Visible = True
Next
End If
End Sub

jkharmer · Jul 25, 2008

Cadence,

You are 100% correct. 2 is a value from a dropdown (if I select "No", teh value is 1, if I select "Yes", the value is 2.

I also get a runtime error 5 message.

James

Tom Urtis · Jul 25, 2008

Then for your situation, try this, also in the worksheet module, see my post above for directions on where to place this if you don't know.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$AL$13" Then Exit Sub
Dim i%
Application.ScreenUpdating = False
If Target.Value = "Yes" Then
For i = 391 To 426
ActiveSheet.Shapes("Check Box " & i).Visible = False
Next
Else
For i = 391 To 426
ActiveSheet.Shapes("Check Box " & i).Visible = True
Next
End If
Application.ScreenUpdating = True
End Sub

jkharmer · Jul 25, 2008

Hi Tom,

Does a private sub run in the background of the worksheet, as I can not seem to assign it to my dropdown box?

James

Tom Urtis · Jul 25, 2008

Repeating what I posted earlier:

Right click on your sheet tab (for the sheet where these checkboxes are), left click on View Code, and paste the procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

jkharmer · Jul 25, 2008

Yep, Tom.

Done all that, but what makes the macro work, does it sense when I change my dropdown from one value to the other?

James

Tom Urtis · Jul 25, 2008

Yes because you are using a Change event for that worksheet, to wit
Private Sub Worksheet_Change(ByVal Target As Range)

and only cell AL13 is regarded, to wit
If Target.Address <> "$AL$13" Then Exit Sub

jkharmer · Jul 25, 2008

I see, I have tried it in a new worksheet, and it works fine, but that is when I create the check boxes in format control.

My checkboxes have format control applied to them, so if they are checked, you get a true, if they are not checked you get a false. I wonder if that is causing any issues?

jkharmer · Jul 25, 2008

Bizarre, opened up another new sheet and it works fine, even if I have applied format control. I am stumped as to why this is not working!

Tom Urtis · Jul 25, 2008

I don't know what you mean by
"when I create the check boxes in format control"
but whether a checkbox is checked or not checked has no affect on the code I posted, which was in response to the question you asked, which had to do with hiding 36 checkboxes based on
- the checkbox names (named "Check Box 391" etc)
- the type of checkbox (from the Forms toolbar)
- the value entered into cell AL13 (first you said 2, then Yes but I gave examples of both)

Hiding Checkboxes?

jkharmer

Board Regular

Tom Urtis

MrExcel MVP

Excel Facts

jkharmer

Board Regular

Tom Urtis

MrExcel MVP

jkharmer

Board Regular

Tom Urtis

MrExcel MVP

jkharmer

Board Regular

Tom Urtis

MrExcel MVP

jkharmer

Board Regular

jkharmer

Board Regular

Tom Urtis

MrExcel MVP

Similar threads

Forum statistics

Share this page

Hiding Checkboxes?

Board Regular

MrExcel MVP

Excel Facts

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock