How to run a macro when user enters a value in a cell?

[serdar]

See the question about vbasic functions below also.

 

[KL]

Hi serdar,

You may want to use the Change event of the worksheet. For example copy the
following code to the VBA module of your active worksheet and try changing
value in cell [A1]:

'---code start----
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
Call Test
End Sub

Sub Test()
MsgBox "Hello!"
End Sub
'---code end----

Reagrds,
KL

 

[serdar]

thanks. this may be useful, and this is what i want to do actually, if u are
interested:

I have a workbook with one master worksheet and several others for each bank
i work with.
When i enter a new record in the master worksheet (for instance i get a
check from citibank) this record should go to the appropriate place in the
"citibank worksheet".

Briefly, i have a master worksheet that has all the records that i entered,
and others each has only the fitting records.

Hi serdar,

You may want to use the Change event of the worksheet. For example copy the
following code to the VBA module of your active worksheet and try changing
value in cell [A1]:

'---code start----
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
Call Test
End Sub

Sub Test()
MsgBox "Hello!"
End Sub
'---code end----

Reagrds,
KL

See the question about vbasic functions below also.

 

[KL]

Hi serdar,

In what you are describing there are a lot of variables to take into account
when writing a code. If you need advice on how to write such a code, you
will have to be a lot more specific about the workbook and data structure as
well as the intended workflow.

Regards,
KL

thanks. this may be useful, and this is what i want to do actually, if u
are
interested:

I have a workbook with one master worksheet and several others for each
bank
i work with.
When i enter a new record in the master worksheet (for instance i get a
check from citibank) this record should go to the appropriate place in the
"citibank worksheet".

Briefly, i have a master worksheet that has all the records that i
entered,
and others each has only the fitting records.

Hi serdar,

You may want to use the Change event of the worksheet. For example copy the
following code to the VBA module of your active worksheet and try
changing
value in cell [A1]:

'---code start----
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
Call Test
End Sub

Sub Test()
MsgBox "Hello!"
End Sub
'---code end----

Reagrds,
KL

See the question about vbasic functions below also.

 

[Dave Peterson]

I wouldn't do this. There's too many things that can go wrong. My typing could
make it so that that event copies something to the wrong sheet and then I have
to find where it went and correct it in multiple spots.

In fact, if you use data|filter|autofilter, you may not even have to move the
data to separate sheets. (I'd try my best to keep one set of data. If you have
two copies, it won't be too long before one version is different from the other.

But if you must, maybe you could use a program (on demand) that would move or
copy your data.

I'd borrow some code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Ron de Bruin has an addin that may do what you want right out of the box:
http://www.rondebruin.nl/easyfilter.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

thanks. this may be useful, and this is what i want to do actually, if u are
interested:

I have a workbook with one master worksheet and several others for each bank
i work with.
When i enter a new record in the master worksheet (for instance i get a
check from citibank) this record should go to the appropriate place in the
"citibank worksheet".

Briefly, i have a master worksheet that has all the records that i entered,
and others each has only the fitting records.

Hi serdar,

You may want to use the Change event of the worksheet. For example copy the
following code to the VBA module of your active worksheet and try changing
value in cell [A1]:

'---code start----
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
Call Test
End Sub

Sub Test()
MsgBox "Hello!"
End Sub
'---code end----

Reagrds,
KL

See the question about vbasic functions below also.

 

[serdar]

Thank for advices Dave.
I wrote the code and just thinking about the problem u have metioned.
I'm gonna send the code to the newsgroup. Take a look if u r still
interested.

I wouldn't do this. There's too many things that can go wrong. My typing could
make it so that that event copies something to the wrong sheet and then I have
to find where it went and correct it in multiple spots.

In fact, if you use data|filter|autofilter, you may not even have to move the
data to separate sheets. (I'd try my best to keep one set of data. If you have
two copies, it won't be too long before one version is different from the other.

But if you must, maybe you could use a program (on demand) that would move or
copy your data.

I'd borrow some code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Ron de Bruin has an addin that may do what you want right out of the box:
http://www.rondebruin.nl/easyfilter.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

thanks. this may be useful, and this is what i want to do actually, if u are
interested:

I have a workbook with one master worksheet and several others for each bank
i work with.
When i enter a new record in the master worksheet (for instance i get a
check from citibank) this record should go to the appropriate place in the
"citibank worksheet".

Briefly, i have a master worksheet that has all the records that i entered,
and others each has only the fitting records.

Hi serdar,

You may want to use the Change event of the worksheet. For example

copy
the

following code to the VBA module of your active worksheet and try changing
value in cell [A1]:

'---code start----
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1]) Is Nothing Then _
Call Test
End Sub

Sub Test()
MsgBox "Hello!"
End Sub
'---code end----

Reagrds,
KL


See the question about vbasic functions below also.