Hiding/Showing rows with a button

[ramatthews]

Hi All,

Can Someone please help me? I have a button with the following macro to hide/show rows:

Sub HideUnhide()
Rows("165:183").Hidden = Not Rows("165:183").Hidden
End Sub

This works perfectly for hiding/showing these rows by clicking on the button. The problem I have is that I would like to Hide rows 4:157 when the button shows rows 165:183, and show rows 4:157 when button hides rows 165:183.

Is this possible, and if so, can anyone please assist me?

Also, how can I create a button which shows a specific sheet in the workbook, but the user must not be able to unhide the sheet. It can only be done by making use of the button?

I will appreciate any help if possible. Thank you so much in advance.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Arkadi]

hi rammatthews,

In future please wrap code in code tags, by highlighting the code part and hittint the "#" symbol in the menu.

to flip your other rows:

Please Login or Register  to view this content.

[Arkadi]

if you set the property of the sheet to very hidden (via code) then the only way to unhide it is via macro, so they can't unhide manually.

Please Login or Register  to view this content.

[ramatthews]

Hi John,

Thank you VERY much.This works perfectly. This was a great help!

I am not sure if you saw the last bit of my previous post regarding the button which shows a specific sheet in the workbook. If you can assist in this regard I will be very grateful.

[ramatthews]

Dear Arkadi,

Thank you for your post and the advise regarding my posts. Will work on it in future.

How/Where should I post this code? I am a bit confused. Apologies, but I am very new to VBA.

[JOHN H. DAVIS]

Hi John,

Thank you VERY much.This works perfectly. This was a great help!

I am not sure if you saw the last bit of my previous post regarding the button which shows a specific sheet in the workbook. If you can assist in this regard I will be very grateful.

You're welcome. Glad to help out and thanks for the feedback. I think Arkadi has addressed the Sheet question. Sorry, I missed it and jumped the gun.

[Arkadi]

I just gave you the code needed to do it, I am unsure how you want to implement it.... truth is the first line you can put in a sub that you just run manually, it will need to be run whenever you want to make the sheet hidden again.

as for the unhide, make a button wherever you need it, and then assign it a macro with just one line of code:

Please Login or Register  to view this content.

Replace "Sheet1" with the name of the actual sheet in question. In another macro that you run manually I guess (or another button) you can run the code to make it hidden in such a way that only code can unhide it, again, replacing the sheet name as needed:

Please Login or Register  to view this content.

[ramatthews]

Thanks Arkadi. I am trying to get this to work. Your help is really appreciated. is there a way when the sheet is "unhidden", that the user is automatically taken to that specific sheet?

[Arkadi]

yes there is... using the "sheet1" example again...

when they press the button, which links to a macro, make that macro:

Please Login or Register  to view this content.

If you have more questions, just ask. Once we get your issue resolved, please remember to mark the thread as solved

[ramatthews]

HI ARKADI,

That worked perfectly, thanks so much. I do however have a few questions:

1. I still cannot hide my sheets permanently from the user. I do not know how to apply the code you provided i.e.

Please Login or Register  to view this content.

2. When the button is clicked to show a particular sheet, I do not wish this sheet to stay visible. As soon as they leave the sheet, they will have to use the button again to activate the particular sheet. Is this possible?

I really appreciate all your assistance.

Regards

[Arkadi]

But where is the button if the sheet is gone? or do you mean when they deactivate the ONE SPECIAL sheet, it should re-hide?

If so, then in addition to what we have done already, go to VBA editor, double-click on the sheet you want hidden in the project browser (top-left area of VBA editor), and paste in this code:

Please Login or Register  to view this content.

[ramatthews]

Dear Arkadi,

This works perfectly! Thank you sooo much for your assistance. I hope that you don't mind if I press on your button again in future, as I really appreciated your assistance.

[Arkadi]

Any time, always happy to help
Please take a moment to mark thread as solved if we dealt with the issue at hand?

[ramatthews]

Hi John, Hope you are well. I have another question to ask. Would appreciate it if you can assist me. It applies to the following code as above:

Please Login or Register  to view this content.

I have another variable to add. Let us assume Cell "I2" can be two different variables. The two variables is "large" and "QSE". When cell I2 is "large", then the code should stay the same as above when the button is clicked, except that rows 180:194 should also be hidden. The range of 160:178 becomes 160:194, but there is two different sections. When cell I2 is "QSE" then it needs to swap around when the button is clicked i.e. 180:194 is shown, and 160:178 is hidden. Is this possible at all? I do hope my explanation is making sense.

I would appreciate all you assistance in this regard. Please help if you can?

Regards

[JOHN H. DAVIS]

You can place an elseif statement around the case statement that covers "QSE" and "large". Then you can work out what you want hidden and when.

Please Login or Register  to view this content.

[ramatthews]

Hi John,

The same principle applies to the following code which I use for unhiding a sheet:

Please Login or Register  to view this content.

When the variable is at "large" I would like rows 2:22 on the hidden spreadsheet to be visible and rows 38:48 to be hidden, and then again vice versa for "QSE". Can this be done, and if so, please can you point me in the right direction?

Please understand that I really appreciate all you help and assistance so much.

Regards

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[ramatthews]

Hi John,

Thank you very much, but I have hit a obstacle again. My code is looking as follows:

Please Login or Register  to view this content.

I have also attached the sheet for your information should you wish to have a look at it. When "I2" is on "Qualifying Small Enterprise", then the button does not work, but on "Large" it works perfectly. What am I doing wrong? Please help?

[ramatthews]

How to I combine this with the existing code of:

Please Login or Register  to view this content.