start a macro from a func
 

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

start a macro from a func
 

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)

"FTCA" wrote in message
...
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

start a macro from a func
 

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




"Bob Phillips" wrote in message
...
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)

"FTCA" wrote in message
...
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

start a macro from a func
 

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)

"GB" wrote in message
...
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




"Bob Phillips" wrote in message
...
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)

"FTCA" wrote in message
...
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

start a macro from a func
 

Bob

Okay, nothing useful - hmm, shame but thanks anyway.

Geoff


"Bob Phillips" wrote in message
...
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)

"GB" wrote in message
...
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




"Bob Phillips" wrote in message
...
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)

"FTCA" wrote in message
...
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