Hide and Unhide sheet using code

[ABabeNChrist]

Could I add some additional code that will allow me to unhide a selected sheet from a UserForm?
Here is the code I use

Code: Select all

Worksheets("Bathroom(s)").Select
ActiveSheet.Unprotect Password:=""
    Application.Goto Worksheets("Bathroom(s)").Range("A1"), Scroll:=True
    UserForm8.Hide
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""

I would also like to be able to hide when I use this code to return to UserForm.
I use this code to return to UserForm

Code: Select all

UserForm8.Show

[HansV]

1. You can use code like

Worksheets("Bathroom(s)").Visible = xlSheetVisible

to unhide a worksheet. Note that you can't use ActiveSheet here, because the active sheet can't be a hidden sheet.

2. You could hide the sheet before showing the userform:

Worksheets("Bathroom(s)").Visible = xlSheetHidden
UserForm8.Show

[ABabeNChrist]

Thank you HansV
As always, it works great

[ABabeNChrist]

Hi HansV
I seem to be having a problem with opening other worksheets from my UserForm. It works great when I use with it for one sheet but when I try to use the same code on a different sheet names I get an error 1004 Method 'Goto of object _ Application failed.
on this line

Code: Select all

    Application.Goto Worksheets("Bathroom(s)").Range("A1"), Scroll:=True

[HansV]

Are you sure that the sheet name is spelled correctly and that it is a sheet in the same workbook?

[ABabeNChrist]

I checked the spelling and yes it is in the same workbook.
I probably applied the code incorrectly
here is how I changed it

Code: Select all

Worksheets("Bathroom(s)").Visible = xlSheetVisible
ActiveSheet.Unprotect Password:=""
    Application.Goto Worksheets("Bathroom(s)").Range("A1"), Scroll:=True
    UserForm8.Hide
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""

I no you mentioned about ActiveSheet, I wasnt totally sure if that included sheet protection

[HansV]

Try replacing ActiveSheet with Worksheets("Bathroom(s)")

[ABabeNChrist]

Hi HansV
I believe I got it
I replaced ActiveSheet with Worksheets("Bathroom(s)) on the Unprotect Password line.
It now seems to work for each sheet, hiding and unhiding.
I took it for a test drive, opening closing, here and there, seems all good
Do you think I should do the same for the bottom line that adds protection or is not needed

Code: Select all

Worksheets("Bathroom(s)").Visible = xlSheetVisible
Worksheets("Bathroom(s)").Unprotect Password:=""
    Application.Goto Worksheets("Bathroom(s)").Range("A1"), Scroll:=True
    UserForm8.Hide
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""

[HansV]

It's not really necessary to change ActiveSheet in the line that protects the sheet, because the line with Application.GoTo has made Worksheets("Bathroom(s)") the active sheet. But you could still do it to make the code more uniform. You could even use

Code: Select all

With Worksheets("Bathroom(s)")
  .Visible = xlSheetVisible
  .Unprotect Password:=""
  Application.Goto .Range("A1"), Scroll:=True
  UserForm8.Hide
  .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""
End With

[ABabeNChrist]

Thanks you HansV
most greatly appreciated

[ABabeNChrist]

Hi HansV
I have been using this code for a couple days and it seemed to be working great, but I noticed yesterday when I was navigating through different sheets and UserForms. I would develop a critical error that would close up my workbook, this does not occur all time, but it does only occur when I use this code to open sheet.

Code: Select all

Worksheets("Cover Page").Visible = xlSheetVisible
Worksheets("Cover Page ").Unprotect Password:=""
Application.Goto Worksheets("Cover Page ").Range("A1"), Scroll:=True
UserForm8.Hide
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=""

I also tried to change the ActiveSheet to Worksheets to help make it more uniform, I tried and then developed an error on .Protection
This morning I tried to rein act the error and of course I failed. I was trying to receive what type of error it actually was. I know it was serious enough to close my workbook.
I know there are a lot of different codes I can use that will appear to work OK
I am now trying this code at the moment and it seems to be OK. I eliminated the protection part of the code
Do you have any idea why this error has been occurring? And do you believe this new code may be a better approach to opening a hidden sheet

[HansV]

I notice that you refer first to Worksheets("Cover Page"), then to Worksheets("Cover Page ") with a space after Page.

[ABabeNChrist]

I'm sorry that was just a type O on this thread, the code is correct in workbook
Here is the newer version I was trying, It seems to work OK. I'm just trying to find the best approach with least amount of possible problems

Code: Select all

Worksheets("Cover Page").Visible = xlSheetVisible
Worksheets("Cover Page").Select
    UserForm8.Hide

[HansV]

That code is definitely simpler.

[ABabeNChrist]

Thank you HansV
But do you have any idea why I got such a serious error, could it of been the code, or unknown mystery?

[HansV]

I'm sorry, I don't really know.

[ABabeNChrist]

Thank you HansV
I'll label it as a mystery, and see how this simpler code appoach works

[StuartR]

I have had all sorts of problems with hidden userforms. Do you need to be able to access information stored in the userform when it is hidden? If not then you may want to consider using
Unload UserForm8
instead of
UserForm8.Hide

[ABabeNChrist]

Thank you StuartR
Thats a good idea, I have like 6 UserForms (5 for either navagting or printing) and only one is used to access information.

[ABabeNChrist]

This is just a follow up; I seem to have solved my issue with receiving errors. What I did was add some additional code that would select a single unlocked cell (not a merged cell). I then applied this to each of my sheets and now my problem seems to be gone.
Here is how it my code looks now.

Code: Select all

Worksheets("Utilities").Visible = xlSheetVisible
Worksheets("Utilities").Select
ActiveSheet.Range("AE7").Select
  Unload UserForm8

As a matter of fact everything is working great, Thank you everyone who has helped