VBA troubleshooting - hide/show rows based on cell value

[spena129]

Hi All,

For some reason, my macro is not working. Will you please let me know where I am going wrong? I want to hide a few rows if one cell value is over 15k, if it is <=15000, then should show. See my code below:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("C18").Value > 15000 Then

Rows("20:46").EntireRow.Hidden = True

Else

Rows("20:46").EntireRow.Hidden = False

End If

End Sub


Please help!

 

[Luke M]

Looks to be working correctly. Can you elaborate on what is not working? If nothing happens, check that Application.EnableEvents = True.

Does it matter which cell the user selects? Currently, everything in your macro is hard-coded.

 

[spena129]

Nothing happens. This is part of an overall form that individuals would fill out. I was hoping to have the rows hide/show depending on when the user selects cell C18 and inputs a value. Does that answer your question.

Where would I add the Application.EnableEvents = True code?

 

[Luke M]

First, let's try changing to a Change_event macro like this:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C18")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Rows("20:46").EntireRow.Hidden = Target.Value > 15000
End Sub
If nothing still happens, run this macro:

Sub ResetThings()
Application.EnableEvents = True
End Sub

[/pre]

 

[Mike86]

Just on observation, but I can get Luke's code to work IF I click on C18 AFTER making the change. The change doesn't happen when the number is entered, but does happen after the cell is made the active cell again.

 

[SirJB7]

@Mike86

Hi!

I didn't try Luke M's code, but it seems to respond to Change event so clicking on a cell doesn't trigger this event but the SelectionChange one. So it will only work when updating the cell, not when selecting it, either by mouse or keyboard.

Regards!


EDIT: Tried and works fine. Hides rows only if entered a value higher than 15K in C18.

 

[Mike86]

@SirJB7


It seemed strange and I can't explain it. Just how it worked on my box.


Steve's code worked, but getting the code into the correct Sheet>Worksheet area may be the trick?

 

[SirJB7]

@Mike86

Hi!

Both codes, from Steve and Luke M, work. But are only automatically triggered if placed into the corresponding worksheet VBA code pane. Otherwise, even if it working, they should be called passing a range reference as parameter.

Regards!

 

[spena129]

Thanks everyone. I am an idiot. @Mike86 was right. I didn't have the code in the correct worksheet. Obviously, still a rookie working in VBA.

 

[SirJB7]

Hi, Steve!

Glad you solved it. Thanks for your feedback and don't worry, it'd still be happening years after leaving behind rookie status (and no, I'm not going to say when it happened to me, or even if it did happen, nobody is obliged to testify against himself). Welcome back whenever needed or wanted.

Regards!