activate macro with a fomula

ricklowry

New Member
Joined
Mar 3, 2004
Messages
11
I would like to activate a macro with a formula or hyperlink. Can this be done? :p
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Hi, welcome to the board

For hyperlink try making the hyperlink on a label and giving the label a click subroutine.

for a formula, I assume you want to run the macro if the formula has a certain value?

Put that code in the sheet_change subroutine

lets say the formula is in A1 and you want the macro to run if the value is 1 then try something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

if range("A1").value = 1 then
code here
else
end if


End Sub

Hope this helps

Jacob
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
I doubt that would help Jacob. The issue is
"activate a macro with a formula or hyperlink."

So, an event in the sheet module that would speak to those two possibilities better than your SelectionChange would be

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

or

Private Sub Worksheet_Calculate()
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
oops that should have been sheet_change, not selection change. I must have clicked the wrong one when I typed this up in vbe.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224

ADVERTISEMENT

_Change still would have nothing to do with the issue. Clicking on a hyperlink is not a change. A formula calculation is not a change, though it can be caused by a change in another precedent cell but maybe on a different sheet, so since the result needing to trigger a macro is formula-driven, a Calc event in the sheet module would be the safer bet given the choice between Change and Calc.
 

ricklowry

New Member
Joined
Mar 3, 2004
Messages
11
Gentlemen,
Thank you for your responses. I tried your suggestion but without success.
Perhaps if I get more specific you could give me a hand.
I am importing data from a delimited database and using auto_Open to activate the formatting macros. There is a varying field that requires different formatting depending on what is in the field. The way I would like the formula to read is:
=if(a1="Monday", Columns("B:B").Select.Selection.EntireColumn.Hidden = True, Columns("A:A").Select.Selection.EntireColumn.Hidden = True)
I realize this is combining Visual Basic with an excel formula. I just can't get the macro started and I know of no other way to hide columns automatically.
Thank You in advance for any help you can offer,
Rick
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
Well if it's an import at Open, you really don't need a formula at all, you need an If structure just like you laid out. In fact, it doesn't look like formulas are involved in any way with this based on your description.

Try this block of code to achieve what you say you are after - - just stick it in at the approriate point in your existing Auto_Open macro.

Range("A:B").EntireColumn.Hidden = False
If Range("A1").Value = "Monday" Then
Columns(2).EntireColumn.Hidden = True
Else
Columns(1).EntireColumn.Hidden = True
End If
 

ricklowry

New Member
Joined
Mar 3, 2004
Messages
11
Tom Urtis,
"You Very Smart man" and apparently I am kinda dumb. :wink: Thanks for your help, it works like a charm.
Rick
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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