Using CheckBox.Caption to determine the workseet name

**nicer2011** · 12-20-2011, 09:25 PM

Hi,

I have many checkboxes named under the file's worksheets. May I use CheckBox.Caption in order to go to a worksheet?

My suggestion would be like that, but it's not working:

Please Login or Register  to view this content.

See an example in the file attached.

Thanks for your help!

**mikerickson** · 12-20-2011, 09:59 PM

What happened when you tried it?

Oh, I see the problem. As written "ActiveSheet.OLEObjects(i).Object.Caption" is being passed as a literal string.
If you want to go to the sheet whose name is the caption, use this

Please Login or Register  to view this content.

**nicer2011** · 12-20-2011, 10:33 PM

I still have an error:

Run time error 9: Subscript out of range

Thanks

**protonLeah** · 12-20-2011, 11:52 PM

Check the sheet names against the checkbox captions:

Please Login or Register  to view this content.

Results:

Please Login or Register  to view this content.

Notice the spaces at the beginning of the actual sheet names, ergo, the caption <> sheet.name

**nicer2011** · 12-21-2011, 08:04 AM

Hi,

Thanks for your suggestion, protonLeah. I've changed the space at the beginning of the actual sheet names, but still not working - error 1004 this time

Thanks