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

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

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

Macro triggered by a cell value change
 

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

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

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