Excel 2013 Macros Click-Activated Shapes

[aworthey]

Hello,

I'm trying to retrieve a value in a cell that is located 6 cells to the left of a shape that the user would click, but I'm have trouble with activating the macro with my named shape. The first problem is with assigning my macro to the named shape; it is called SelectButton.

Here's an excerpt from the code:


Sub Select_Quote1()


Dim path As String
Dim wb As Workbook
Dim ws As Worksheet
Dim warehouse As Workbook
Dim data As Worksheet
Dim row As Integer
Dim clm As Integer
Dim QNumber As Range
Dim rng As Object
Dim SearchRange As Range
Dim RepName As String
Dim dest As Range
Dim copyrange1 As Range, copyrange2 As Range, copyrange3 As Range
Dim destrange1 As Range, destrange2 As Range, destrange3 As Range
Dim queue As String
Dim queue2 As String
Dim stamp As String
Dim datepaste As Range


Application.ScreenUpdating = False


'Reference this Workbook and Worksheet needed for Code
Set wb = ThisWorkbook
Set ws = wb.Sheets("QuoteView")
queue = "PV-" & ws.Range("S4").Value
queue2 = "Hold-" & ws.Range("S4").Value


'Reference the data warehouse Workbook and Worksheet needed for code
path = "\\Uswifs05\indsolutions\Matte\ES Reports\REPORTS\Test\KPS Genset & ATS Quote Index (Automated).xlsm"
Application.Workbooks.Open (path)
Set warehouse = ActiveWorkbook
Set data = warehouse.Sheets("Data")


'Assigns the Value to search in DataTable
Set rng = ws.Shapes(Application.Caller).TopLeftCell
Set QNumber = rng.Offset(0, -6).Value
'Assigns the RepName for the Selected Project
RepName = ws.Range("S4").Text
'Code will paste into the below row number
row = data.Range("B:B").Find(QNumber).row
'Code will paste into the below column number


I appreciate any suggestions!

[mrmmickle1]

If your button is in Cell G1 this will retrieve the value 6 cells to the left... i.e. A1



Sub Test()


Dim ws As Worksheet
Dim strCllAdd As String
Dim myVal As String

Set ws = Sheets("Sheet2")

strCllAdd = ws.Shapes(Application.Caller).TopLeftCell.Address
MyValue = ws.Range(strCllAdd).Offset(0, -6).Value


End Sub