Hide / Unhide rows based on cell content

[KaFi]

Hi there

I have a worksheet that has a number of hidden rows. The hidden rows contain content that I want to appear if particular content is placed in certain cells.

I have tried placing the following into ThisWorkbook:

Please Login or Register  to view this content.

Followed by this in Module 1

Please Login or Register  to view this content.

When I pasted this into Module 1 I deleted the existing content as I assumed it related to text boxes that I had in the sheet earlier but which have all been deleted:

Please Login or Register  to view this content.

Thanks for your time!

[royUK]

You can't have worksheet events in the workbook module. Always use the drop downs in the VB Editor to add an event.

Try attaching an example workbook with your existing code

[KaFi]

Hi Roy, thanks for your reply. I've attached the file as suggested.

I'm a new user, so can you pardon my ignorance and tell me where I should paste the code?


Thanks!
Kafi

[royUK]

You need to research basic inputting of code. You can't just copy & paste event code anywhere. See this

Also your code to hide the rows has an unnecessary End Sub,doesn't specify a worksheet to act on and does not need to select the range. You have unneeded End Ifs and you don't put speech marks around Procedure names to call them.

Also,get rid of the Merged cells, they will make coding more difficult. I've made a few changes

[KaFi]

Thanks Roy, pointing me to that resource is much appreciated.

In the amended workbook I get the following error when I select any cell:

Run-time error '9':
Subscript out of range

Clicking debug highlights the following line of code:

Please Login or Register  to view this content.

Clicking End instead of Debug returns me to the worksheet, but the rows do not reveal themselves when I select 'contractor' though.

Any further help gratefully received.

Regards
Kafi

[Ryan Murtagh]

KaFi,

With this error:

Please Login or Register  to view this content.

The reason you get a Subscript Out Of Range error here is because "SR Add New User" should be "SR1 Add New User". You can actually get rid of 'Sheets("SR1 Add New User") anyway. The following would be sufficient, because you have already specified the Worksheet object in your With statement.

Please Login or Register  to view this content.

It doesn't matter though KaFi, because the SelectionChange event won't fire until the user exits cell B29, not when they select "Contractor" from the dropdown...which presumably is what you are trying to accomplish.

The list of errors in your project is really pretty long KaFi. I completely dig the fact that you were super ambitious here, but I think you're in a little over your head. If you were unable to troubleshoot a runtime error (however obtuse the description may have been) that was caused by an incorrectly spelled worksheet name, will you be able to effectively troubleshoot and resolve the sundry errors that your users will create once they get their hands on it?

I hate to discourage your ambition, but I have to respectfully recommend that you consider a completely non-VBA approach to the project. For example, you could use the Group and Outline function to hide those rows and prompt the user to unhide the rows using an IF statement that will display a message in a cell, once the user selects "Contractor" from the dropdown list. Additionally, you can instruct the user (with a textbox or cell comment) to use Excel's native email functionality to send the form, once completed. See the attached workbook example.

[KaFi]

Thanks Ryan and RoyUK, all points noted (particularly the one about checking the basics before assuming the problem is something big and ugly).

I'm still keen to keep working on this though, as all the other functions are sorted now and this hide/unhide is the last issue.

Any additional help from anyone is gratefully received.

Okay, so this is what I'm up to (all credit to RoyUK who has got me most of the way here so far):

When selecting "Contractor" in B31 the required rows unhide. Unfortunately once they have unhidden, I can't hide them again to see what happens when I play with the code. I clear the content of B31, select the rows and hide them, but as soon as I click anywhere in the worksheet the rows unhide again.

Apart from the obvious issue, it's also a problem because I'm trying to work out how to do the following:
If "Fixed Term Employee" is selected I want some different rows to unhide, and if "Permanent Employee" or nothing is selected, I want all the rows to remain hidden.

I also want to apply the same rules in a couple of other places in the spreadsheet.

This is what I have in the worksheet:

Please Login or Register  to view this content.

And this is what I have in Module 1:

Please Login or Register  to view this content.

I realise that I need a macro for each different set of rows that I need to unhide or hide, but I'm having trouble working out how to reference each one in the worksheet. I had another macro called UnhideFixedTerm, for example, that I wanted to run if the content of B31 was "Fixed Term Employee". I tried doing the following (which of course didn't work, but by then I also couldn't rehide rows to try other things and I was getting sick of going back to the unsaved sheet all the time):

Please Login or Register  to view this content.

So I guess I have two questions - how do I rehide rows that have been unhidden using the first bunch of code above, and how do I structure a series of requests to look at different cells and refer to different macros based on their content?

[Ryan Murtagh]

Hi KaFi,

Well...I'm not one to turn down a good challenge either. Can you post your updated workbook? I downloaded the attachment from Roy's post, but it appears to be the same as your original attachment.

Also, when you say that you can't rehide the rows, you mean programmatically, right? You can still manually hide the rows, no?

[KaFi]

Hi Ryan, thanks for coming back to this one! I have attached the current file including my current hide/unhide issue - I highlight rows 30 to 49, then right-click and select 'hide'. They dutifully disappear. Then I click anywhere in the sheet, and they magically unhide, regardless of the current content of B29.

Regards
Kafi