On update, if cell equals, hide/show rows

[Gerdagan]

Hi all.

I need some help here. I have a date entry worksheet, where I want certain entry fields (rows) to appear or dissappear depending on the value of a cell.

The coding I am using is:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("S82") = "Yes" Then
    Rows("85:92").Select
    Selection.EntireRow.Hidden = False
    Range("S85").Select
    End If
    If Range("S82") = "No" Then
    Rows("85:92").Select
    Selection.EntireRow.Hidden = True
    Range("P82").Select
    End If
    If Range("V146") = "Required" Then
    Rows("148:156").Select
    Selection.EntireRow.Hidden = False
    Range("V149").Select
    End If
    If Range("V146") = "Not required" Then
    Rows("148:156").Select
    Selection.EntireRow.Hidden = True
    Range("V146").Select
    End If
End Sub 

I have used this code before, but only when I have one set of options on a worksheet. The problem I am encountering with this is that Excel is updating the entire worksheet based upon the IF values of both cells (S82 and V146). So IF S82 = Yes then the cell that should be selected is S85. However, also IF V146 = Required, the cell that is being selected is V149, though I need to update cell S82. The cell selection jumps.

No I know this same end result I am looking for (hide/unhide rows) could be done using option buttons with an assigned macro, but I cannot hide the rows with the option buttons on them, and ActiveX buttons are not working out for me.

So, is there any way of making the two codes for the two updated cells work independently, where one IF does not consider any other IF's?

I really hope this makes sense - I'm really not up on the VBA lingo so I hope I've put my problem across OK, and everyone knows want I am looking to achieve.

Many thanks,
Ged.

[kcarley]

If I've understood you correctly then this should work just fine for you.

Please Login or Register  to view this content.

[Gerdagan]

Hi Kcarley,

I really appreciate the post, but it didn't work. To make the code run I had to click off the cell, at which point the screen flashed for about 30 seconds, I'm assuming that this was Excel looking for the changes and acting accordingly, but it still doesn't work as I wanted.

The code I am using works, it's just I need each IF to work sperate from the others so a row is being hidden/shown dependent upon the cell that has just been updated only. Does that make sense?

Thanks, G.

[kcarley]

It does! I'm sorry about the change in the name of the subroutine, I didn't notice yours and simply worked the code in the first event that worksheet creates... changing that back should help with the timing of the action.

I would like to fix this quickly for you; do you have a sample of the spreadsheet I could work on? If you look to the bottom of where you type to reply to me and click the [Go Advanced] button you will be able to attach it to your reply. It will make it a lot easier to see what didn't work for you.

In the meantime I will try to see if I can't debug the code a bit as it should have done 'something'.

[kcarley]

Ah, as suspected. There are no errors, so the code is doing 'something'. I think the problem is that I may be missing some of the logic you need here. So reading back at Post #1 I see that you want:

If S82 is changed to "Yes" then UNHide rows 85 thru 92 and put me on cell S85 ready to type.
but if...
If S82 is changed to "No" then Hide rows 85 thru 92 and put me on cell P82 ready to type.

ALSO but ONLY if I change it...

If V146 is changed to "Required" then UNHide rows 148 thru 156 and put me on cell S82 ready to type.
but if...
If V146 is changed to "Not Required" then Hide rows 148 thru 156 and put me on cell ??? (I don't see this choice?) ready to type.


If you change any or all of those statements to what you actually want, I am likely to be able to have a solution. Keep in mind that to change a cell you have to either press [Enter] or click off the cell. The subroutine I use would run multiple times for each change that was made by code and explains the seconds of flickering but you'd still have to commit the change in some fashion.

[kcarley]

Hi!

Are you still looking for this? You may need to change some of the cells to end where you want or even the order of the statements but this should be what you were looking for:

Please Login or Register  to view this content.