Excel "goto" command 1

[KOKE]

I am creating a form that uses a list box (sorted alphabetically) for users to make a selection.

How can I use the list box selection to go to the corresponding name on a different worksheet, where the names are sorted in a different order.

ie: "Beta" may be number 2 in the list box, but is located on sheet2 at cell A7. I want to be able to goto Sheet2, cell A7 if Beta is selected on the list box.

Thanks in advance for your help!

 

[LoNeRaVeR]

KOKE:

The following will work using the .Find Method. Change sRange to the range for your values and change the names of UserForm1 and ComboBox1 to the names in your form.

Private Sub ComboBox1_Change()
Dim oValue as Object
Dim sRange As String
sRange = "A1:A10"
With Worksheets(2).Range(sRange)
Set oValue = _
.Find(UserForm1.ComboBox1.Value, _
LookIn:=xlValues)
If Not oValue Is Nothing Then
Range(oValue.Address).Select
End If
End With
End Sub

I hope that helps. Regards,

LoNeRaVeR

 

[KOKE]

Thank-you LoNeRaVeR,

Please bear with me, I'm mature in age but an infant in programming!

I tried your suggestion; however, I received a "Can't Execute In Break Mode" error.

I understand the sRange, what is UserForm1 and how do I name it, and if I only have one ComboBox is it not named ComboBox1 or how do I check the name?

Thanks for your patience!
KOKE

 

[LoNeRaVeR]

KOKE:

Is your form inside a spreadsheet or was it created in VBA? The code that I provided uses a VBA form. We'll have to take a different approach if your form is inside a spreadsheet. Regards,

LoNeRaVeR

 

[KOKE]

This is an Excel spreadsheet that I am creating a form for users to access data comparisons.

Koke

 

[LoNeRaVeR]

KOKE:

If the ComboBox is inside a spreadsheet then you will have to set it up to first capture the item selected in the ComboBox and then find it on the second sheet.

In my example I set up a list of 10 items in Sheet1 in the Range A1:A10. The ComboBox is likewise on Sheet1. The set the ComboBox Input Range to $A$1:$A$10 and the Cell link to $B$1. The second list is on Sheet2 in a random order and is in the Range A1:A10. I assigned the following Macro to the ComboBox.

Sub ComboBoxSelect()
Dim oValue As Object
Dim sRange As String
Dim sValue As String
sRange = "A1:A10"
sValue = Cells(Range("B1&quot.Value, 1).Value
With Worksheets(2).Range(sRange)
Set oValue = _
.Find(sValue, _
LookIn:=xlValues)
If Not oValue Is Nothing Then
Sheets(2).Select
Range(oValue.Address).Select
End If
End With
End Sub

A spreadsheet ComboBox returns the number of the item in the list to the Cell Link address. The Macro takes that number using the Cells property to find the value of the item selected in the ComboBox list. This will need to be adjusted if the list doesn't start in Row 1. It then uses the .Find method to locate the item within the Range set in sRange on Sheet2. Please let me know what questions you have. Regards,

LoNeRaVeR

 

[KOKE]

Thank you LoNeRaVeR, works like a charm!!

KOKE

 

[LoNeRaVeR]

KOKE:

You're so welcome. I'm glad I could help! s-) Regards,

LoNeRaVeR