Macro triggered by a cell value change

[Guest]

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

 

[Joerg]

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

 

[Joerg]

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

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

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

 

[Guest]

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
(e-mail address removed)

Many Thanks

Varne

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

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

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

 

[Joerg]

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

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
(e-mail address removed)

Many Thanks

Varne

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

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



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

 

[Guest]

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

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

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
(e-mail address removed)

Many Thanks

Varne

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


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



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