xlSheetVeryHidden Not Working At all

Dear All,

Please find attached a sample file that demonstrates the problem that I need help with.

1. The workbook contains two worksheets, named MAIN OFFICE and SERVICES.

2. My desire is that when I double click to open the file, it is the MAIN OFFICE sheet that should be visible. The SERVICES sheet should be VeryHidden (i.e. xlSheetVeryHidden). At the moment, the first part works OK (see the codes in ThisWorkbook) - when I open the file, it is only the Main Office sheet that is visible. But although the Services sheet is hidden, it is not VeryHidden. This means that while on the Main Office sheet, a user can easily right-click and 'unhide' the Services sheet. I want to ensure that this is not possible.

3.Also when a user is on the Main Office sheet, and clicks the button to move to the Services sheet, the Main Office sheet should be VeryHidden while the Services sheet is visible. Again, making the Services sheet visible while hiding the Main Office sheet works OK. But I cannot get the Main Office sheet to be VeryHidden when the Services sheet is visible.

4. Making one sheet VeryHidden while the other sheet is visible is what I need help with, please, and I hope that someone will be able to tweak the codes that I have in ThisWorkbook and the two worksheets to enable me to get the result that I want.

Thanks all.

Buddy

The problem is that your Before close code is canceling the Open code out. Try this instead

Private Sub Workbook_Open()
If Worksheets("SERVICES").Visible = True Then
   Worksheets("Main Office").Visible = True
  Worksheets("SERVICES").Visible = xlVeryHidden
End If
End Sub

Thanks gmr4ever1 for your kind response.

I have tried the code that you provided, but it is not solving the problem. I'd appreciate if you would, please, check the code and kindly try it out, if possible, on the sample file that I attached.

I replaced your code with the 'Workbook Open' code that I have in ThisWorkbook. However, when I am on any of the two sheets and right-click on the name of the sheet, the 'unhide' for the other sheet shows, making it possible to unhide. I expect that the 'unhide' in each case should be greyed out.

Could you also, please, examine the codes that I placed on each of the two sheets too - i.e. Main Office sheet and Services sheet. In view of my baby knowledge in VBA codes, I don't know if the additional codes that I placed in Main Office and Services sheets are OK, in addition to the ones I placed in ThisWorkbook.

Thank you.

Buddy

I've had another look and I made a mistake. The code I provided will only work if the "Services" sheet is not hidden when the workbook opens. Attached is your updated file. I've changed that code to...

Private Sub Workbook_Open()

    Sheets("Main Office").Visible = True
    Sheets("Main Office").Activate
    Worksheets("SERVICES").Visible = xlVeryHidden
    
End Sub

When the workbook opens, this code will make the main office sheet visible if it was saved as hidden and then activate the main office sheet and make the service sheet veryhidden even if the services sheet isn't visible.
As for the code on your sheets, they look ok to me, although I'm not quite sure what the Me.Visible = False is for.
Also, and i'm not sure if this matters, but when the user clicks one of the buttons to active the other sheet, the sheet they are on will not be "very hidden".
Buddy8-xlsheetveryhidden-not-working-at-all-sample.xlsm

Hello Buddy8,

Try:

Code for Sheet 1;

Sub RoundedRectangle1_Click()
With Sheets("SERVICES")
        .Visible = True
        .Activate
    End With

Me.Visible = xlSheetVeryHidden
End Sub

Code for Sheet 2;

Sub Oval1_Click()
With Sheets("MAIN OFFICE")
        .Visible = True
        .Activate
    End With

Me.Visible = xlSheetVeryHidden
    
End Sub

Code for ThisWorkbook Before Close;

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 If Worksheets("SERVICES").Visible = True Then
 Worksheets("MAIN OFFICE").Visible = True
 Worksheets("MAIN OFFICE").Select
  Worksheets("SERVICES").Visible = xlSheetVeryHidden
End If

End Sub

Code for ThisWorkbook Open;

Private Sub Workbook_Open()
If Worksheets("SERVICES").Visible = True Then
  Worksheets("SERVICES").Visible = xlSheetVeryHidden
End If
End Sub

Regards.

Hi Buddy8,

Do you mean something like the attached sample Workbook?

Of course you may change the text on sheet 3, to your liking.

Beat Regards.

Hello Buddy8,

Thank you for your feedback.

I have tried the sample sheet that you uploaded. I found a slight problem with it...

Yeah you are correct, and it is my fault for not testing it extensively first

Sorry about that! Most of the problems turned out to be the stacking of the Code. I have also made some other changes, and the attached sample Workbook should work now without a problem.

Regards.

Hi Buddy8,

You are welcome, glad I could help.

Also, thank you for adding to my Reputation.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks.

Hello Buddy8,

I have taken the liberty of adding quite a few changes to your Workbook, and I would like your feedback on the new attached sample of same.

Regards.

Hello Buddy8,

Thank you for the feedback.

If it is possible, I would like either of the Exit buttons to SAVE the workbook and completely exit both the application/workbook and Excel.

To accomplish this we need to change the ThisWorkbook Before Close Code to,

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If Not ThisWorkbook.Saved Then
        Cancel = True
         Worksheets("MAIN OFFICE").Visible = xlSheetVisible
         Worksheets("SERVICES").Visible = xlSheetVeryHidden
         Sheet3.Visible = xlSheetVeryHidden
 Sheet1.Select
    MsgBox "Please use the 'Exit' Button provided, to Close this Workbook", vbOKOnly + vbExclamation, ("Procedure Control")
    ThisWorkbook.Save
    ElseIf ThisWorkbook.Saved = True Then
        Cancel = False
    ThisWorkbook.Save
    Quit
    End If

End Sub

For the "Exit" Button, we have a Standard module named "Quit", with Code as follow:

Sub Quit()
Application.ScreenUpdating = False
 Application.EnableEvents = False
  Application.Quit
    ThisWorkbook.Close SaveChanges:=True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

To test it, please try the revised attached sample Workbook.

Regards.

Hi Winon,

I am sorry, please, that I am coming back to you, two days late, with this feedback. My health was a bit poorly, but I am feeling a bit better now.

You are really a genius!

Thanks very much for the amended codes. The file now exits completely with each of the exit buttons.

There is only one little final adjustment, please, that I'll like you to kindly do for me. When the Exit button on 'Sheet3' i.e. the Macro Enabling instruction sheet is clicked, the file closes OK. And when you start the application again, the first sheet that comes up is the Sheet3 (i.e. Macro-enabling instruction sheet). This is Perfect!

However, when you click the Exit button on the Main Office sheet, and start the application again, the first sheet that comes up is the Main Office sheet instead of the Sheet3 (the Macro-enabling instruction sheet). Please this is the only final adjustment i.e. When a user exits the application using the Main Office 'Exit' button, and then restarts the application, the user should be taken to Sheet3 (the Macro enabling instruction sheet) and not straight to the Main Office sheet.

Again, thank you Winon for all your kindness and support. You have been wonderfully helpful.

Buddy

Hello Buddy8,

Good to hear your health has improved.

Thank you for the feedback.

... the first sheet that comes up is the Main Office sheet instead of the Sheet3 (the Macro-enabling instruction sheet).

It is most likely that you may have your Macro Security Settings set to "Disable All Macros Enabled Workbooks Without warning"

I have changed the Code in the Workbook, to comply with your request.

Take care of your health

Regards.