Excel unhide sheet by codename

Question

I use a Userform with command buttons that Unhides veryhidden sheets and then selects them by the sheet's codename so i can make edits to the sheet and then i hide them with another command button when i am done making the changes to the sheet.

But when i have (2) of the workbooks with different filenames open at once i get an the error.

Sub Show_Sheet()

BlankOptionSheet.Visible = -xlSheetVisible
BlankOptionSheet.Select

End Sub

Avoid to use `select´. Try to define the sheet instead. http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Niclas, Nov 10 '16 at 21:59
Is the worksheet accessed by its code name `BlankOptionSheet`? — Mathieu Guindon, Nov 10 '16 at 22:18
Can you clarify "more than 1 of the same workbooks open at once"? because Excel doesn't let you open two instances of the same workbook, you get a "workbook is already open" message. — Mathieu Guindon, Nov 10 '16 at 22:22
@Niclas - selecting the sheet is the *point* of the code: no reason to avoid it here... — Tim Williams, Nov 10 '16 at 22:26
i use `.Show(vbModeless)` i guess that makes it Modeless?. dose StackExchange have a website that you can upload file to be reviewed? — luke, Nov 10 '16 at 23:11

score 4 · Accepted Answer · answered Nov 10 '16 at 22:55

4

If the Userform is in the workbook that is not currently Active at the time, the code will fail because you can't Select a Worksheet that is not in the ActiveWorkbook. You should Activate the Sheet instead of Selecting it:

Sub Show_Sheet()

    BlankOptionSheet.Visible = -xlSheetVisible
    BlankOptionSheet.Activate

End Sub

answered Nov 10 '16 at 22:55

YowE3K

23,852
7
26
40

that keeps the error from happening but when i run the code from the userform it's only changing one of the workbooks even if i close it and reopen it from the other workbook. i use `Call BidSheet_User_Form.Show(vbModeless)` to open it – luke Nov 10 '16 at 23:05
@luke it's always only ever going to work with one workbook. You need to get a handle on the other workbook for it to work on both. – Mathieu Guindon Nov 10 '16 at 23:35
@Mat'sMug changing `BlankOptionSheet.Select` to `BlankOptionSheet.Activate fixed the error, and it work fine now, i was using a shortcut key to open the userform and thats why it was still running the code from the other workbook. – luke Nov 10 '16 at 23:39
1

I'm still unsure exactly what you're doing, but remember to mark the most helpful answer as accepted if everything works now. If you can share the working code I'd also suggest you put it up on [codereview.se] to get it peer reviewed and improved. Cheers! – Mathieu Guindon Nov 10 '16 at 23:42

cullan · Answer 2 · 2016-11-10T23:31:44.403

2

You can reference the activeworkbook and the specific worksheet you want to hide by using:

ActiveWorkbook.Worksheets("nameOfSheet").Visible = False

Then turn it back on by:

ActiveWorkbook.Worksheets("nameOfSheet").Visible = True

Alternatively, you can use the code name of the sheet as well.

ActiveWorkbook.BlankOptionSheet.Visible = True
ActiveWorkbook.BlankOptionSheet.Visible = False

edited Nov 10 '16 at 23:31

answered Nov 10 '16 at 22:02

cullan

280
3
13

I'd upvote this answer if it explained why the worksheet can't be accessed by its global instance / with its `CodeName` as the OP asks. I'm sure this code works, but my understanding is that accessing a worksheet by its `CodeName` is more robust, and implicitly works off the `ActiveWorkbook` anyway. – Mathieu Guindon Nov 10 '16 at 22:50
Yes that would definitely work. Let me revise. I used the sheet name because if you're running excel on OS X, it doesn't allow you to change the codename. – cullan Nov 10 '16 at 23:30
1

@Mat'sMug - `"implicitly works off the ActiveWorkbook"` - I think, because the `CodeName` is referring to an object **within** a Workbook object, it would implicitly work off ThisWorkbook. To access an object in another workbook (e.g. the ActiveWorkbook) you would need to use `ActiveWorkbook.BlankOptionSheet`. – YowE3K Nov 10 '16 at 23:57
Correct. Although this works via late-binding. Upvoted! – Mathieu Guindon Nov 11 '16 at 00:12
`ActiveWorkbook.BlankOptionSheet` doesn't give you a compile-time error "Method or Data member not found" ? Even `ThisWorkbook.BlankOptionSheet` will give you the same error... – Tim Williams Nov 11 '16 at 06:36

score 2 · Answer 3 · edited Nov 10 '16 at 23:31

2

See this quote:

The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides.

From ozgrid.com

edited Nov 10 '16 at 23:31

Mathieu Guindon

69,817
8
107
235

answered Nov 10 '16 at 22:27

Brian

2,078
1
15
28

@Mat'sMug I understand now. My sheet name and code name were the same. That's why it worked. I will try to change my answer. Thanks for the reply. – Brian Nov 10 '16 at 22:41
Assuming OP is trying to unhide and activate/select a sheet in *the other* workbook, this is a major part of the answer. Well done! – Mathieu Guindon Nov 10 '16 at 23:33

Excel unhide sheet by codename

3 Answers3