Can a macro be run using a formula?

[ElceyOwen]

Does anyone know if a macro can be executed or run in a formula?
For example:

=IF(B57="France",...........)

This: .......... would be where the macro would be told to execute?

I have created some macro's to insert a picture in a cell but want to know how to run the macro if a B57 only says something imparticular.

 

[MartinK]

Hello and welcome to the board!
I believe you'll need Worksheet_Change event to trigger the macro you want.

 

[ElceyOwen]

Cheers Martin, not too familiar with in depth excel. How do you use Worksheet_Change?

 

[Mudface]

No, you can't run a macro directly from within a formula, but you can run code based on the Calculation or Worksheet_Change events. As an example, right-click on the worksheet tab, choose View Code and copy and paste in the following: -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$57" Then
    If Target = "France" Then
        ' add your code here or a call to one of your macros
    End If
End If

End Sub

 

[ElceyOwen]

That looks as close I have got so far. Can you put more than 1 macro in the same code cos I have 16 that I would need to put in the same code. Because their is 16 different possibilities for cell B57 I have 1 different macro that I would need to run for each possibility.

 

[Mudface]

Could you post up one of your exisiting macros. If they're similar you might be able to compact them all into the one by sending an argument through the event change code. As a simple example using the above: -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$57" Then
    Call YourMacro(Target.Value)
End If

End Sub

Public Sub YourMacro(strArgument As String)

MsgBox strArgument

End Sub

When cell B57 gets changed, what you've typed in there gets passed to your macro and shown in a message box. You would use the strArgument variable to decide which picture gets added (eg if you typed in 'France' the picture called France would get pasted etc).

 

[MartinK]

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$57" Then
    Select Case Target
    
    Case "France":    Call xxxxx 'your macro for France
    Case "Italy":       Call yyyyy 'Your code for Italy
    
    End Select
    
End If

End Sub

 

[ElceyOwen]

That sounds brilliant, merging all the macro's to one. This is an example of one of the macro's, all the others are the same/similar:

--------------------------------------------------

Sub France()
'
' France Macro
' Macro recorded 12/02/2004 by adamelce
'
' Keyboard Shortcut: Ctrl+Shift+F
'
ActiveSheet.Shapes("Group 42").Select
Selection.Delete
Sheets("Sheet3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B62:T68").Select
ActiveSheet.Paste
Selection.ShapeRange.IncrementTop -0.75
Selection.ShapeRange.IncrementLeft -0.75
End Sub

----------------------------------------------------
What this does, as you can probably gather, is it deletes the current picture in the cell, goes to sheet 3 and selects the relevant picture (ie France) and copies it, returns to the origanal sheet and pastes it in the relevant cell, nudging it once up and once left to fit.

I don't know whether you could work from this?

 

[MartinK]

I have a nice solution, only... I can't make it work.

So I posted a new thread here: http://www.mrexcel.com/board2/viewtopic.php?p=368224#368224
Martin