Vba codes to hide other sheets leaving one open not working properly

  • Hi All,

    I need help with vba codes that are not working properly. I have three worksheets in a workbook namely:

    Menu
    Balance Sheet
    Income & Expenditure

    On the ‘Menu’ sheet I created a textbox button each for the other two sheets including one button for ‘Exit’:

    My aim is this: When the ‘Main Menu’ sheet is showing and I click on the button for e.g. ‘Balance Sheet’, it would take me to the ‘Balance Sheet’ sheet and hide the other sheets (i.e. the ‘Menu’ and the ‘Income & Expenditure’ sheets.

    On the ‘Balance Sheet’ sheet, I have a button for ‘Menu’, and when I click the button, it should take me back to the ‘Menu’ sheet and hide the other sheet including the ‘Balance Sheet’ Sheet itself.

    On the Menu sheet, I created textbox buttons and assigned macros to take me to the those other sheets. And on each of those other sheets I created a ‘Menu’ button to take me back to the Menu. For e.g. on the ‘Balance Sheet’ sheet, I placed the following codes:


    Code
    1. Sub TextBox1_Click()With Sheets("Balance Sheet") .Visible = True .Activate End WithMe.Visible = TrueEnd Sub  Sub Button27_Click()With Sheets("Menu") .Visible = True .Activate End With Me.Visible = FalseEnd Sub


    The first code takes me from the Menu to the Balance Sheet – hiding the Income & Expenditure Sheet but it DOES NOT HIDE the Menu sheet itself. However, the second code takes me from the ‘Balance Sheet’ back to the Menu. In this case it succeeds in hiding itself (the Balance Sheet ) and also the Income & Expenditure Sheet.

    I also placed two such codes on the ‘Income & Expenditure’ Sheet and I get the same result.

    My problem here is that I am unable to figure out the appropriate code to place in the Menu sheet or to attach to any of those other codes to achieve the ‘hiding’ of the Menu sheet. For e.g. When I click on the button for ‘Balance Sheet’ it should take me to the Balance Sheet while hiding both the Menu sheet and the Income & Expenditure sheet. The second part of it is OK i.e. when I am on the Balance Sheet and click on the Menu button, it takes me back to the Menu and successfully hides the other sheets.

    Can someone help me please.

    PS: It is possible I might have messed things up a bit by creating the Menu buttons using textbox buttons and the sheet buttons using command buttons. It is too late for me to scrap them and start all over. They work partially. All that I want is a tweaking of the codes so that when I go from the Menu, that particular sheet should be hidden along with the other sheet that I am not going to.

    Thanks.

    Buddy

  • Re: Vba codes to hide other sheets leaving one open not working properly


    Instead of using text boxes try using buttons = see the attached workbook and have a look at the code in each sheet - it should do what I think you are asking. If you need help just ask
    PS: Remember that you must always have one sheet visible so always make sure the order of your code ensures that at least one sheet is visible when hiding the other sheets otherwise you will get an error.
    I assume you want the state of the workbook when opening just to show the menu page - there is code in the OpenWorkbook Event to just show the Menu sheet and hide the others.

    Files

    • Buttons3.xlsm

      (34.43 kB, downloaded 118 times, last: )

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Vba codes to hide other sheets leaving one open not working properly


    Thanks Smuzoen for your kind response and for the attached workbook. I am sorry that I did not mention that I use Excel 2003. When I tried to convert the attached workbook from 2007 to 2003, it did but there was a message warning that it would only convert to 'read-only'. I went ahead and converted. I could therefore not get any of the buttons to work. It was only the 'Menu' that shows on the Bar below, and I also saw the buttons you created.


    I believe that the work you did would help meet my needs except that it is in 2007 format.


    Another point I wanted to clarify please, is this: With the workbook you attached - when I click a button to go to say Balance sheet and it takes me there, (I assume the 'Menu' sheet would be hidden at that point) is there a button in the Balance Sheet which when I click will take me back to the Menu? Same for Income & Expenditure?


    You also suggested that I use 'command buttons'. Do you suggest that I clear the others that I have already created and start afresh with command buttons? Can I not use the buttons that I have already created in my Menu sheet and tweak the macros to respond to those buttons?


    I hope you can help further, please.


    Thanks.


    Buddy

  • Re: Vba codes to hide other sheets leaving one open not working properly


    You are right in all your assumptions - if you go to the Balance Sheet or the income & expenditure then all the sheets are hidden except the one you selected including the menu sheet. On the Balance sheet and income and expenditure sheet there is only a button for Menu - this takes you back to menu and closes the balance sheet - essentially only one sheet is open at a time. Try this new workbook I have attached however just in case the code is as follows. The menu sheet has 3 buttons - 1 for income and expend and 1 for balance sheet and a button to exit.. So in the worksheet Menu place 3 buttons and attach the respective code


    In the balance sheet place a button and attach this code


    In the Income Expend sheet place a button and attach code

    Code
    1. Private Sub menu_Click()
    2. Application.ScreenUpdating = False
    3. Worksheets("Menu").Visible = True
    4. Worksheets("Income & Expenditure").Visible = False
    5. Worksheets("Balance Sheet").Visible = False
    6. Application.ScreenUpdating = True
    7. End Sub


    You could actually make this easier by having central code in a module however if you are starting out with VBA this probably is easier to understand. If you repeat code it could be placed into a module and you just call this code - saves writing the same code over and over - anyway this will get you on your way


    In ThisWorkbook place this code

    Code
    1. Private Sub Workbook_Open()
    2. Application.ScreenUpdating = False
    3. Worksheets("Menu").Visible = True
    4. Worksheets("Income & Expenditure").Visible = False
    5. Worksheets("Balance Sheet").Visible = False
    6. Application.ScreenUpdating = True
    7. End Sub


    You should get the idea and code from the above examples. Try attached workbook

    Files

    • Buttons3.xls

      (60.93 kB, downloaded 81 times, last: )

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Vba codes to hide other sheets leaving one open not working properly


    Hi Smuzoen,


    Thanks. Please can you kindly come to my aid. I've run into some trouble with my Excel application.


    I was still fiddling around with the first set of codes that I used (part of which I posted in my original post) as I awaited a response from the forum when I suddenly realised that I couldn't get any new Excel workbook to show the standard excel menus, toolbars, scroll bars, formatting bars, etc.


    As I was designing the first sheet, i.e. the Menu sheet, I applied a vba code to hide all the menus, toolbars, scroll bars for that first sheet ALONE (i.e. the Menu sheet). And to the 'Menu Sheet' hiding code, I added my initial codes (the ones that I included in my first post) to enable me to move from the Menu sheet to the other sheets etc. Meanwhile, when I tested it, the code for hiding the Excel menus, toolbars, etc. for the Menu sheet worked, leaving the the menus, toolbars, scroll bars for the other two sheets intact. However, when I closed the application and tried to open another new Excel workbook so that I could do some other work, I realised all subsequent new Excel workbooks that I opened came up without any Excel menus, toolbars, scroll bars. The only things that show on any new Excel sheet are the column headings and row numbers.


    I went into the vba editor, deleted the codes attached to the workbook and worksheets of the file and resaved the file. That did not help. I deleted the file and moved it to my USB key - away from my PC, and it still did not solve the problem. I deleted my entire Office 2003 and reinstalled it. And I still cannot get any new Excel workbook or sheet to show the Excel menus, toolbars, etc.


    Please could you help me so I can get Excel to show the standard menus, toolbars, scrollbars, etc. I'm lost. I don't know what to do.


    Thank you.


    Buddy

  • Re: Vba codes to hide other sheets leaving one open not working properly


    Create a macro (with another PC if necessary) reversing the code that you used to hide Excel menus, toolbars, scroll bars, formatting bars, etc.. (ex. where before was "hide" change to "show") and then launch the macro.

  • Re: Vba codes to hide other sheets leaving one open not working properly


    Thanks Rollis for your prompt response and suggestion. I put the file back onto my pc, opened it up, viewed the code and after making the adjustments, everything came back to normal, and the new Excel sheets that I opened had their menus etc. back. Again, thanks for your suggestion.

    Meanwhile, I have pasted below the code that I originally used:



    Code
    1. Private Sub Workbook_SheetActivate(ByVal Sh As Object)Dim ws As WorksheetIf ActiveSheet.Name = "MENU" ThenApplication.CommandBars("Worksheet Menu Bar").Enabled = FalseApplication.CommandBars("Standard").Visible = FalseApplication.CommandBars("Formatting").Visible = FalseWith ActiveWindow .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False End WithApplication.DisplayFormulaBar = FalseApplication.DisplayStatusBar = FalseElseApplication.CommandBars("Worksheet Menu Bar").Enabled = TrueApplication.CommandBars("Standard").Visible = TrueApplication.CommandBars("Formatting").Visible = TrueWith ActiveWindow .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True End WithApplication.DisplayFormulaBar = TrueApplication.DisplayStatusBar = TrueEnd If Application.DisplayAlerts = TrueEnd Sub



    The idea is to hide the menu, toolbars etc. ONLY for the 'Menu' sheet. I copied the code, went to the vba editor, clicked on 'ThisWorkbook', view code, and pasted it there. It meets my need - i.e. hides the menu, toolbars, etc. for the ‘Menu’ sheet, and leaves the menus, toolbars, scrollbars, etc. for the other sheets in the workbook intact - just as I want it. BUT, unfortunately, it affects all other new Excel workbooks/worksheets that I open.


    I don't know if the problem arises from me pasting it into 'ThisWorkbook'. A few minutes ago, I took the code out of 'ThisWorkbook' and attached it to the 'Menu' sheet, but it would not work.


    Please could someone kindly direct me on how best to use it - where I should place it so that it will affect ONLY the Menu sheet and not the other sheets in the workbook - and most importantly so that it does not affect any other new Excel worksheets that I might open and work on subsequently.


    Please could someone kindly review the code. If the problem lies in adding additional line(s) of code to it including the issue of the correct sheet to place the code in, please, I'd appreciate any help.


    Thanks.


    Buddy


  • Re: Vba codes to hide other sheets leaving one open not working properly


    Instead of using text boxes try using buttons = see the attached workbook and have a look at the code in each sheet - it should do what I think you are asking. If you need help just ask
    PS: Remember that you must always have one sheet visible so always make sure the order of your code ensures that at least one sheet is visible when hiding the other sheets otherwise you will get an error.
    I assume you want the state of the workbook when opening just to show the menu page - there is code in the OpenWorkbook Event to just show the Menu sheet and hide the others.

    Thank you so much!! this fixed my problem! I do have a qustion, I would like to change the shape of I comand buttom and use a house shape instead since they are going back hme, I am trying to copy and paste the code insde the shape but is not working, I know xcontrols are different from normal shapes but this shoud be an easy macro right? is ther way way to add this code to a shape and make it work? Thank you so much again!

  • Welcome to the Forum


    Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

  • royUK

    Closed the thread.