Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

Activate a Macro With Only One Clic

Posted by oeloel on August 04, 2000 8:52 PM

Please help me on this: I want to activate a macro with one clic in a specific cell. I also have heard of running a macro with two clics in a cell. I’ll appreciate if anybody comes up with an explanation on the two ways. Thanks in advance.

Check out our Excel VBA Resources

Re: Activate a Macro With Only One Clic

Posted by Ivan Moala on August 05, 0100 3:07 AM

One click in a specific cell means Activating
that cell so you should lool @ sheet event handlers
eg Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Look up online help for this, the key to this
being able to use the Target = your cell to activate the macro.

HTH

Ivan

Re: Activate a Macro With Only One Clic - Example

Posted by Ivan Moala on August 05, 0100 6:11 AM

Here is an example of triggering a macro when
it is in the named range = Totals sales

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim TtlSales As Range
Dim TriggerRg As Range

Set TtlSales = Range("Total_Sales")
Set TriggerRg = Application.Intersect(Target, TtlSales)
If TriggerRg Is Nothing Then End

If TriggerRg.Areas.Count = 1 Then
Call MyMacro
End If

End Sub

Ivan

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.