How to HIDE/UNHIDE Specific Sheets Using a TOGGLE Button?

How to HIDE/UNHIDE Specific Sheets Using a TOGGLE

Dear Forum,

I have certain Sheets which I need to Hide them as they use a lot of space in the Tab Bar and not required to be opened once the Data-Entry is done in these specific sheets.

I know how to manually HIDE and UNHIDE the Sheets however, I am looking for some code which can HIDE or UNHIDE these specific SHeets.

The Sheets are different Savings Account Nos and are not Pure Numeric Nos but have some spaces for readability such as

Sample A/c No 1 ===> 1234 567 890 ( As can be seen, its a no except for the inner spaces in the entire no)

Sample A/c No 2 ===> 012345678910
Sample A/c No 3 ===> 123456

I intend to HIDE all the Sheets using a Command Button which will HIDE them on the first click and UNHIDE them on the second click... I have known but some TOGGLE buttons but dont know how to write the code..

SO kindly help me on the same....

Have got this code while googling but dont know how will I use it in my scenario where the Sheet Names are Space Number Entries..

Please Login or Register  to view this content.

Want something which can HIDE all the Account No Sheet Names in one go and UnHide the next Instant the Command Button is clicked..

Please Login or Register  to view this content.

Eagerly waiting for Help...!

Warm regards
e4excel

Originally Posted by e4excel

How to HIDE/UNHIDE Specific Sheets Using a TOGGLE

Dear Forum,

I have certain Sheets which I need to Hide them as they use a lot of space in the Tab Bar and not required to be opened once the Data-Entry is done in these specific sheets.

I know how to manually HIDE and UNHIDE the Sheets however, I am looking for some code which can HIDE or UNHIDE these specific SHeets.

The Sheets are different Savings Account Nos and are not Pure Numeric Nos but have some spaces for readability such as

Sample A/c No 1 ===> 1234 567 890 ( As can be seen, its a no except for the inner spaces in the entire no)

Sample A/c No 2 ===> 012345678910
Sample A/c No 3 ===> 123456

I intend to HIDE all the Sheets using a Command Button which will HIDE them on the first click and UNHIDE them on the second click... I have known but some TOGGLE buttons but dont know how to write the code..

SO kindly help me on the same....

Have got this code while googling but dont know how will I use it in my scenario where the Sheet Names are Space Number Entries..

Please Login or Register  to view this content.

Want something which can HIDE all the Account No Sheet Names in one go and UnHide the next Instant the Command Button is clicked..

Please Login or Register  to view this content.

Eagerly waiting for Help...!

Warm regards
e4excel

Hi Maybe something like this will help?

Here is a test book to show you how it works.

Please Login or Register  to view this content.

Originally Posted by jwright650

Can you use something like this? Just edit the code to hide sheets vs column C

post back if you need more help editing the code.

Thanks for the reply JWright..

I want to HIDE Sheets though thats on similar grounds but I want to HIDE specific SHeets and not Columns but would prefer to have a regular Command Button ActiveEx Control..

So if you can MOdify nothing like it..

One more thing I forgot to mention is I have the Sheets to be Hidden in a List Form in a Defined Name as Account_Nos in a Sheet called Lists..

So, if thats helpful..

Warm Regards
e4excel

Dear Moderator,

If there's any Duplicate thread please delete the same it must have got clicked twice due to slow internet speed.

Kindly Ignore if theres no Duplicate Post...

Warm regards
e4excel

Originally Posted by realniceguy5000

Hi Maybe something like this will help?

Here is a test book to show you how it works.

Please Login or Register  to view this content.

Dear NiceGuyReally..

Thanks for such a quick resposne I was still googling for this!

One more thing I forgot to mention is I have the Sheets to be Hidden in a List Form in a Defined Name as Account_Nos in a Sheet called Lists..

So, if thats helpful..

This is exactly what I want except for the Data Sheet which is the only Sheet which is to be kept Visible I want the Account Nos Sheets to be Hidden..as mentioned above I have a list of the Account nos stored in a Name Define which can be sued and also I need Plain ActiveX Control Command Button of small size with no COlours..

Thanks for the help..

Warm regards
e4excel

Originally Posted by jwright650

see if you can use the revised workbook.....added command button and it hides and unhides sheet2(you can edit the sheet(s) to be hidden by editing the VBA code behind the command button to suit your application).

Dear JWRight,

AS mentioned I need to HIDE only Acct Nos which fall in the mentioned categories in the FIrst Post and all other Sheets should be Visible..

Your code:

Please Login or Register  to view this content.

I dont know how to really put the LIKE concept here to HIDE and UNHIDE the Specific SHeets..

Warm Regards
e4excel

Dear Forum,

Attach a sample file with the sheets named as they are in your file and I can add the code to hide/unhide the sheet that you want.("Acct Nos")

JWright,
I have made some code based on that can you guide me please...as attaching a WorkBook is difficult as i will have to create a Dummy oNe

Some useful Links I found in connection with the query..

http://excel.bigresource.com/hide-sh....html#8rIRpoFS

http://www.excelforum.com/excel-prog...worksheet.html

Leiths Code :

Please Login or Register  to view this content.

Untill now, I didnt even now that there was a Seperate Toggle Button existing..

The code on similar lines needs to be MOdified as well as InterTwined in the Toggle BUttons Click Event

Please Login or Register  to view this content.

I build some code on my own from Leith's Code and other stuff and I think it does preetty well but not have understood completely about the "On Error" ..

Would appreciate if someone could explain about that..

Warm Regards
e4excel.

The best part would have to HIDE/UNHIDE from a Range of CELLS or a Name Define Name..

can someone help me with that please...!

And also when the Sheets are UnHidden they appear from the LEFT Hand SIde can it bee changed to the RIGHT Hand Side..

Warm Regards
e4excel

Originally Posted by e4excel

The best part would have to HIDE/UNHIDE from a Range of CELLS or a Name Define Name..

can someone help me with that please...!

And also when the Sheets are UnHidden they appear from the LEFT Hand SIde can it bee changed to the RIGHT Hand Side..

Warm Regards
e4excel

It would be much faster if you would take the time to make up and attach a dummy workbook that we can look at to see what you are talking about.

With out that everyone is guessing at what you are trying to say.

Thank You, Mike

I think I have got the solution myself after researching but still would appreciate any valuable inputs from the experts..

I havents till figured a way of Hiding UnHIding the Sheets from a Range or Name Defined Range I have a Range of AcctNos created in the SHeet Lists..

Please feel free to add or lessen the code I have made..

I also wanted to create Hyperlinks to the Accts Sheets from the Sheets "PassBook" & "Ledger-View" to the Account Nos SHeets even when they are Hidden..

Can someone please tell me what do you mean by HIdden and VeryHidden SHeets ?

Hope this would help me get help now

Warm regards
e4excel

Excel has built-in function for this...

Excel has a "Custom View" drop down menu that you can drag onto any tool bar. Then set a view i.e. hidden sheets, columns etc... now give that view a name in the "Custom View" drop down list. Repeat until all views have been recorded in "Custom View" now just click on drop down and choose the view you want .

Nimrod,

Thats a good piece of information however, I was looking to have a Programmatic approach as the Account Nos are bound to increase and also any additional Sheets which need to remain in the WorkBoob but Hidden,,

Newvertheless, for confined no of sheets your would be a better option..!

Warm regards
e4excel