Macro question.

[Guest]

Hello!
I was wondering if there was any way to make a macro run in the background
without having to run the command over and over again.

basically my macro finds data and displays it in the cell. The data found is
dependent upon the string entered in another cell.

I dont want it to crash the program or something if it keeps running voer
and over again but for the purpose i'm making it for it needs to be somewhat
automatic.

If theres a command i could type after the formula in my cell that would
automatically run the macro after a new value is entered that would be great.

Thanks!
Brandon Roland

 

[Guest]

Look at Events

http://www.cpearson.com/excel/events.htm

Sounds like you need to use the Change Event which will fire on every change
to every cell, but you can do it like this - so it only actually does work on
a change to cell B9 for example.

Private Sub Worksheet_Change(ByVal Target As Range)
If target.count > 1 then exit sub
On Error goto ErrHandler
if Target.Address = "$B$9" then
Application.EnableEvents = False
mymacro ' runs your macro
end if
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Guest]

Hey Tom thanks for the quick reply!

So I just add this to the top of my module>

Thanks,
BR

 

[Guest]

Hey Tom I tried putting it in my module and chaning it to F2 and it's not
working.. maybe im putting it in the wrong place?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$F$2" Then
Application.EnableEvents = False
GetValues 'my macro
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Guest]

NO. Right click on the sheet tab for the sheet where you want this behavior
and select view code. In the left dropdown at the top of the module choose
worksheet and in the right dropdown at the top of the module select Change.
It is always best to select event code from the dropdowns so the proper
declarations are made for the arguments.

Now add the code I showed to these procedures/events.

The macro you have it call should be in a general module (insert=>Module)
whidh is what I assume you mean by "to the top of my module". If you are
already using a sheet module, you would be better off just using that for
event code related to worksheet events or activeX controls placed on that
worksheet (in most cases) and using General/standard modules for other code.