Can a macro be run using a formula?

ElceyOwen

New Member
Joined
Feb 12, 2004
Messages
4
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

MartinK

Active Member
Joined
Oct 30, 2003
Messages
384
Hello and welcome to the board!
I believe you'll need Worksheet_Change event to trigger the macro you want.
 

ElceyOwen

New Member
Joined
Feb 12, 2004
Messages
4
Cheers Martin, not too familiar with in depth excel. How do you use Worksheet_Change?
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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: -

Code:
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

New Member
Joined
Feb 12, 2004
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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: -

Code:
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

Active Member
Joined
Oct 30, 2003
Messages
384

ADVERTISEMENT

Try something like this:
Code:
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

New Member
Joined
Feb 12, 2004
Messages
4
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?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,490
Messages
5,764,666
Members
425,227
Latest member
SpreadsheetNoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top