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

dafan · Jul 20, 2008

I assume your checkbox is on another sheet. By using sheets("TPM") you're making that your activesheet, so Excel can't find the shape on that sheet. Try changing Activesheet to Sheets("sheetname") where sheetname is the sheet where your checkboxes are located.

You can try to make a For-loop to hide all (hide is visible=false btw).

Code:

If Sheets("TPM").Range("AL13").Value = 2 Then
For i = 391 to 426
Sheets("name").Shapen("Check Box" & i).Visible = False
Next i
End if

jkharmer · Jul 20, 2008

No good I am afraid, saying that the item with the specified name could not be found.

Any other ideas?

Thanks for trying, James

dafan · Jul 20, 2008

Sorry

Code:

Sheets("name").Shapen("Check Box" & i).Visible = False

should be

Code:

Sheets("name").Shapen("Check Box " & i).Visible = False

Forgot the space between Box & number.

pbt · Jul 20, 2008

Psst, dafan

Shouldn't Sheets("name").Shapen

Be Sheets("name").ShapeS

jkharmer · Jul 20, 2008

I did clock that one, and it made no difference changing it!

cadence · Jul 25, 2008

Hey guys did anyone have joy solving this as its great but I am getting also not getting a result.

jkharmer · Jul 25, 2008

Nope, could not get it to work. Mine are still visible at the mo, even though the rows they are occupying are hidden. They seem to stack themselves up on top of one another. Would be good if someone could come up with a way to hide them, though.

James

Tom Urtis · Jul 25, 2008

This sounds do-able but there are several pieces to the puzzle that are missing, examples:

- Were the checkboxes created from the Forms toolbar or the activex control toolbox.

- "Ideally I want to hide Check boxes 391 through to 426, using a single macro."
What does that mean? When cell AL13 = 2? Or when each cell in AL13:AL? = 2 for checkboxes that are in a cell on each row somewhere?

- How does the 2 get into whatever cell(s) it is in...is it manually entered? ...the result of a formula?...imported from a web query?...copied and pasted from somewhere?

- Are you positive (positive means you looked at every checkbox as you stepped through them) that you really do have 36 of them numbered and named exactly in the syntax
Check Box 391
Check Box 392
etc
with a space between Check and Box and number.

So with that info and anything else you can think of to round out the spreadsheet description, this should be solvable, just too many undefined possibilities at the moment.

cadence · Jul 25, 2008

I have tried to create mine the same as jkhammer,

Yes mine are Form Toolbar
For i = 1 to 12
Yes each one is called Check Box 1 ... Check Box 2 ... Check Box 12

I suspect that AL13 = 2: the 2 is the value from say a drop down that will then hide all of those in the range

for me it seem to be the line
ActiveSheet.Shapes("Check Box " & i).Visible = False 'slightly tweaked

that causes the trouble and jkhammer correct me if I'm wrong the same as well?

I get a run time error 5.

thanks Tom

Hiding Checkboxes?

jkharmer

Board Regular

Excel Facts

dafan

Well-known Member

jkharmer

Board Regular

dafan

Well-known Member

pbt

Well-known Member

jkharmer

Board Regular

cadence

Well-known Member

jkharmer

Board Regular

Tom Urtis

MrExcel MVP

cadence

Well-known Member

Similar threads

Forum statistics

Share this page

Hiding Checkboxes?

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

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