VBA Code To Hide Sheets & Create Index Of Worksheets To Unhide

ninth1dr · Jun 8th 2010

Hello,

I have a bunch of worksheets for different "spaces" in a house. I then have several sheets that are for a "system" to accomodate all those spaces. I have a form that I can fill out all kinds of information on each space and it will sum it up.

What i'd like to now is create a sort of "finish" button, that when hit will take information from certain cells on each sheet, and automatically place it on the "system" sheet, along with the name of the space on the first column, and then hide the "space" sheets and only show each "system" sheet. I then want to have it so I can click on the name of the space on the "system" sheet and have it unhide the corresponding "space" sheet.

I'm having problems with having it hide each sheet, and know which sheets are system sheets and which are space sheets. I also don't know how to make it so when I populate the system list with the name of the space sheet, that it will allow me to click on that name, unhide the space sheet, and go to it.

Any help or recommendations?

I can attach the sheets if it would make it easier

gollem · Jun 11th 2010

Re: VBA Code To Hide Sheets & Create Index Of Worksheets To Unhide

Hi,

I'm going to give you a start here, some examples.

Here's a code that loops through all your sheets and hides all sheets where the name begins with "space". It also shows the use of a messagebox, displays sheetname.

Code

Dim intSheet As Integer
 
    'Loop through sheets
    For intSheet = 1 To ActiveWorkbook.Sheets.Count
        'Display sheet_name
        MsgBox ActiveWorkbook.Sheets(intSheet).Name
 
        'Hide sheets name beginning with space
        If Left(ActiveWorkbook.Sheets(intSheet).Name, 5) = "space" Then
            ActiveWorkbook.Sheets(intSheet).Visible = False
        End If
    Next

Display More

To select a sheet:

Code

activeworkbook.sheets("Sheet_Name").select

To recognise if your sheet is a system or space I would you use the sheet name like "system_Basement" and "space_Basement" or you have to put a name on the sheet itself in a certain cell so every sheet has the same structure.

Another tip: try to use the macro recorder and take a look at the code. Easy to learn that way.

Hope this gives you a start.

Gollem

VBA Code To Hide Sheets & Create Index Of Worksheets To Unhide

Participate now!

Share