activate macro with a fomula

[ricklowry]

I would like to activate a macro with a formula or hyperlink. Can this be done?

 

[DRJ]

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]

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]

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]

_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]

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 autpen 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]

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 Autpen 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]

Tom Urtis,
"You Very Smart man" and apparently I am kinda dumb. :wink: Thanks for your help, it works like a charm.
Rick