Macro runs when address entered?

One way to do this is to place this code in your worksheet module, and replace "YourMacro" with the name of the macro you say you already have.

One thing, you should consider validating your cell (A1 in this example) for a certain minimal length, since as you say you will enter addresses in that cell to trigger the macro, so you wouldn't want a stray entry such as one key stroke (a single letter or number value for instance) be mistakenly entered into A1 and then triggering "YourMacro" unnecessarily.

Right click on the sheet tab, left click on View Code, and paste this in.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("A1").Address Then
Run "YourMacro"
End If
End Sub

HTH

Tom Urtis

I'm making a mess of this...again!

I tried to figure out how to follow your example to accomodate a progressive address cell, but it aint working.
My main macro allows me to copy and paste a set layout of data and equations. Cell A14 contains "Address" until changed with an actual address, then after CTRL N is pressed for a new entry, A24 contains "Address" and this keeps repeating until no more entries are required.

My attempt below may give you some idea of what I am trying to do.

Thanks for the help.
George

' Listing_Update Macro
' Macro recorded 30/01/2002 by gj
'
'
Private Sub Worksheet_Calculate()

If Not Range("A65536").End(xlUp).Offset(-5, 0).Row("Address") Then
Run ("listing_Update")
End Sub

Sub Listing_Update()
PropertyName = Range("A65536").End(xlUp).Offset(-5, 0).Row
Range("A" & PropertyName).Select
Selection.Copy


Sheets("listing").Select
LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

If LastRow < 3 Then
NextRow = 3
Else
NextRow = Range("A65536").End(xlUp).Offset(2, 0).Row
Range("A" & NextRow).Select
End If

ActiveWindow.LargeScroll ToRight:=-1
ActiveSheet.Paste
End Sub

Re: I'm making a mess of this...again!

Maybe it's me, sorry, but I'm a bit confused on your worksheet event. Can you please provide a few details, mostly for my curiosity so I can know I'm on the right track:

(1) You are using the Worksheet_Calculate event.
a. What is the formula in that cell?
b. What is the range of expected results that, when achieved (or not achieved) are supposed to trigger your Listing_Update() macro?

(2) You say A14 contains "Address" until changed with a real address.
a. Do you mean a cell address, such as "B4", or some other kind of address?
b. What is the relevance of A14, if it looks like in the sheet event that you are monitoring the cell for a calculation change 5 rows below that?

I got up too early today, so sorry if I'm missing something obvious.

Tom Urtis

I tried to figure out how to follow your example to accomodate a progressive address cell, but it aint working. Sheets("listing").Select LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

Re: I'm making a mess of this...again!

This is the second macro I have written, with no reference books or knowledge sources to go on. I used the Worksheet_Calculate event as this looked to be along the lines taken by other users trying to do something similar in the archives. I am probably completely wrong in using this. (2) You say A14 contains "Address" until changed with a real address.

Cell A14 caontains the text "Address". This is so that the user will enter an address in this cell. Once this has changed from "Address" to the new text, I want the listing sheet to list all the addresses entered; starting at cell A3 and then every 2nd cell after that.

5 cells below the address in A19, then A29 etc I have the text "Total". This is why I am counting back 5 cells. I got up too early today, so sorry if I'm missing something obvious.

My Explanations are almost as bad as my programming. The original Spreadsheet I had, I listed 200 properties. I took a screenshot of this way back and hopefully you can see it from the URL below. It is the last of the 4 images.
(The program on this site is still being developed, but I have already suggested it to Bill Jelen)

The main Macro I have (which surprisingly works) copies cells A4 to P13 and pastes them in the next line. This allows me to bring up a new entry if required, so I can find Range("A65536").End(xlUp).Offset(-5, 0).Row which will be the last address entered.

If you need any more clarification, please just ask. Thanks for the input.

George

Sheets("listing").Select LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

Link in here

OOPS

Here is the link
http://www.quicpic.co.uk/demo/quicpic.php?cat=3&event=Screenshot

George

Re: I'm making a mess of this...again!

Based on your answers then, my original post might do the trick, with a few modifications to suit your situation.

Right click on the sheet tab, left click on View Code, and paste this in.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("A14").Address Then
Run "Listing_Update"
End If
End Sub

See if this gets you any closer to what you want to accomplish. You might consider keeping the word "Address" out of A14, and maybe in A13 enter some text, such as "Enter your address in A14".

Tom Urtis

Sheets("listing").Select LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

Success!

I was trying to put the private sub part at the beginning of the macro DOH!! Didn't know you could right click on the sheet tab put the code in there.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("A65536").End(xlUp).Offset(-5, 0).Address Then
Run "Listing_Update"
End If
End Sub

This works fine. I just need to hide it going back and forward between the sheets, but I know how to do this part.

Thanks for the advice, time and patience
George Sheets("listing").Select LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row