Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by a cell value change
Hi
I like to have the VBA codes(Not Excel Formula) for the following; I select Range("a1") and enter "OK" I select Range("a5") and enter 3 Now if I change Range("a5") then Range("a1") should read "Error". Thanks Varnendra M |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by a cell value change
Maybe
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [A5] < 3 And [A1] = "OK" Then [A1] = "Error" End Sub Goes into the code page of the worksheet you want to use. Joerg "Varne" wrote in message ... Hi I like to have the VBA codes(Not Excel Formula) for the following; I select Range("a1") and enter "OK" I select Range("a5") and enter 3 Now if I change Range("a5") then Range("a1") should read "Error". Thanks Varnendra M |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by a cell value change
Sorry, too quick. Better use
Private Sub Worksheet_Change(ByVal Target As Range) If [A5] < 3 And [A1] = "OK" Then [A1] = "Error" End Sub Joerg "Joerg" wrote in message ... Maybe Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [A5] < 3 And [A1] = "OK" Then [A1] = "Error" End Sub Goes into the code page of the worksheet you want to use. Joerg "Varne" wrote in message ... Hi I like to have the VBA codes(Not Excel Formula) for the following; I select Range("a1") and enter "OK" I select Range("a5") and enter 3 Now if I change Range("a5") then Range("a1") should read "Error". Thanks Varnendra M |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by a cell value change
Hi Varne,
check your mail. I've sent you a demo. The code should not go into a module , but rather into the the code section of a sheet (go to the VBA Editor, and in the left Project window doubleclick the worksheet where you want the use the code. An empty code window will open. Into this window you can paste the small macro). The reason why you can't use a module: The maco is triggered by a 'Change' event. Events are defined for objects like worksheets or the whole workbook and are particular to these objects (e.g. the workbook has a 'BeforeSave' event, triggered by calling the Save dialogbox, but the worksheet has not - since you can't save sheets, you can save only whole workbooks). A module is no object and has no event, so code sitting in a module could not react to events happening in a sheet or workbook. Joerg "Varne" wrote in message ... Joerg Hi Thanks for interest. I copied the codes in the VBA project module and checked it if it works. It doe not. I am not sure what I did was what you meant. Do I have to write the codes in a different place? Not VB Editor? Microsoft Script Editor or something? If you not mind please do a demo on Excel and email it to me on Many Thanks Varne "Joerg" wrote: Sorry, too quick. Better use Private Sub Worksheet_Change(ByVal Target As Range) If [A5] < 3 And [A1] = "OK" Then [A1] = "Error" End Sub Joerg "Joerg" wrote in message ... Maybe Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [A5] < 3 And [A1] = "OK" Then [A1] = "Error" End Sub Goes into the code page of the worksheet you want to use. Joerg "Varne" wrote in message ... Hi I like to have the VBA codes(Not Excel Formula) for the following; I select Range("a1") and enter "OK" I select Range("a5") and enter 3 Now if I change Range("a5") then Range("a1") should read "Error". Thanks Varnendra M |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by a cell value change
Joerg
Hi The following codes you sent do what I want. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next [A1] = [A5] / 3 End Sub Thank you very much. Regards M Varnendra "Joerg" wrote: Hi Varne, check your mail. I've sent you a demo. The code should not go into a module , but rather into the the code section of a sheet (go to the VBA Editor, and in the left Project window doubleclick the worksheet where you want the use the code. An empty code window will open. Into this window you can paste the small macro). The reason why you can't use a module: The maco is triggered by a 'Change' event. Events are defined for objects like worksheets or the whole workbook and are particular to these objects (e.g. the workbook has a 'BeforeSave' event, triggered by calling the Save dialogbox, but the worksheet has not - since you can't save sheets, you can save only whole workbooks). A module is no object and has no event, so code sitting in a module could not react to events happening in a sheet or workbook. Joerg "Varne" wrote in message ... Joerg Hi Thanks for interest. I copied the codes in the VBA project module and checked it if it works. It doe not. I am not sure what I did was what you meant. Do I have to write the codes in a different place? Not VB Editor? Microsoft Script Editor or something? If you not mind please do a demo on Excel and email it to me on Many Thanks Varne "Joerg" wrote: Sorry, too quick. Better use Private Sub Worksheet_Change(ByVal Target As Range) If [A5] < 3 And [A1] = "OK" Then [A1] = "Error" End Sub Joerg "Joerg" wrote in message ... Maybe Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [A5] < 3 And [A1] = "OK" Then [A1] = "Error" End Sub Goes into the code page of the worksheet you want to use. Joerg "Varne" wrote in message ... Hi I like to have the VBA codes(Not Excel Formula) for the following; I select Range("a1") and enter "OK" I select Range("a5") and enter 3 Now if I change Range("a5") then Range("a1") should read "Error". Thanks Varnendra M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for a one time triggered copy of cell value | Excel Discussion (Misc queries) | |||
event triggered by cell format change? | Excel Programming | |||
Can a macro be triggered when a cell is selected? | Excel Programming | |||
Event triggered by cell data change/entry | Excel Programming | |||
macro triggered by a change to a cell | Excel Programming |