Macro execution from Excel IF function

[Bryan Morris]

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

 

[Guest]

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...

 

[R. Choate]

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


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...

 

[Tom Ogilvy]

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

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

 

[Guest]

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...

 

[Bryan Morris]

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

B

 

[Tom Ogilvy]

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