Put a 'Click' event in a Macro

[tvac]

Can a ‘Click’ event be included within code to trigger a macro on another worksheet (WS)?
I have a workbook which monitors share prices, and through a macro changes the value in columns H & I on worksheet (WS) 1 and cols E & F on WS2 should the value in cols G & D respectively be higher or lower than the values already there. (The macros on both sheets are similar but use different columns).
On WS1 the macro is activated by ‘clicking’ on a Command Button, (the GREEN one on the attached file) which does other things before changing any values. No problem here.
The values are imported from the web into WS3 and 4 (Import Share Price and Import Tracking) which are linked to cols G and D on WS1 and 2 respectively.
On WS2 I have to physically go to that sheet and ‘Click’ on any cell to activate the macro, as the macro on both sheets starts as:

Please Login or Register  to view this content.

What I want to do is reduce the two actions into one so the macro on WS2 automatically runs after the actions on WS1 once the Command Button is pressed.
I assume that the two macros cannot be combined, as WS2 would not be the active worksheet, so can WS2 be selected and the macro run with a ‘Click’ event on that sheet, or is there another way around this.
I attach a sanitized file with all the code on both WS’s.
To test any solution, enter a value greater and lesser than those shown in cells G51 and G52 on WS4 (Import Tracking). These values will then be shown in cells D53 and 54 on WS2, and should increase and decrease the values in cells E53 and F54 after the macro is run.

[Andy Pope]

If you move the code in the selection event into a routine in a standard code module then you can call it from the selection event and anywhere else you want to.

[tvac]

If you move the code in the selection event into a routine in a standard code module then you can call it from the selection event and anywhere else you want to.

Hi Andy
Thanks for taking the time to reply, but you have just gone straight over my head. I inherited this workbook, and although I know my way around Excel (more or less), VBA is a mystery! I've only worked out what I know from logically looking at the code.
Any further assistance would be appreciated.

[Leith Ross]

Hello tvac,

Andy is offline right now. I have looked at the workbook and agree with Andy's assessment. The attached workbook now updates the "Tracking" sheet when the "To see daily changes" button is clicked. The tracking sheet no longer updates when you select a different cell.

I moved the code from the Worksheet_SelectionChange event into a separate VBA module named "Update_High_Low_Tracking_Values". Now this can be called from any event, code, or module in the VBA project to update the "Tracking" worksheet.

[Andy Pope]

Thanks Leith

[tvac]

Hello tvac,

Andy is offline right now. I have looked at the workbook and agree with Andy's assessment. The attached workbook now updates the "Tracking" sheet when the "To see daily changes" button is clicked. The tracking sheet no longer updates when you select a different cell.

I moved the code from the Worksheet_SelectionChange event into a separate VBA module named "Update_High_Low_Tracking_Values". Now this can be called from any event, code, or module in the VBA project to update the "Tracking" worksheet.

Hello Leith and Andy
Thanks for that. I think I understand the solution, however, it still does not work. On pressing the Command Button I get a "Sub or Function Not Defined" error message.
I note that the module is called:
Update_High_Low_Tracking_Values
but the line of code is:
[CODECall UpdateHighLowValues]Please Login or Register to view this content.[/CODE]
I have tried inserting 'Tracking' but still no joy.
Regards - John

[Andy Pope]

Just a typo.

Please Login or Register  to view this content.

[Jack in the UK]

Look in the prodecure you want and call it ie

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[tvac]

Hello Andy
I changed the code as you suggest, and now no error messages. However, only the macros on the 'Stop Loss' sheet (WS1) run, including updating of higher/lower prices. It just stops there, and the 'Call' to activate the 'Tracking' (WS2) does not happen.
Jack. Thanks for your contribution, but I think we are nearly there with Andy's solution.
Andy, sorry to be such a pain, but could you look again please and maybe amend the file posted by Leith.

[Andy Pope]

Because the routine has been moved from the worksheet object we need to include a reference to the worksheet.

Please Login or Register  to view this content.

WS2 needs the event code changed slightly

Please Login or Register  to view this content.

The button on WS1 also need to call the routine

Please Login or Register  to view this content.

Even with those changes in place I'm not sure what will happen as the WS2 has nothing in A1 so the While/Wend loop will not execute.

[tvac]

[QUOTE][Even with those changes in place I'm not sure what will happen as the WS2 has nothing in A1 so the While/Wend loop will not execute. /QUOTE]
Hi Andy
Me again! Made the changes but everthing exactly as before, everything runs ok up to the point of calling WS2 - nothing. I even un-merged cells in Row 1 and entered a value in A1, still no good. What do we do next?

[Andy Pope]

Do you have data in the appropriate cells of WS2?
Can you post the workbook you currently have that does not work.

[tvac]

Hi Andy
All sorted. Data re-entered into Col A on WS2, I forgot that you need something there, but wiped it all out when I sanitized the workbook.
It works perfectly and I have made all changes into the large master workbook and that works fine also.
Thanks to you and Leith, you must be very patient people. If you are ever in the Preston area - I owe you a pint!
Kind regards - John

[Andy Pope]

Thanks for the update. Glad you are sorted.