Solved: Hyperlink activation of Script/Macro

[bassnsjp]

I'm using MS Windows XP Pro and MS Office 2003. Here is an example of a system configuration worksheet. The worksheet is designed to track the configuration levels of various components of particular systems, in this example, the ISUN system is used. I Don't know how to insert multiple spaces or tabs so I'm using hypens ("-") in place of.

System---NDI Configuration
ISUN-----ISUN-NDI-AA-03.00

The configuration level represents the products that consititutes that particular level, which is at least one product, but usually represents many different products. Here is an example of the ISUN-NDI-AA-03.00 configuration to product worksheet.

Configuration---------Product
ISUN-NDI-AA-03.00-- MS Windows XP Pro
ISUN-NDI-AA-03.00-- MS Office 2003
ISUN-NDI-AA-03.00-- Adobe Reader 8
ISUN-NDI-AA-03.00-- Norton Utilities 14

Both worksheets exists in the same workbook. What I would like to do is click on the cell in the system configuration worksheet that contains ISUN-NDI-AA-03.00 and have a script activated to read the current active cell and search the product configuration worksheet and display all the products that are associated with that particular configuration level. Is there a way to click on the contents of a cell to activate the execution of a script/macro? Any assistance would be greatly appreciated, thank you in advance.

[mdmackillop]

Paste this in the Worksheet module. Do you have the macro for the rest?
[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call Macro1(Target.Value)
End Sub

Sub Macro1(Data)
MsgBox Data
End Sub

[/VBA]

[bassnsjp]

Mack,

No I do not have the rest of the macro developed yet, but don't foresee that being a problem. Thanks for your quick reply. However, not sure how the statements you provided would get executed. Is there some hyperlink required in the cell? Thanks in advance for your efforts.

Steve

[mdmackillop]

Double clicking any celll will trigger the macro. By testing the range address/column/row, you can limit the double click response to a range or a single cell.

[bassnsjp]

Mack,

To test this out I created the following test macro, but double clicking on cell B3 or any cell does nothing. Obviously, I'm not getting it. Your assistance is requested once again to set me straight, thanks in advance.

[VBA]
Option Explicit
Sub main()
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("B3:B3")
Call Worksheet_BeforeDoubleClick(myrange)
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call Macro1(Target.Value)
End Sub

Sub Macro1(Data)
MsgBox Data
End Sub
[/VBA]

[mdmackillop]

Here's a sample where B1:B3 will run the macro.

[vba]
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyRange As Range
Set MyRange = Range("B1:B3")
If Not Intersect(Target, MyRange) Is Nothing Then
Call Macro1(Target.Value)
Cancel = True 'Cancels normal double click behaviour
End If
End Sub

Sub Macro1(Data)
MsgBox Data
End Sub

[/vba]

[bassnsjp]

Mack,

I saved the attached DoubleClick.xls file and ran it with no problems. That is pretty awesome. So, the question I have is how is that done? I opened up the VBA editor and expected to see the module, but it didn't exist. Where is the module stored in order to be executed, thanks again.

Steve

[mdmackillop]

This type of macro is known as an Event macro. It is stored in the module for the Object to which the Event applies. In this case it is the Module for Sheet1

[bassnsjp]

Mack,

Awww, ok, got it. Now, in this instance would the entire set of macros have to exist within the worksheet object or can macros from Modules be called? If so, how would that be done. Your assistance is greatly appreciated.

Steve

[mdmackillop]

The other macros may be saved in a standard module. This allows one macro to be called form any/all of the sheet event macros.

[bassnsjp]

Mack,

I tested calling another module from the event module and it works great. Thank you again for all your efforts and assistance. I don't know how to mark this issue closed/solved, if you can please do, thanks.

Steve

[mdmackillop]

Hi Steve,
You mark a thread Solved using the Thread Tools dropdown
Happy to help out
Regards
MD