Thread
:
Macro Trigger
View Single Post
#
11
Posted to microsoft.public.excel.programming
Mike H
external usenet poster
Posts: 11,501
Macro Trigger
Don,
Your right, I read A19 as A1 and included that. Hope the OP spots it.
Mike
"Don Guillett" wrote:
Didn't see anything about row 1 in the post. Also try the "dreaded space
bar" in f18. Also, what do you want to do for any other entry besides yes no
or blank??
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Stringhaussen" wrote in message
...
Thanks Mike,
Worked an absolute treat.
Regards,
Stringhaussen
"Mike H" wrote:
Hi,
You could re-write your code a bit more effeciently like this
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address < "$F$18" Then Exit Sub
If UCase(Target.Value) = "NO" Or UCase(Target.Value) = "" Then
Rows(1).EntireRow.Hidden = True
Rows("19:24").EntireRow.Hidden = True
ElseIf UCase(Target.Value) = "YES" Then
Rows(1).EntireRow.Hidden = False
Rows("19:24").EntireRow.Hidden = False
End If
End Sub
Mike
"Stringhaussen" wrote:
Hi,
I've created a spreadsheet where I want rows to hide/reveal depending
on the
contents of a Cell. The code I used below works however every time I
complete
another cell on the spreadsheet the macro is triggered.
Is there a way to only run the one time when you complete cell F18 and
then
have it disabled until you change that cell value again?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True
If Range("F18").Text = "No" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
Exit Sub
ElseIf Range("F18").Text = "Yes" Then
Rows("18:25").Select
Range("A19").Activate
Selection.EntireRow.Hidden = False
Exit Sub
ElseIf Range("F18").Text = "" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
Exit Sub
End If
End Sub
Thanks,
Stringhaussen
Reply With Quote
Mike H
View Public Profile
Find all posts by Mike H