I am trying to get a vlookup with mouse-over to work, however I am having
difficulty assigning a Global Variable, and cannot identify what variables to
declare.

My situation:
data.xls - Columns A:B hold the vlookup information
report.xls - is the file I want to apply this macro to, so that when I right
click on a value in Column A it will look into the data.xls file and return
the value in a popup.

I need to define the range variable (I assumed the range variable was the
€œTheCell€� but I also see €œDataElements€� and €œLookupRange€� €“ are these all
variables that need to be defined?) within report.xls. My previous attempt
was:

Sub RVariable()
Dim TheCell As Range
TheCell = A: D
End Sub

I have a feeling I should not be using a sub, as the macro above does not
call to it - I am a novice with VB and usually work backwards from a finished
product so any assistance would be greatly appreciated.

Code taken from previous response:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Dim obj As Object
Set TheCell = Target
For Each obj In Application.CommandBars("cell").Controls
If obj.Tag = "HereIsYourItem" Then obj.Delete
Next obj
If Not Application.Intersect(Target, Range("DataElements")) _
Is Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, befo=6, _
temporary:=True)
.Caption = "See The Definition"
.OnAction = "HereIsYourMacro"
.Tag = "HereIsYourItem"
End With
End If
End Sub


'In the separate module:

Option Explicit
Public TheCell As Range

Sub HereIsYourMacro()
MsgBox Evaluate("=VLOOKUP(""" & TheCell.Value & """,LookupRange,2)")

End Sub

First, to assign to an object variable, you must use the "Set" keyword. An
object variable is any variable that is not just a simple data place order
or an array of simple data place holders. Variable objects could reference
to objects built within the program, reference to class modules built by who
ever, or other objects linked to the workbook/application rather it be
directly referenced during design time (early binding) or reference during
run time (late binding)

To assign a range to a range object variable, use one of the acceptable
means of doing it, which is via Range Object, Cells Object (In the case of a
single cell as a range) or square brackets like the following:

Set TheCell = Range("A:D")
Set TheCell = [A:D]

The only problem with the above coding, the range object will be referencing
to what ever workbook and worksheet is the currently active
workbook/worksheet at the time that line of code is executed, so to get
around that, you may want to prequalify the range object with the workbook
and worksheet objects like the following:

Set TheCell = ThisWorkbook.Worksheets("Sheet1").Range("A:D")

ThisWorkbook references to the workbook that the line of code resides in.


Next, if you want to have this come up at the time when the right click is
taking place on the worksheet, you will need to use the BeforeRightClick
Event on the worksheet. To do that:

Double click on the worksheet object within VBA
Click on the drop down above the code with the left side drop down, and
click on "Worksheet"
On the right side at the top, click on the Drop down, then click on
"BeforeRightClick"

You then use the following code to find out if the user clicked on one of
the first 2 columns on the worksheet.

If Target.Column < 3 Then
<Statements
End If

From there, you will then use the CommandBarPopup object and add items to it
via the Controls Property. Also, you will set the Type Property to the type
deemed appropriate such as msoControlPopup.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Bryand" wrote in message
...
I am trying to get a vlookup with mouse-over to work, however I am having
difficulty assigning a Global Variable, and cannot identify what variables
to
declare.

My situation:
data.xls - Columns A:B hold the vlookup information
report.xls - is the file I want to apply this macro to, so that when I
right
click on a value in Column A it will look into the data.xls file and
return
the value in a popup.

I need to define the range variable (I assumed the range variable was the
"TheCell" but I also see "DataElements" and "LookupRange" - are these all
variables that need to be defined?) within report.xls. My previous attempt
was:

Sub RVariable()
Dim TheCell As Range
TheCell = A: D
End Sub

I have a feeling I should not be using a sub, as the macro above does not
call to it - I am a novice with VB and usually work backwards from a
finished
product so any assistance would be greatly appreciated.

Code taken from previous response:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Dim obj As Object
Set TheCell = Target
For Each obj In Application.CommandBars("cell").Controls
If obj.Tag = "HereIsYourItem" Then obj.Delete
Next obj
If Not Application.Intersect(Target, Range("DataElements")) _
Is Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, befo=6, _
temporary:=True)
.Caption = "See The Definition"
.OnAction = "HereIsYourMacro"
.Tag = "HereIsYourItem"
End With
End If
End Sub


'In the separate module:

Option Explicit
Public TheCell As Range

Sub HereIsYourMacro()
MsgBox Evaluate("=VLOOKUP(""" & TheCell.Value & """,LookupRange,2)")

End Sub

Paste the following code into the sheet module of report.xls
(right-click the sheet tab and choose view code)
Any existing code in the module should be removed before adding my code.

For it to work, the data.xls workbook must be open.
Right-click a cell in the first 50 rows of column "A" (report.xls workbook)
and the value from the column to the right of the lookup value in the
data.xls workbook (first sheet) is displayed in a fake ToolTip.
Change "50" in the 'Set rngStart' line to the actual number of rows.
There is no error handling in the code.

'--
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
'Jim Cone - Portland, Oregon USA - April 2009
Dim rngStart As Excel.Range
Dim vRngValue As Variant
Dim rngWhat As Excel.Range
Set rngStart = Application.Intersect(Me.Columns(1), Me.Range("1:50"))
If Not Application.Intersect(Target(1), rngStart) Is Nothing Then
Cancel = True
rngStart.Validation.Delete
On Error Resume Next
Set rngWhat = _
Workbooks("data.xls").Worksheets(1) _
.UsedRange.Find(Target(1).Value).Offset(0, 1)
On Error GoTo 0
If Not rngWhat Is Nothing Then
vRngValue = rngWhat.Value
Else
vRngValue = "Not Found"
End If
With Target(1).Validation
.Add Type:=xlValidateCustom, _
AlertStyle:=xlValidAlertInformation, Formula1:=True
.InCellDropdown = True
.InputTitle = vbNullString
.InputMessage = vRngValue
.ShowInput = True
End With
End If
End Sub
--

Jim Cone
Portland, Oregon USA





"Bryand"
wrote in message
I am trying to get a vlookup with mouse-over to work, however I am having
difficulty assigning a Global Variable, and cannot identify what variables to
declare.

My situation:
data.xls - Columns A:B hold the vlookup information
report.xls - is the file I want to apply this macro to, so that when I right
click on a value in Column A it will look into the data.xls file and return
the value in a popup.

I need to define the range variable (I assumed the range variable was the
€œTheCell€� but I also see €œDataElements€� and €œLookupRange€� €“ are these all
variables that need to be defined?) within report.xls. My previous attempt
was:

Sub RVariable()
Dim TheCell As Range
TheCell = A: D
End Sub

I have a feeling I should not be using a sub, as the macro above does not
call to it - I am a novice with VB and usually work backwards from a finished
product so any assistance would be greatly appreciated.

Code taken from previous response:
-snip-