start a macro from a func

  • Thread starter Thread starter FTCA
  • Start date Start date
F

FTCA

Hi All
Can I start a macro in a "IF" Func
e.g

IF ISBLANK(D3), " " , start macro name getweight

i.e
if a certain cell is blank can I use the true response to place a " " (
space chr)
and with the false response can I automatically start
a macro named mymacro?


or is there a 'onfocus' type command like in access
i.e when the cursor hits a cell , the macro is run then the cursor is
moved to the next cell along ready for input.

TIA
 
Functions cannot start a macro, as they only return a result to the cell
they are declared in.

You can use worksheet events, in this case Worksheet_SelectionChange to do
what you want. For Example
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$A$1" Then
myMacro
End If

End Sub

As it is worksheet code it goes in the appropriate worksheet code module.
MyMacro needs to be in that code module as well, or in a normal code module.
But you could just embed that functionality in the event code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hmm. I'm not sure I understand this fully. Consider the following:

Public Function MyTestFunction()
MsgBox "Hi"
MyTestFunction = 33
MsgBox "Hi again"
Application.Workbooks.Close
End Function

This all works, except that the application does not in fact close the
workbook. There are apparently some VBA statements that are executed inside
a user-defined function and some that are not. However, I cannot find
anything useful in the help file.

Regards

Geoff
 
Geoff,

Yes, you can do simple things like that, but nothing useful. In the code you
cannot set any attributes of a workbook or worksheet or range. So you cannot
add or delete rows, you cannot insert or delete sheets, and you cannot set a
cell value, other than by returning a result to the active cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top