Call a macro from a cell

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 3, 2007

• #2

do you mean upon selecting the cell? Or if Cell Value = X?

Either way is possible, but clarify please.

 

Upvote 0

L

leoh48763

Board Regular

Joined

Apr 2, 2005

Messages

64

• Jan 3, 2007

• #3

Thanks for the reply, I need to link a product code to a product. I would normally would use nested if functons in excel, however excel only allows for seven. If the user selects 1/2 Reg from cell A2, then I need GB4080 to come up in cell B2. I can do this with macros but I'm trying to make it automatic instead of having the user call the macro through a button, ect....Thanks again, Leo...

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 3, 2007

• #4

Ok, so the question is, do you want the macro to run upon a specific cell's VALUE changing to a specific value? If so, which cell and what value?


Or would you like the macro to run simply after selecting a specific cell. If so, which cell?

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 3, 2007

• #5

Ok, I see 3 different ways to do it.

1. If User simply selects specific cell, run macro

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Range("a1"), Target) Is Nothing Then Exit Sub
Application.Run "mymacro"
End Sub

2. If specific cell value changes to specific value VIA User Entry

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("a1"), Target) Is Nothing Then Exit Sub
If Range("a1").Value = "X" Then Application.Run "mymacro"
End Sub

3. If specific cell value changes to specific value VIA Formula

Code:

Private Sub Worksheet_Calculate()
If Range("a1").Value = "X" Then Application.Run "mymacro"
End Sub

All 3 examples use Cell A1
All 3 examples use "mymacro" as the name of the macro
Examples 2 and 3 use "X" as the value to trigger macro

adjust as needed

All 3 codes should be pasted into the Worksheet Code

 

Upvote 0

L

leoh48763

Board Regular

Joined

Apr 2, 2005

Messages

64

• Jan 3, 2007

• #6

Thanks, That will get me started

 

Upvote 0

F

Fitzhay

Active Member

Joined

Jun 10, 2005

Messages

486

• Jan 3, 2007

• #7

Hi

While I'm sure jonmo's solution is far more elegant and efficent it is possible to have more than 7 nested IF statements. After your first 7 have been completed with all the )) closed off just add another IF statement joined by the & symbol. This may be easier in some cases where you want to add another 2 or 3 conditions to your original statement.

ie: =IF(B1=1,"A",IF(B1=2,"B",IF(B1=3,"C",IF(B1=4,"D",IF(B1=5,"E",IF(B1=6,"F",IF(B1=7,"G","")))))))&IF(B1=8,"H",IF(B1=9,"I",""))

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jan 3, 2007

• #8

Sweeeeeet!!!!

I've looked and looked and looked for a way to do that.

Nice.

The only drawback is that makes the result text. Which can complicate things if you need numbers....That can be resolved like this

Code:

=--(IF(B1=1,"1",IF(B1=2,"2",IF(B1=3,"3",IF(B1=4,"4",IF(B1=5,"5",IF(B1=6,"6",IF(B1=7,"7","")))))))&IF(B1=8,"8",IF(B1=9,"9","")))

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,650

Office Version

1. 365

Platform

1. Windows

• Jan 3, 2007

• #9

Thanks for the reply, I need to link a product code to a product. I would normally would use nested if functons in excel, however excel only allows for seven. If the user selects 1/2 Reg from cell A2, then I need GB4080 to come up in cell B2. I can do this with macros but I'm trying to make it automatic instead of having the user call the macro through a button, ect....Thanks again, Leo...

Click to expand...

Why not use VLOOKUP?

 

Upvote 0