Change ActiveCell based on ComboBox Selection

[imav]

I am working on a userform that has a combobox and a set of textboxes. When the user selects a unique value from from the combobox the textboxes are updated.

The code for this process is Below.

Private Sub UserForm_Initialize()
    Dim wsSheet As Worksheet
    Dim rngNext As Range
    Dim myRange As Range
    Set wsSheet = Sheets("RLATAM")
    With wsSheet
        .Select
        Set rngNext = .Range("A65536").End(xlUp)
    End With
    rngNext.Select
    Set myRange = Range("A5", rngNext)
    With ComboBox1
    For Each rngNext In myRange
        If rngNext <> "" Then .AddItem rngNext
    Next rngNext
End With
End Sub

What I am trying to get my userform to do is change the Activecell based on the selection in the combobox. All the values populating the combobox are in column A, If the value selected from the Combobox resides in cell A5 then A5 should also be the active cell.
What I am attempying to accomplish is, If the user edits the details in the userform, the edited details are written back to the correct cells on the worksheet.

Can anyone help me out with this?

 

[mikerickson]

I recommend that you not use ActiveCell or selection for this purpose. Selcting will slow things down.

The ComboBox1.ListIndex itself is equivialnt to setting the Active Cell.

Private Sub CommandButton1_Click()
    With ComboBox1
        If -1 < .ListIndex Then
            MsgBox "The record you are working on is on row " & .ListIndex + 5
            ActiveSheet.Cells(.ListIndex + 5, 1) = "new Column A data"
        End If
    End With
End Sub

 

[imav]

I recommend that you not use ActiveCell or selection for this purpose. Selcting will slow things down.

Thanks for the response mikerickson,

I understand that could be a potential problem however for the action I am attempting to create I still need my Active cell to change based on my combobox selection.

Is there anyway to do that?

 

[AlphaFrog]

Using the Listindex to select the cell in column A would work if you don't have any empty cells in between data in column A. If you did have empty cells, the combobx Listindex wouldn't match with the cell index.

This puts each cell address in the 2nd column of the combobox and hides that second column. Then when you select a value in the combobox, it uses the cell address in the hidden combobox column to select the cell.

Private Sub UserForm_Initialize()
    Dim rngNext As Range
    Dim myRange As Range
    With Sheets("RLATAM")
        Set myRange = .Range("A5", .Range("A65536").End(xlUp))
    End With
    With ComboBox1
        .ColumnCount = 2                'Two column combobox
        .ColumnWidths = .Width & ";0"   'Hide column 2
        For Each rngNext In myRange
            If rngNext <> "" Then
                .AddItem rngNext.Value                      'Column 1 has the cell value
                .List(.ListCount - 1, 1) = rngNext.Address  'Column 2 has the cell address
            End If
        Next rngNext
    End With
End Sub

Private Sub ComboBox1_Change()
    Sheets("RLATAM").Select
    With ComboBox1
        Range(.List(.ListIndex, 1)).Select      'Select the cell in column A
        
        'Read values from columns B and C to textboxes
        TextBox1.Value = Range(.List(.ListIndex, 1)).Offset(, 1)    'column B value to Textbox1
        TextBox2.Value = Range(.List(.ListIndex, 1)).Offset(, 2)    'column C value to Textbox2
        
    End With
End Sub

Private Sub CommandButton1_Click()
    'Write values to columns B and C
    With ComboBox1
        Range(.List(.ListIndex, 1)).Offset(, 1) = TextBox1.Value    'Textbox1 value to column B
        Range(.List(.ListIndex, 1)).Offset(, 2) = TextBox2.Value    'Textbox2 value to column C
    End With
End Sub

 

[imav]

This puts each cell address in the 2nd column of the combobox and hides that second column. Then when you select a value in the combobox, it uses the cell address in the hidden combobox column to select the cell.

AlphaFrog that works beautifully thankyou