Excel macro code

Hi, I am new to write macro's for excel. I am trying to create a workbook where there is a menu sheet, showing a drop down list of each worksheet. I want to be able to chose one from the list for it then to show that work sheet. Otherwise they will be hidden. Is this possible? Thanks for your help

Hello Tara,

Could you please upload a sample file.
Please read the instructions in the gold coloured banner at the top of this page on how to upload a sample file.

Cheerio,
vcoolio.

Hi, thank you for your reply. I have uploaded a sample file. Basically I want to use the "Front Sheet" as a main sheet for a user to see where they would click the drop down list and chose which sheet they need. Then only this sheet plus the front sheet will be visible?

Hello Tara,

See if this helps:-

Please Login or Register  to view this content.

The code above is a Worksheet_Change event and needs to be placed into the sheet module. To implement this code:-

- Right click on the "Front Sheet" tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

On selecting a sheet name from the drop down list, the selected sheet will immediately be selected and all the other sheets (excluding the "Front Sheet") will be hidden. So only two sheets will be visible at any time.

Please note that you have a discrepancy between the sheet name "Screw - Fastener" and the same sheet name in the data validation list. In the sheet name you have a space before and after the "-" but in the data validation list there aren't any spaces. You'll need to adjust one or the other otherwise the code will error on selecting the "Screw - Fastener" name from the drop down.

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio,

Thank you for your help. I tried the code but it didnt work, nothing happened? I will upload the correct file I am trying to use.

Thank you

Tara

Here is the file

Hello Tara,

I'm not sure what's going on at your end but the code works exactly as it should in your actual file
You've correctly changed the cell reference to C6 for the drop down and there aren't any problems on my end.

Try removing the code, save and close the workbook without the code implemented then open it again and place the code back into the worksheet (Front Sheet) module as per the instructions in post #4.

Let us know if that helped.

Cheerio,
vcoolio.

Brilliant, it works now. Not sure what I was doing wrong before but I saved and closed like you suggested and it works Thanks so much for your help!

Hi Vcoolio,

I was just wanting to add in a catergory "ALL" in the drop down list, so when I click this I am able to view all sheets at once. Do you know how I can do this please?

Thanks
Tara

Hello Tara,

Just amend the code as follows:-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

Thank you, and if I want to display a new sheet every time called "References", so each time I have front sheet, "Chosen sheet" and references viewable, how to I add this into the code?

Thanks for your help

Hello Tara,

Do you mean that you now wish to have a maximum of three sheets visible at any one time? So the Front Sheet and the References sheet are always visible?

Cheerio,
vcoolio.

Yes please if that is possible.

Thanks
Tara

Hello Tara,

The code altered as follows should work for you:-

Please Login or Register  to view this content.

The line in red font is the amendment.

I hope that this helps.

Cheerio,
vcoolio.

Perfect! Thanks so much for your help

You're welcome Tara. I'm glad to have been able to assist and thanks for the rep!

Cheerio,
vcoolio.