I want to automatically run a macro if the value of a cell
changes (0 - 12)

I have found the command 'MacroRun()', but I cannot get a
valid macro name to execute

Using Excel 2000

Thanks

You want to catch the on change event. In the vba window, choose the sheet
you want to monitor. (double click on it). In the upper left of the code
window you will find a drop down box with the word general in it. Change it
to worksheet. In the drop down box just to the right select OnChange.

The beginnings of a sub precedure will be inserted into the code window.
Target is the cell that was just changed. Add some code to the procedure
similar to this.

if target.address = "$A$1" then
select case target.value
case 1
msgbox "One"
case 2
msgbox "Two"
'...
end select
end if

Hope this helps...

"Bryan Morris" wrote:

I want to automatically run a macro if the value of a cell
changes (0 - 12)

I have found the command 'MacroRun()', but I cannot get a
valid macro name to execute

Using Excel 2000

Thanks

Be aware that when you use the On Change event, the macro obviously runs anytime any cell on the sheet is changed. You will
sometimes see a slight "jiggle" of the worksheet when it kicks in the macro operation and updates the spreadsheet. It will
definitely slow down the overall speed of the spreadsheet, and you may have to wait for a slight delay while the macro runs before
you can enter something in a cell after you have just entered something somewhere on the spreadsheet. Since it is a worksheet change
event and not a single cell change event, you might want to test this and see if you like the results.

HTH
--
RMC,CPA


"Jim Thomlinson" wrote in message
...
You want to catch the on change event. In the vba window, choose the sheet
you want to monitor. (double click on it). In the upper left of the code
window you will find a drop down box with the word general in it. Change it
to worksheet. In the drop down box just to the right select OnChange.

The beginnings of a sub precedure will be inserted into the code window.
Target is the cell that was just changed. Add some code to the procedure
similar to this.

if target.address = "$A$1" then
select case target.value
case 1
msgbox "One"
case 2
msgbox "Two"
'...
end select
end if

Hope this helps...

"Bryan Morris" wrote:

I want to automatically run a macro if the value of a cell
changes (0 - 12)

I have found the command 'MacroRun()', but I cannot get a
valid macro name to execute

Using Excel 2000

Thanks

This sounds like you are performing a change in the change event and not
disabling events - thus causing a recursive call to change. If you disable
events in this situation, this should minimize the experience you describe.

--
Regards,
Tom Ogilvy


"R. Choate" wrote in message
...
Be aware that when you use the On Change event, the macro obviously runs
anytime any cell on the sheet is changed. You will
sometimes see a slight "jiggle" of the worksheet when it kicks in the
macro operation and updates the spreadsheet. It will
definitely slow down the overall speed of the spreadsheet, and you may
have to wait for a slight delay while the macro runs before
you can enter something in a cell after you have just entered something
somewhere on the spreadsheet. Since it is a worksheet change
event and not a single cell change event, you might want to test this and
see if you like the results.

HTH
--
RMC,CPA


"Jim Thomlinson" wrote in
message
...
You want to catch the on change event. In the vba window, choose the sheet
you want to monitor. (double click on it). In the upper left of the code
window you will find a drop down box with the word general in it. Change
it
to worksheet. In the drop down box just to the right select OnChange.

The beginnings of a sub precedure will be inserted into the code window.
Target is the cell that was just changed. Add some code to the procedure
similar to this.

if target.address = "$A$1" then
select case target.value
case 1
msgbox "One"
case 2
msgbox "Two"
'...
end select
end if

Hope this helps...

"Bryan Morris" wrote:

I want to automatically run a macro if the value of a cell
changes (0 - 12)

I have found the command 'MacroRun()', but I cannot get a
valid macro name to execute

Using Excel 2000

Thanks

OK - So what is the command to disable the events while
the macro call is running?

B
-----Original Message-----
This sounds like you are performing a change in the
change event and not
disabling events - thus causing a recursive call to
change. If you disable
events in this situation, this should minimize the
experience you describe.

--
Regards,
Tom Ogilvy


"R. Choate" wrote in message
...
Be aware that when you use the On Change event, the
macro obviously runs
anytime any cell on the sheet is changed. You will
sometimes see a slight "jiggle" of the worksheet when
it kicks in the
macro operation and updates the spreadsheet. It will
definitely slow down the overall speed of the
spreadsheet, and you may
have to wait for a slight delay while the macro runs
before
you can enter something in a cell after you have just
entered something
somewhere on the spreadsheet. Since it is a worksheet
change
event and not a single cell change event, you might
want to test this and
see if you like the results.

