unhiding worksheet

KennyA

Board Regular
Joined
Jul 1, 2015
Messages
84
I have a macro the will hide a sheet but will not unhide the sheet. I also get Run-Time error 1004 Application-defined or object-defined error.
When stepping through the code it actually skips past: Sheets("PriceTables").Visible = True

Using excel 365

Sub SheetVisibility()
' Defines variables
Dim pWord As String


' Specifies the desired password response
pWord = "password"

' If the user enters the correct password in the input box then...
If InputBox("Please enter password", "Enter Password") = pWord Then
' If PriceTables Sheet is very hidden then...
If Sheets("PriceTables").Visible = xlVeryHidden Then
' Unhide PriceTables Sheet
Sheets("PriceTables").Visible = True
' Else if PriceTables Sheet is not very hidden then...
Else
' Make PriceTables Sheet very hidden
Sheets("PriceTables").Visible = xlVeryHidden
End If
' Else if the user enters the wrong password in the input box then...
Else
' Display an error stating the password was wrong
MsgBox "That is not the correct password", vbOKOnly, "Incorrect Password"
End If
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,452
Office Version
  1. 365
Platform
  1. Windows
It should be xlSheetVeryHidden
 

KennyA

Board Regular
Joined
Jul 1, 2015
Messages
84
I made it xlSheetVeryHidden but all of the same problems still exist
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,452
Office Version
  1. 365
Platform
  1. Windows
If it skips past this line Sheets("PriceTables").Visible = Truethen sheet is not very hidden.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,710
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Try this
Code:
If Sheets("PriceTables").Visible = xlSheetVeryHidden Then Sheets("PriceTables").Visible = xlSheetVisible
 

KennyA

Board Regular
Joined
Jul 1, 2015
Messages
84
Fluff, I looked and since I can't unhide the hidden sheet from the <Home<Format<Hide & Unhide menu the sheet has to be very hidden correct? Unhide is greyed out.

Jolivanes, I made your change and all the original problems still persist.

Is there some way I can change the code to simply show the hidden sheet?
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Do you have the workbook structure protected ? (Review RibbonTab > Protect Workbook)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,452
Office Version
  1. 365
Platform
  1. Windows
In the VB Editor click on the sheet in project window & look at the properties, what does it say against visible?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,452
Office Version
  1. 365
Platform
  1. Windows
In that case it sound like Jaafar Tribak is right, in that the workbook is protected.
If it is protected does it need a password to unlock it?
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,324
Messages
5,635,612
Members
416,869
Latest member
JeffK26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top