how can I keep the hidden sheet hidden?

S

smokenack

Active Member
Joined
Dec 12, 2003
Messages
351
Using some very useful code found on this message board I have set up a user log situated on a hidden sheet. For this to work I need users to enable macros and have used the following to code to attempt to force them to do so:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideSheets
End Sub

Private sub Workbook_Open()
UnhideSheets
End Sub

Private Sub HideSheets()
Dim sht As Object
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub


Private Sub UnHideSheets()
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

This works fine except that it unhides the hidden sheet containing the user
log. I need this to remain hidden to give me the ammunition to prove that the file isn't being used.

thanks for looking

Nick
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
I have managed to change the last part of the code as follows:

Private Sub UnHideSheets()
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "TimeLog" Then sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True

End Sub

This keeps the user log hidden but it's hidden from me as well. My attempts to write a macro (with no vba knowledge) to unhide that sheet for my eyes only have been totally unsuccessful.
Can anyone help?

Nick
 
Upvote 0
You can try this, replace your current unhide sheet with this one: it ahs the line in blue added on,

Code: 
Private Sub UnHideSheets()
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Log Sheet").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub


ThisWorkbook.Sheets("Log Sheet").Visible = xlSheetVeryHidden


you will have to change the name Log Sheet to whatever you sheet name is,


Code: 
Sub ShowLog()
Sheets("LogSheet").Visible = xlSheetVisible
End Sub

Sub HideLog()
Sheets("LogSheet").Visible = xlSheetVeryHidden
End Sub

Then you can run these 2 codes to unhide and re-hide it for yuo to view, assign these 2 codes to shortcut keys that only you know and can easily remember
 
Upvote 0
Did you try the last 2 codes from my post, those will unhide and re-hide the sheet whenever you run the codes, do you need another way of doing this?
 
Upvote 0
Thanks Justin. For some reason the last two codes didn't show up on your post. (or I'm going mad or blind). I'll try them now.
Thanks again for your help

Nick
 
Upvote 0
Brilliant Justin, worked perfectly. I've bought the VBA book and hopefully soon I'll have to rely on you and your peers less and maybe even offer assistance myself.

thanks again

Nick
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA code not working on Mac
Replies
7
Views
644
Jim Gordon
J
P
  • Solved
Print multiple hidden worksheets but save as single file
2 3
Replies
24
Views
823
jolivanes
jolivanes
B
  • Solved
Combining vba
Replies
1
Views
386
Joe4
Joe4
B
  • Question
Delete sheets on password expiry
Replies
3
Views
364
bptaw
B
rjplante
  • Solved
Change from Set as Default to Set as Active printer
Replies
1
Views
267
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,912
Messages
6,127,685
Members
449,398
Latest member
m_a_advisoryforall

Share this page

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
Back
Top