I still have 5 cells

[CLR]

Hi All..........

I still have 5 cells that I want to automatically copy over to another
WorkBook as soon as any of them change, and return the cursor to the
original WorkBook/Sheet. Two of the cells accept typed in values and three
are on Validation drop-downs.

I've tried to write a Change Event macro to no avail. I've tried to
understand an explanation on a site about how to do it, also to no avail. I
take existing Change-event macros that work and try to change the code to
accomodate a macro that I recorded (which also works by itself), also to no
avail..........

I've been home from work for 3 days this weekend, working on this but just
don't seem to get it. Could someone please spare a few lines of code to
help me through this?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

No doubt this won't work for you but

Private Sub Worksheet_Change(ByVal Target As Range)
if not intersect(target,Range("A1:E1")) then
Range("A1:E1").copy Destination:=Worksheets( _
"sheet2").Cells(rows.count,1).end(xlup)(2)
End if
End Sub

In Excel 97, data validation doesn't trigger a change event unless the
choices are hard coded in the textbox of the validation dialog.

You give few details on your situation, so the above is about the best I can
offer.

Assuming cells are contiguous, data is copied to column 1 of sheet2, changes
are made by editing the cells

 

[dan]

This may not be what you need, but I thought I'd toss it out there...

Is there some reason why you need to do this with a macro? The simplist way
to accomplish what you describe below would be to link the 2 workbooks
together with a formula.

Assume 2 workbooks are named Book1.xls and Book2.xls, and you want a cell to
be instantly updated in Book2.xls with whatever change you make to cell A1
on Book1.xls. In any cell in Book2.xls type the following formula:

=[Book1.xls]Sheet1!$A$1

Whenever you change the contents of cell A1 on Sheet1 of Book1.xls, the
change will occur in the Book2.xls worksheet containing the formula.

Dan C.

 

[CLR]

Thanks Tom,

I appreciate your response..........

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks for the response Dan.........

I appreciate your solution, but indeed, there is a reason for needing the
macro. What I have here is a "Calculation Engine" for our Electronic
people. And they want it to be accessable from several satelite programs.
And if they want to change it, they only want to have to do that in ONE
place. It has several caculations and lots of lookup tables and the
Validation Boxes for the satelites........I've done just as you suggest, and
linked the RESULTS, and the Validation Boxes FROM Book2 TO Book1, as they
are fixed depending upon the inputs and the lists, but cannot link the
inputs FROM Book1 TO Book2, because they may come from as many as five other
"Book1's". It all
works fine now, by hand, and my recorded macros, but I just wanted to jazz
it up and make it provide the accurate data as soon as a single entry was
inputted. Before this, the results did not match the inputs until the user
hits the "Calculate" button..............

Vaya con Dios,
Chuck, CABGx3

This may not be what you need, but I thought I'd toss it out there...

Is there some reason why you need to do this with a macro? The simplist way
to accomplish what you describe below would be to link the 2 workbooks
together with a formula.

Assume 2 workbooks are named Book1.xls and Book2.xls, and you want a cell to
be instantly updated in Book2.xls with whatever change you make to cell A1
on Book1.xls. In any cell in Book2.xls type the following formula:

=[Book1.xls]Sheet1!$A$1

Whenever you change the contents of cell A1 on Sheet1 of Book1.xls, the
change will occur in the Book2.xls worksheet containing the formula.

Dan C.

Hi All..........

I still have 5 cells that I want to automatically copy over to another
WorkBook as soon as any of them change, and return the cursor to the
original WorkBook/Sheet. Two of the cells accept typed in values and three
are on Validation drop-downs.

I've tried to write a Change Event macro to no avail. I've tried to
understand an explanation on a site about how to do it, also to no

avail.

I
take existing Change-event macros that work and try to change the code to
accomodate a macro that I recorded (which also works by itself), also to no
avail..........

I've been home from work for 3 days this weekend, working on this but just
don't seem to get it. Could someone please spare a few lines of code to
help me through this?

TIA
Vaya con Dios,
Chuck, CABGx3