User Enters Number in One Worksheet, Taken to Match in Another Worksheet

[Lali2023]

Hello,

Is it possible to create a user dialog box where the user enters a number and gets taken to that matching number in another worksheet? Here is what I mean:

Worksheet 1

3.3
3.4
3.5
3.6

Worksheet 2
3.3 Jon
3.4 Bryan
3.5 Ken
3.6 David


The user clicks to start a macro and the dialog box comes up:

Enter a number:

User enters 3.4, for example, and gets taken to the spot where 3.4 is in Worksheet 2.

Is there a VBA function I can create to do this?

 

[Eric W]

Given at table like this on Sheet2:

Book2
	A	B
1	List	Name
2	3.3	Jon
3	3.4	Bryan
4	3.5	Ken
5	3.6	David
Sheet2

You can use the HYPERLINK function like this:

Book2
	A	B
1	List
2	3.4	Bryan
3	3.6	David
4	3.5	Ken
5	3.3	Jon
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=HYPERLINK("#Sheet2!A" & MATCH(A2,Sheet2!A$2:$A$5,0)+1,VLOOKUP(A2,Sheet2!$A$2:$B$5,2,0))

I had the "friendly name" in the Hyperlink look up the corresponding name from the table on Sheet2. So you can put the number in column A, and the Hyperlink formula will show the name, and if you click on it, it will take you to the table on Sheet2. A dialogbox and a macro will also work, but see if this work for you first.

 

[Peter_SSs]

The user clicks to start a macro and the dialog box comes up:

Enter a number:

Instead of that, would this work for you?
Just double-click on the value in Worksheet 1 and be taken to the relevant value in Worksheet 2?
If so, you could try a Worksheet_BeforeDoubleClick event code like this.
I have assumed that the values of interest that you would double-click on in Worksheet 1 are in column A and the values to be searched for in Worksheet 2 are also in column A.
To implement ..
1. Right click the 'Worksheet 1' sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim rFound As Range
  
  If Target.Column = 1 And Len(Target.Value) > 0 Then
    Cancel = True
    Set rFound = Sheets("Worksheet 2").Columns("A").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If rFound Is Nothing Then
      MsgBox Target.Value & " not found"
    Else
      Application.Goto Reference:=rFound, Scroll:=True
    End If
  End If
End Sub

 

[Lali2023]

Given at table like this on Sheet2:

Book2
	A	B
1	List	Name
2	3.3	Jon
3	3.4	Bryan
4	3.5	Ken
5	3.6	David
Sheet2

You can use the HYPERLINK function like this:

Book2
	A	B
1	List
2	3.4	Bryan
3	3.6	David
4	3.5	Ken
5	3.3	Jon
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=HYPERLINK("#Sheet2!A" & MATCH(A2,Sheet2!A$2:$A$5,0)+1,VLOOKUP(A2,Sheet2!$A$2:$B$5,2,0))

I had the "friendly name" in the Hyperlink look up the corresponding name from the table on Sheet2. So you can put the number in column A, and the Hyperlink formula will show the name, and if you click on it, it will take you to the table on Sheet2. A dialogbox and a macro will also work, but see if this work for you first.

Hi Eric,
Thank you for the response. Can the hyperlink function work across two worksheets? If I am reading this correctly, the description and the number would be on the same worksheet. Could the number on worksheet 1 be used to match on the second worksheet?

Best,
Lali

 

[Lali2023]

Instead of that, would this work for you?
Just double-click on the value in Worksheet 1 and be taken to the relevant value in Worksheet 2?
If so, you could try a Worksheet_BeforeDoubleClick event code like this.
I have assumed that the values of interest that you would double-click on in Worksheet 1 are in column A and the values to be searched for in Worksheet 2 are also in column A.
To implement ..
1. Right click the 'Worksheet 1' sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim rFound As Range
 
  If Target.Column = 1 And Len(Target.Value) > 0 Then
    Cancel = True
    Set rFound = Sheets("Worksheet 2").Columns("A").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If rFound Is Nothing Then
      MsgBox Target.Value & " not found"
    Else
      Application.Goto Reference:=rFound, Scroll:=True
    End If
  End If
End Sub

Hi Peter,

The code worked in bringing in the description. Ctrl-z takes out the description to revert it to what it was beforehand. Can the code be extended to double-click to erase the first half of the code had linked? Thank you.

Best,
Lali

 

[Eric W]

Hi Eric,
Thank you for the response. Can the hyperlink function work across two worksheets? If I am reading this correctly, the description and the number would be on the same worksheet. Could the number on worksheet 1 be used to match on the second worksheet?

Best,
Lali

The example I showed is showing a lookup/hyperlink across 2 worksheets. The first mini-sheet with the table was on Sheet2, and the HYPERLINK formula is on Sheet1. If you're asking if the formula can work across two workbooks, then the answer is still yes, but you need to fully qualify the ranges with the workbook name (3 places).

 

[Lali2023]

Instead of that, would this work for you?
Just double-click on the value in Worksheet 1 and be taken to the relevant value in Worksheet 2?
If so, you could try a Worksheet_BeforeDoubleClick event code like this.
I have assumed that the values of interest that you would double-click on in Worksheet 1 are in column A and the values to be searched for in Worksheet 2 are also in column A.
To implement ..
1. Right click the 'Worksheet 1' sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim rFound As Range
 
  If Target.Column = 1 And Len(Target.Value) > 0 Then
    Cancel = True
    Set rFound = Sheets("Worksheet 2").Columns("A").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If rFound Is Nothing Then
      MsgBox Target.Value & " not found"
    Else
      Application.Goto Reference:=rFound, Scroll:=True
    End If
  End If
End Sub

Hi Peter,
I deleted the code from the VBA prompt for one worksheet and the code is still working upon double-clicking. What can I do to make sure that once it is deleted from the VBA prompt the code will not still work in the worksheet?


Best,
Lali

 

[Peter_SSs]

Ctrl-z takes out the description to revert it to what it was beforehand.

What 'description' where? Your request was to be taken to a particular place in Worksheet 2. That is what my code does.

Can the code be extended to double-click to erase the first half of the code had linked?

I don't know what this means. What 'code' is to be erased?

What can I do to make sure that once it is deleted from the VBA prompt the code will not still work in the worksheet?

I still don't know what is being deleted and how.

Can you give some specific examples like you did in post 1?

 

[Lali2023]

What 'description' where? Your request was to be taken to a particular place in Worksheet 2. That is what my code does.


I don't know what this means. What 'code' is to be erased?


I still don't know what is being deleted and how.

Can you give some specific examples like you did in post 1?

Hi Peter,

I had opened up the VBA prompt for the specific worksheet and deleted the code:

However, in that worksheet, I double-click and the prompt appears as if it is still active and the previous code you had suggested still works.

I tried looking to see why that is but could not find anything that stood out. Is there another place I should look?

Best,
Lali

 

[Peter_SSs]

Double-click the ThisWorkbook module and see if there is any code in there.