none
VBA - Active X ComboBox - Go To Cell Location On Selection RRS feed

  • Question

  •  

    Hi friends,

     

    :)

    I have made an active X - combobox

    I have filled it with the values from a data sheet.

    When I select a value I would like to go there

    Private Sub ComboBox1_Click()
    Dim oCell As Range
    For Each oCell In Range("A2:A20") ' < In worksheet called Data
    
    If oCell.Value = ComboBox1.Value Then
    oCell.Select
    
    Application.Goto Sheets("ComboBox").Range("oCell")
    
    
    Exit Sub
    End If
    Next
    End Sub

    am i on the right track, as I can't get my combo to work

    thank you for any ideas


    Cheers Dan :)

    Saturday, November 26, 2016 11:27 PM

Answers

  • Try this:

    Private Sub ComboBox1_Click()
        On Error Resume Next
        Application.Goto Range(ComboBox1.Value)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Sunday, November 27, 2016 11:47 AM
    Sunday, November 27, 2016 11:05 AM

All replies

  • Try this:

    Private Sub ComboBox1_Click()
        Dim oCell As Range
        For Each oCell In Worksheets("Data").Range("A2:A20")
            If oCell.Value = ComboBox1.Value Then
                Application.Goto oCell
                Exit Sub
            End If
        Next oCell
    End Sub

    or, a bit more efficient:

    Private Sub ComboBox1_Click()
        Dim oCell As Range
        Set oCell = Worksheets("Data").Range("A2:A20").Find(What:=ComboBox1.Value, LookAt:=xlWhole)
        If Not oCell Is Nothing Then
            Application.Goto oCell
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 26, 2016 11:58 PM
  • Hello Hans,

     

    Nice to see you  - on this Saturday!

     

    Im afraid I think I got my wires crossed again.

     

    Your code works nicely.

     

    It’s a bit confusing you see.

     

    I have put the combobox in worksheet A

    Now I will Select AA1 from the combobox - Go To AA1 in the current worksheet A.

     

    But I hid the locations in a worksheet called Data

     

     
    
    Private Sub ComboBox1_Click()
    
        Dim oCell As String
    
        For Each oCell In Worksheets("Data").Range("A2:A20")
    
            If oCell.Value = ComboBox1.Value Then
    
               
    
                'Application.Goto oCell
    
                Application.Goto Sheets("A").Range(oCell)
    
               
    
               
    
                Exit Sub
    
            End If
    
        Next oCell
    
    End Sub
    
     


     

    I hope its not too confusing, I had a good go at it today - but I just couldn’t get this last little bit.


    Cheers Dan :)

    Sunday, November 27, 2016 12:25 AM
  • Try this:

    Private Sub ComboBox1_Click()
        On Error Resume Next
        Application.Goto Range(ComboBox1.Value)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Sunday, November 27, 2016 11:47 AM
    Sunday, November 27, 2016 11:05 AM
  •  

    Hello Hans,

     

    How did you know this 1 liner  - it did the trick!

     

    Thank you very much for helping out a lot - I appreciate it, as I always spend a lot of time before I ask for help, but I still couldn’t get it to work and well it’s the weekend, no rest for the wicked, thanks to 1 malfunctioning combobox🙁

     

    But it's all solved now  - untill excel gives me something else to worry about!

     

    Thanks so much and have a fantastic rest of weekend  -

    🙂


    Cheers Dan :)

    Sunday, November 27, 2016 11:47 AM