Button VBA To Refer To A Cell

[JIMBOB77]

Hi
I Have A Workbook.
In Sheet 1, Column A Contains Order Numbers.
In Column B I Have Buttons (1 Per Row).

In Sheet 2, Column A Contains The Same Order Numbers (But With Different Info)

What I Need To Do Is...
If I Click The Button In B24 It Takes Reference Of The Value In A24 Then Goes To Sheet 2 And Finds The Cell In Column A Of The Same Value.

I Need To Use The Same Macro For All Buttons (Eg It Refers To The Cell To The Left Of The Button)

 

[James006]

Hi,

You could make your life much easier ...

1. By deleting ALL your Buttons ...

2. And inserting a single Event macro ...

Hope this will help

 

[JIMBOB77]

Thanks James
Not Sure What You Mean - Can You Expand
Sorry

 

[Yongle]

Here is one example
Test by double-clicking on any cell in column A in sheet1

Paste code in the SHEET module of sheet1
(by right clicking on sheet tab \ View Code \ pasting code into that window \ {ALT}{F11} takes you back to Excel)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row = 1 Or Target.Column > 1 Then Exit Sub
    Dim ws As Worksheet: Set ws = Sheets("Sheet2")
    Cancel = True
    On Error Resume Next
    ws.Select
    Application.Goto Reference:=ws.Cells(WorksheetFunction.Match(Target, ws.Columns(1), 0), 1)
    If Err.Number > 0 Then MsgBox Target & " not found"
End Sub

 

[My Aswer Is This]

I agree. Having hundreds or even several buttons one for each row is not the best way to do things.

You said:

If I Click The Button In B24 It Takes Reference Of The Value In A24 Then Goes To Sheet 2 And Finds The Cell In Column A Of The Same Value.

And what happens when we find that value?

So we could write a script where if you:

Double click on A24 the script will look for the value in A24
Searching column A of sheet (2) and if found select that cell.

Would this work for you?
This would then not require any buttons and only one small script.

This script would work when you double click on any cell in column A if this cell is not empty.
​

 

[James006]

@ Yongle

Thanks ... your event macro will make Jimbob 's life easier ...

 

[JIMBOB77]

Thanks James
Almost Works...
The Order Number I Double Click On is "131"
When I Double Click It Takes Me To Sheet2 But I Get Message "131 Not Found"
131 does exist in column A Of Sheet2

Any Ideas

 

[Yongle]

There must be something different between the 2 cell values or Sheet2 has a different name

Manual test - enter this formula in sheet1 and amend the refernce to the cell in sheet1 containing order 131
=MATCH(A8,Sheet2!A:A,0)

If you get #N/A there is no match

 

[My Aswer Is This]

The script works for me.
Do you have 131 in column A of sheet2
Or do you have My age is 131 or something like that

Thanks James
Almost Works...
The Order Number I Double Click On is "131"
When I Double Click It Takes Me To Sheet2 But I Get Message "131 Not Found"
131 does exist in column A Of Sheet2

Any Ideas

 

[JIMBOB77]

As Expected I Get N/A
What Could Be Different About The Numbers??