Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro Trigger
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Macro Trigger
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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 523
Default Macro Trigger
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.address = $F$18 then

'Your code

end if

end sub

"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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Macro Trigger
Hi Sam,

Thanks for your help. I'm still not getting this to work, if I type in any
other cell after I hit enter it automatically selects row 19:24 as in the
coding.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True
If Target.Address = F18 Then
ElseIf Range("F18").Text = "No" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
ElseIf Range("F18").Text = "Yes" Then
Rows("18:25").Select
Range("A19").Activate
Selection.EntireRow.Hidden = False
ElseIf Range("F18").Text = "" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
End If
End Sub

Regards,

Stringhaussen

"Sam Wilson" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.address = $F$18 then

'Your code

end if

end sub

"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 QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Macro Trigger
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 QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
GB GB is offline
external usenet poster
  
Posts: 230
Default Macro Trigger
Take a look at Mike H's response, whichever "method" you adopt, the
comparison to target.address should be as text.

target.address = "F18" or
target.address = "$F$18"


"Stringhaussen" wrote:

Hi Sam,

Thanks for your help. I'm still not getting this to work, if I type in any
other cell after I hit enter it automatically selects row 19:24 as in the
coding.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True
If Target.Address = F18 Then
ElseIf Range("F18").Text = "No" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
ElseIf Range("F18").Text = "Yes" Then
Rows("18:25").Select
Range("A19").Activate
Selection.EntireRow.Hidden = False
ElseIf Range("F18").Text = "" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
End If
End Sub

Regards,

Stringhaussen

"Sam Wilson" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.address = $F$18 then

'Your code

end if

end sub

"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 QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Macro Trigger
oops,

forgot a line

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
Application.ScreenUpdating = True
End Sub

Mike

"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 QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default Macro Trigger
You didn't follow Sam's instruction. To do it the way he showed you MUST use
$ and caps. You can also use
if target.address<range("f18").address or INTERSECT
try this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("F18").Address Then Exit Sub
Rows.Hidden = False
If LCase(Target) = "no" Or _
Len(Application.Trim(Target)) < 1 Then _
Rows("19:24").Hidden = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stringhaussen" wrote in message
...
Hi Sam,

Thanks for your help. I'm still not getting this to work, if I type in any
other cell after I hit enter it automatically selects row 19:24 as in the
coding.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True
If Target.Address = F18 Then
ElseIf Range("F18").Text = "No" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
ElseIf Range("F18").Text = "Yes" Then
Rows("18:25").Select
Range("A19").Activate
Selection.EntireRow.Hidden = False
ElseIf Range("F18").Text = "" Then
Rows("19:24").Select
Range("A19").Activate
Selection.EntireRow.Hidden = True
End If
End Sub

Regards,

Stringhaussen

"Sam Wilson" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.address = $F$18 then

'Your code

end if

end sub

"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 QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default Macro Trigger
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 QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Macro Trigger
Glad I could help

"Stringhaussen" wrote:

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 QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default Macro Trigger
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 QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default 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 QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Trigger a Macro Patrick Simonds Excel Programming 1 December 16th 06 07:28 AM
Trigger macro on value only [email protected] Excel Programming 4 September 6th 06 09:28 PM
macro trigger Leslieac Excel Discussion (Misc queries) 3 February 2nd 06 09:08 PM
Another way to trigger a macro? Leon[_5_] Excel Programming 1 December 22nd 05 06:03 AM
Macro trigger? excelguru Excel Programming 0 February 29th 04 06:10 AM


All times are GMT +1. The time now is 06:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement