Hide/Show a row based on data entry in another row

[Guest]

I have a data entry cell at B4 where you enter a currency value. If B4 is
blank I want row 19 to be hidden and if B4 is not blank then show row 19.
I've tried a heap of different things but none have worked so far.

This works if you only want to hide them and your cell has a value but I
can't think how to do it if it's a blank!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Hidden = True
Else
Exit Sub
End If
End Sub

 

[Norman Jones]

Hi Ivory Kitten,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Me.Range("B4")

If Not Intersect(rng, Target) Is Nothing Then
Rows(19).EntireRow.Hidden = IsEmpty(rng.Value)
End If
End Sub
'<<=============

 

[Guest]

I get: Compile Error: Ambiguous name detected: Worksheet_Change!

But I think that's because I have another function which changes some text
fields to uppercase. How do I use both?

Here's my other one:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Range("b1,b2,b5"), Target) Is Nothing Then
Target.Formula = StrConv(Target.Formula, vbUpperCase)
End If
End Sub

 

[Norman Jones]

Hi Ivory Kitten,

I get: Compile Error: Ambiguous name detected: Worksheet_Change!

But I think that's because I have another function which changes some text
fields to uppercase. How do I use both?

It is only possible to have one Worksheet_Change procedure.

Try replacing your present two procedures with;

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Me.Range("B4")

If Not Intersect(rng, Target) Is Nothing Then
Rows(19).EntireRow.Hidden = IsEmpty(rng.Value)
End If

If Not Application.Intersect(Range("b1,b2,b5"), _
Target) Is Nothing Then
Target.Formula = StrConv(Target.Formula, vbUpperCase)
End If

End Sub
'<<=============

 

[Guest]

I must be doing something wrong! It works once or twice then nothing
happens! I'm copying it exactly.

 

[Norman Jones]

Hi Ivory Kitten,

I must be doing something wrong! It works once or twice then
nothing happens! I'm copying it exactly.

What does "nothing happens" mean? Lower case entries in B1, B2 or B5 are not
converted to uppercase? Row 19 is not hidden or unhidden in reponse to
changes in B4?

There is nothing intrinsic to the Worksheet_Change code that would explain
successful operation followed by failure.

Perhaps, howevere, you have other code which may have turned off events. In
the Immediate window, try typing:

Application.EnableEvents = True

and hit the Enter key. Now retry the Worksheet_Change code.

 

[Guest]

It's odd, I have no other codes, its working fine now. The case bit was
working and the hide/unhide row part was not. But it is now!?

how do you change the hide/unhide so that it hides when empty or a certain
value?

 

[Norman Jones]

Hi Ivory Kitten,

It's odd, I have no other codes, its working fine now. The case bit was
working and the hide/unhide row part was not. But it is now!?

I am glad that you have got it working.

how do you change the hide/unhide so that it hides when empty or
a certain value?
Change:

to

Rows(19).EntireRow.Hidden = Not IsEmpty(rng.Value)