| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro when a vlooup formula shows a new value
Hello,
I have cell A3 with vlookup formula. Every time a new value is shown in A3 I would like to start /call a macro automatically in that same worksheet. Could you please assist with the syntax Thank you ! Arno |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Start a macro when a vlooup formula shows a new value
Take a look at Worksheet Change events and think about having it fire when
you change the value that is used for the VLOOKUP. You can read more about events here. http://www.mvps.org/dmcritchie/excel/event.htm -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Arno" wrote: Hello, I have cell A3 with vlookup formula. Every time a new value is shown in A3 I would like to start /call a macro automatically in that same worksheet. Could you please assist with the syntax Thank you ! Arno |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Start a macro when a vlooup formula shows a new value
1. enter the following in a standard module:
Public oldval As Variant Sub settup() oldval = Range("A3").Value End Sub 2. run settup 3. enter the following in the worksheet code area: Private Sub Worksheet_Calculate() Dim r As Range Set r = Range("A3") If r.Value = oldvalue Then Exit Sub Call bigmac oldvalue = r.Value End Sub Naturally modify this to use your sub, not bigmac. -- Gary''s Student - gsnu200814 "Arno" wrote: Hello, I have cell A3 with vlookup formula. Every time a new value is shown in A3 I would like to start /call a macro automatically in that same worksheet. Could you please assist with the syntax Thank you ! Arno |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Start a macro when a vlooup formula shows a new value
Here is a way
Code:
'===========================================================
'- CHECK FOR CHANGE IN A VLOOKUP VALUE
'- WE CANNOT CHECK FOR THE CHANGE DIRECTLY (eg.TargetValue)
'- BECAUSE IT DOES NOT FIRE A CHANGE EVENT ITSELF
'===========================================================
'- code runs whenever a change is made in the ws
'- Goes into ws code module. Right click tab. view code
'- Brian Baulsom November 2008
'============================================================
Private Sub Worksheet_Change(ByVal Target As Range)
'- Saves the value in this subroutine
Static A3value As Variant
'--------------------------------------------------------
'- check if the saved value is the same as it was
If A3value < Range("A3").Value Then
A3value = Range("A3").Value
MsgBox ("A3 has changed to " & A3value)
End If
End Sub
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Start a macro when a vlooup formula shows a new value
Hello Gary,
Thank you so much for you help - again your suggestion worked straight away! wondering which reference book you use :) whatever..... have a good day ciao Arno "Gary''s Student" wrote: 1. enter the following in a standard module: Public oldval As Variant Sub settup() oldval = Range("A3").Value End Sub 2. run settup 3. enter the following in the worksheet code area: Private Sub Worksheet_Calculate() Dim r As Range Set r = Range("A3") If r.Value = oldvalue Then Exit Sub Call bigmac oldvalue = r.Value End Sub Naturally modify this to use your sub, not bigmac. -- Gary''s Student - gsnu200814 "Arno" wrote: Hello, I have cell A3 with vlookup formula. Every time a new value is shown in A3 I would like to start /call a macro automatically in that same worksheet. Could you please assist with the syntax Thank you ! Arno |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Start a macro when a vlooup formula shows a new value
Thank you very much Brian,
I will try this too! " wrote: Here is a way Code:
'===========================================================
'- CHECK FOR CHANGE IN A VLOOKUP VALUE
'- WE CANNOT CHECK FOR THE CHANGE DIRECTLY (eg.TargetValue)
'- BECAUSE IT DOES NOT FIRE A CHANGE EVENT ITSELF
'===========================================================
'- code runs whenever a change is made in the ws
'- Goes into ws code module. Right click tab. view code
'- Brian Baulsom November 2008
'============================================================
Private Sub Worksheet_Change(ByVal Target As Range)
'- Saves the value in this subroutine
Static A3value As Variant
'--------------------------------------------------------
'- check if the saved value is the same as it was
If A3value < Range("A3").Value Then
A3value = Range("A3").Value
MsgBox ("A3 has changed to " & A3value)
End If
End Sub
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Start a macro when a vlooup formula shows a new value
Thank you for the link Barb, I will look for more info on this, it is important to be able to find information and resolve this issued indipendently - Thank you again ! Ciao Arno "Barb Reinhardt" wrote: Take a look at Worksheet Change events and think about having it fire when you change the value that is used for the VLOOKUP. You can read more about events here. http://www.mvps.org/dmcritchie/excel/event.htm -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Arno" wrote: Hello, I have cell A3 with vlookup formula. Every time a new value is shown in A3 I would like to start /call a macro automatically in that same worksheet. Could you please assist with the syntax Thank you ! Arno |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlooup formula and if(),isna etc | Excel Worksheet Functions | |||
| Macro to insert this formula automatically on start up | Excel Programming | |||
| cell shows 20. Formula shows 20. why not 540/27 | Excel Worksheet Functions | |||
| Alter data or start macro from a date formula | Excel Programming | |||
| Can I start a macro from a cell by using a formula | Excel Worksheet Functions | |||
Linear Mode
