xlSheetVeryHidden Not Working At all

[Buddy8]

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

[gmr4evr1]

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

Please Login or Register  to view this content.

[Buddy8]

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

[gmr4evr1]

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...

Please Login or Register  to view this content.

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

[Buddy8]

Thanks gmr4ever1 for your most recent response. I am very thankful to you for all the efforts you are making to help me. I really appreciate it all.

I have tried the new code that you provided. When I try it out, I have the following experience:

1. When the workbook is opened, it is only the Main Office sheet that is visible. The 'Services' sheet is not visible and it is Very Hidden. Also if you right-click on the Main Office tab, you will observe that 'unhide' is greyed out. This outcome is perfectly OK. I presume that the code on ThisWorkbook is responsible for this outcome. Great!

2. However, when on the 'Main Office' sheet, if I click on the button to go to the 'Services' sheet, the action is correctly executed, and I am able to go to the 'Services' sheet. At this point, the Services sheet becomes the only visible sheet and the Main Office sheet becomes hidden. But while the Main Office sheet is hidden, if you then right-click on the 'Services' sheet, you will find that 'hidden' is NOT greyed out. In otherwords, you will be able to click on 'hidden' and from there unhide the Main Office sheet - even though you are on the Services sheet. What I want is that while you are on 'Services' sheet, if you right-click on the Services sheet tab, 'unhide' should also be greyed out - making it impossible to unhide the Main Office sheet at that stage.

3. I also observe that if you are on the 'Services' sheet and click on the button to move back to Main Office sheet, the moving action will be executed OK. But unfortunately if, at this time, while you are on the Main Office sheet, you right-click on the Main Office sheet tab, you will find that 'unhide' is NOT greyed out - the same problem as in No. 2 above. It appears that when you right-click on the visible Main Office Sheet tab when the workbook is initially opened, that is the only time 'unhide' is greyed out and you can't access/unhide any other sheet in the workbook.

What I want, please, is that whatever sheet you are on (which is visible), if you right-click on the tab of that sheet, 'unhide' should be greyed out in respect of the other hidden sheets (i.e. the other hidden sheets should really be Very Hidden).

Meanwhile, apart from the code in ThisWorkbook, I just wish to add that the other two sheets, each, has a code on the sheet. I don't know if the way those codes are crafted might be responsible for the problem that I am having.

I hope that this could, please, be looked at again to help me further.

Thanks.

Buddy

[Winon]

Hello Buddy8,

Try:

Code for Sheet 1;

Please Login or Register  to view this content.

Code for Sheet 2;

Please Login or Register  to view this content.

Code for ThisWorkbook Before Close;

Please Login or Register  to view this content.

Code for ThisWorkbook Open;

Please Login or Register  to view this content.

Regards.

[Buddy8]

Hi Winon,

Very grateful to you for your excellent codes. I have tested them, and they are exactly what I need. Thanks very much for your help.

One final thing on this matter: I want to ensure that when users open the workbook, they will have to 'enable' macros to be able to get the workbook function properly.

1. I am thinking of adding another sheet to the workbook and give it any name, e.g. 'Macros'.

2. That sheet will have the following inscription on it: "Please click on 'Option' above. And in the 'Security Alert' window that opens, choose 'Enable this Content', and click OK".

3. When the workbook is opened, the said 'Macros' sheet will be the only visible sheet. And as soon a user follows the instructions and enables macros, the 'Macros' sheet disappears (becomes Very Hidden), and the 'Main Office' sheet becomes visible, as it does now with your new codes.

I'll appreciate your further help with this, please.

Again, thanks for your support.

Buddy

[Winon]

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.

[Buddy8]

Hi Winon,

Thanks for your help.

I have tried the sample sheet that you uploaded. I found a slight problem with it: When I click to open the file, it is the MAIN OFFICE sheet that is visible. And when I click Options and enable macros, it then takes me to Sheet 3.

It should be the other way round: When the file is clicked to open, Sheet 3 should be the initial visible sheet. And when Macros are enabled and 'Proceed' is clicked, then Sheet 3 should disappear (Very Hidden) and MAIN OFFICE sheet becomes visible.

I should be very grateful if you would, kindly, look at it again. We're almost there.

Again, thanks for your kind help.

Buddy

[Winon]

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.

[Buddy8]

Hi Winon,

Brilliant! That works perfectly OK.

I am very grateful to you for all your help and patience. Very kind of you.

Buddy

[Winon]

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.

[Winon]

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.

[Buddy8]

Thanks very much Winon for your latest amendment to the file. I have tested it, including the two Exit buttons on both Sheet3 and the Main Office sheet, respectively.

i discovered though that if you are on the Main Office sheet and click on the Exit button, the application goes, but Microsoft Excel will still be showing. A user will have to click the 'x' button at the top right corner of Excel in order to close down the application/workbook and exit Excel. The same thing happens when you are on Sheet3 and click the Exit button on it.

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.

Thanks for your continued support.

Kind Regards.

Buddy

[Winon]

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,

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Regards.

[Buddy8]

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

[Winon]

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.

[Buddy8]

Thanks very much Winon for all your help. It works perfectly OK.

Thanks also for your thoughtfulness and good wishes. You've really been very amazing.

Buddy