Editing data from another sheet using Combobox?

[jmpatrick]

Happy Friday!

Today I'm trying to give users the ability to edit data on a sheet using a userform.

Here's a screen shot...

Here's my code:

Private Sub cmdUpdateGarageHandling_Click()
Dim EditGarageHandling_id As String
EditGarageHandling = Trim(EditGarageHandling.Text)
LastRow = Worksheets("GarageHandling").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("GarageHandling").Cells(i, 1).Value = EditGarageHandling Then
   Worksheets("GarageHandling").Cells(i, 1).Value = EditGarageHandling.Text
End If
Next
        
        Call cmdResetDeleteGarageHandling_Click
        
        MsgBox ("Garage Handling Successfully Updated!")

End Sub

The user would select data from the combobox, make changes and hit update to edit the data in sheet (GarageHandling).

The current code doesn't error out, but it doesn't edit anything either.

 

[jmpatrick]

Do you have any code in the initialise event that puts a value into the combo?

I have this:

    Set wsGarageHandling = ThisWorkbook.Worksheets("GarageHandling")
    EditGarageHandling.List = [GarageHandling!GarageHandling].Value
    EditGarageHandlingReturn.Visible = False
    LabelEditGH.Visible = False

 

[Fluff]

Ok, that should put the list of values back into the combo, so what exactly is not working?

 

[jmpatrick]

Ok, that should put the list of values back into the combo, so what exactly is not working?

When I edit the data and click Update, Call cmdResetDeleteGarageHandling_Click successfully clears the list box but not the combobox.

Private Sub cmdResetDeleteGarageHandling_Click()
        With Me
            .EditGarageHandling.Clear
            .EditGarageHandlingReturn.Value = ""
        End With
        UserForm_Initialize
        EditGarageHandling.SetFocus
        Me.cmdDeleteModel.Enabled = False
End Sub

 

[Fluff]

What do you mean by it doesn't clear the combo? That code is removing the list in the combo, but then you are putting the list back in on the initialise event.

 

[jmpatrick]

What do you mean by it doesn't clear the combo? That code is removing the list in the combo, but then you are putting the list back in on the initialise event.

Hope this helps:

Before...

Select...

Edit...

Click Update, but reset doesn't clear combobox. Original unedited data remains...

 

[Fluff]

I have no idea why that is happening. Assuming that combo is called EditGarageHandling then this line of code should clear everything from the list.

.EditGarageHandling.Clear

 

[jmpatrick]

I have no idea why that is happening. Assuming that combo is called EditGarageHandling then this line of code should clear everything from the list.

.EditGarageHandling.Clear

Here's the strange part: If I select data and hit reset without editing it clears fine.

 

[Fluff]

Try removing this line

EditGarageHandling = Trim(EditGarageHandling.Text)

 

[jmpatrick]

Try removing this line

EditGarageHandling = Trim(EditGarageHandling.Text)

That worked! What was I doing wrong there?

 

[Fluff]

You were changing the value of the combo, to a value that is not in the list.