HTH
--
RMC,CPA


"Jim Thomlinson"
wrote in
message
news:51FFC373-15D4-46A7-B8F3-
...
You want to catch the on change event. In the vba
window, choose the sheet
you want to monitor. (double click on it). In the upper
left of the code
window you will find a drop down box with the word
general in it. Change
it
to worksheet. In the drop down box just to the right
select OnChange.

The beginnings of a sub precedure will be inserted into
the code window.
Target is the cell that was just changed. Add some code
to the procedure
similar to this.

if target.address = "$A$1" then
select case target.value
case 1
msgbox "One"
case 2
msgbox "Two"
'...
end select
end if

Hope this helps...

"Bryan Morris" wrote:

I want to automatically run a macro if the value of a
cell
changes (0 - 12)

I have found the command 'MacroRun()', but I cannot
get a
valid macro name to execute

Using Excel 2000

Thanks





.

I was talking to R. Choate, but

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error goto ErrHandler:
Application.EnableEvents = False
if application.IsText(Target) then
Target.Value = ucase(Target.Value)
End if
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Bryan Morris" wrote in message
...
OK - So what is the command to disable the events while
the macro call is running?

B
-----Original Message-----
This sounds like you are performing a change in the
change event and not
disabling events - thus causing a recursive call to
change. If you disable
events in this situation, this should minimize the
experience you describe.

--
Regards,
Tom Ogilvy


"R. Choate" wrote in message
...
Be aware that when you use the On Change event, the
macro obviously runs
anytime any cell on the sheet is changed. You will
sometimes see a slight "jiggle" of the worksheet when
it kicks in the
macro operation and updates the spreadsheet. It will
definitely slow down the overall speed of the
spreadsheet, and you may
have to wait for a slight delay while the macro runs
before
you can enter something in a cell after you have just
entered something
somewhere on the spreadsheet. Since it is a worksheet
change
event and not a single cell change event, you might
want to test this and
see if you like the results.

HTH
--
RMC,CPA


"Jim Thomlinson"
wrote in
message
news:51FFC373-15D4-46A7-B8F3-
...
You want to catch the on change event. In the vba
window, choose the sheet
you want to monitor. (double click on it). In the upper
left of the code
window you will find a drop down box with the word
general in it. Change
it
to worksheet. In the drop down box just to the right
select OnChange.

The beginnings of a sub precedure will be inserted into
the code window.
Target is the cell that was just changed. Add some code
to the procedure
similar to this.

if target.address = "$A$1" then
select case target.value
case 1
msgbox "One"
case 2
msgbox "Two"
'...
end select
end if

Hope this helps...

"Bryan Morris" wrote:

I want to automatically run a macro if the value of a
cell
changes (0 - 12)

I have found the command 'MacroRun()', but I cannot
get a
valid macro name to execute

Using Excel 2000

Thanks





.

If you get a screen jiggle then you can remove that with

application.screenupdate = false
followed by
application.screenupdate = true
when the procedure ends.

As to the speed issue you are talking about maybe executing a few hundred
lines of code (in all likelyhood). Based on that I highly doubt that you are
going to see any sort of perfomance issue. That much code will execute faster
than your screen will refresh (unless your code is particularily inefficient).

Hope this helps...
"R. Choate" wrote:

Be aware that when you use the On Change event, the macro obviously runs anytime any cell on the sheet is changed. You will
sometimes see a slight "jiggle" of the worksheet when it kicks in the macro operation and updates the spreadsheet. It will
definitely slow down the overall speed of the spreadsheet, and you may have to wait for a slight delay while the macro runs before
you can enter something in a cell after you have just entered something somewhere on the spreadsheet. Since it is a worksheet change
event and not a single cell change event, you might want to test this and see if you like the results.

HTH
--
RMC,CPA


"Jim Thomlinson" wrote in message
...
You want to catch the on change event. In the vba window, choose the sheet
you want to monitor. (double click on it). In the upper left of the code
window you will find a drop down box with the word general in it. Change it
to worksheet. In the drop down box just to the right select OnChange.

The beginnings of a sub precedure will be inserted into the code window.
Target is the cell that was just changed. Add some code to the procedure
similar to this.

if target.address = "$A$1" then
select case target.value
case 1
msgbox "One"
case 2
msgbox "Two"
'...
end select
end if

Hope this helps...

"Bryan Morris" wrote:

I want to automatically run a macro if the value of a cell
changes (0 - 12)

I have found the command 'MacroRun()', but I cannot get a
valid macro name to execute

Using Excel 2000

Thanks