Edit worksheet data thru userform

[LoveCandle]

Hi everyone,

Depending on the attached workbook,

How can I edit the employee's Name and badge number thru a userform?

What should we depend on to save the employee's name before changing it, in order to use it as a find key?

Thank you,

[royUK]

Take a look at the DataBaseForm example here

http://www.excel-it.com/vba_examples.htm

[LoveCandle]

Hi,

Thank you royUK for your useful link you posted,

I applied amendment code in the mentioned link database form example, but it didn't work with me.

Private Sub CommandButton1_Click()

If MyRange Is Nothing Then GoTo skip

For Each c In MyRange
    If r = 0 Then c.Select
    r = r - 1
Next c

skip:
    Set c = ActiveCell
    c.Value = ComboBox1
    c.Offset(0, 1).Value = TextBox1
    Application.ScreenUpdating = True
    On Error GoTo 0
    
End Sub

No errors appear, but the updates are inserted in the first row of the range only,, I don't know why!!!

Can you please find me a solution for that,,

[royUK]

That code isn't from the example. What doesn't work?

[LoveCandle]

The code is derived from the example, the only difference is that I removed some lines, which I don't need in my code.

But, the problem now is that when I edit for example record No. 4 of my range .. the changes are inserted in record number 1 not 4 and so on.

I hope that you can find the reason behind that and solve the problem.

Thanks,

[royUK]

You've set the code to work on the activecell, no one selected by the userform - that's how the code in the example works

[LoveCandle]

Hi,

Until now I coundn't understand how the code in example works exactly,,

What does (r) in the following part refer to?

For Each c In MyRange
    If r = 0 Then c.Select
    r = r - 1
Next c

It will be better for me if you correct my code's mistakes and post it?

Thank you,

[LoveCandle]

Hi,

Can you please help me in this query, I really need the answer ASAP,

Thanks,

[royUK]

Reading the original question, I'm not sure what you mean. Why do you want to save the name before editing it?

[LoveCandle]

Hi,

I believe that we have to save the original name in order for excel to use it as a find key for recognizing the concerned record that should be found and paste the new changes on it.

I hope that my idea is clear now,

Even in the Database example, when I tried to run the Amendment code Directly (Without running find code before it), the same problem appeared (the changes are inserted in record number 1 only) ..

Therefore, I think that there is a relationship between The example find code and amendment code .. but I don't know what is it exactly??!!!

Anyhow, if my belief is incorrect, I would be so thankful for you if you help me writing the suitable code for edit database records without pasting someone's data on another one's.

Moreover, it will nice from you if you can apply that on my previous attached workbook,

Thank you,

[royUK]

The code in your first post does that - select a name from the combobox, edit the name or amend the textbox. Click Edit & the changes are recorded.

[LoveCandle]

Yes, the workbook in my first post does record the changes perfectly if we change the TexBox1 value only ,,

But once we change ComboBox1 value, the code returns an error, because the code looks for ComboBox1 value and doesn't find it in the database because it has been changed and the new value is not available in the database.

So, I think the solution is that we have to save ComboBox1 original value somewhere else, in order to use it for database search.

I hope that you got my idea now,

Thanks,

[royUK]

Amend the Edit Button

Private Sub CommandButton1_Click()

    If MyRange Is Nothing Then GoTo skip

    For Each c In MyRange
        If r = 0 Then c.Select
        r = r - 1
    Next c

skip:
    Set c = ActiveCell
    c.Value = ComboBox1
    c.Offset(0, 1).Value = TextBox1
    'refresh list
    Unload Me
    UserForm1.Show
    Application.ScreenUpdating = True
    On Error GoTo 0

End Sub

[LoveCandle]

Sorry royUK, there is no difference between your code's result and mine,

The problem is still existing .. the changes are still recorded in the first record.

Anyhow, I found a solution but it is not that much practical.

My trick solution is saving combobox value in a label (the label will be hidden), and the use the label value as a database search key,

See my attachment.

Thank you,

[royUK]

I'm not at all sure what you mean, when you edit the list the userform is "refreshed" and the combobox updated with the changes.

[LoveCandle]

Therefore, I think that there is a relationship between The example find code and amendment code .. but I don't know what is it exactly??!!!

Hi,

As I expected, There is a relationship between The searching Code and Amendment Code, which complete each other.

Searching Code's job is looking for the concerned record (selected by ComboBox) and stops on it by (Using Select property).

Then, Amendment code pastes the new changes to the recoded selected by Searching code.

Anyhow, I summarized the code to fit my need.

Private Sub ComboBox1_Change()
    
On Error GoTo 1

With MyRange.Find(ComboBox1)
   .Select
    TextBox1 = .Offset(0, 1)
End With

1 End Sub

Private Sub CommandButton1_Click()
  
With ActiveCell
    .Value = ComboBox1
    .Offset(0, 1) = TextBox1
End With
Unload Me

End Sub

The only problem with this code is that it works only on the database sheet .. but when we run the code from another sheet, it doesn't work at all,

Therefore, for me the only solution for that is the trick I mentioned in My post # 14.

Thanks for all,

[Krishnakumar]

Hi,

Replace all your code with the following

Public MyRange As Range
Public ws As Worksheet
Public Idx As Long
Private Sub ComboBox1_Change()
Idx = Me.ComboBox1.ListIndex + 1
If Idx = 0 Then
    Me.TextBox1.Value = ""
Else
    Me.TextBox1.Value = Application.Index(MyRange.Value, Idx, 2)
End If
End Sub

Private Sub CommandButton1_Click()
With MyRange.Cells(1, 1)
    If Idx = 0 Then
        Idx = Me.ComboBox1.ListCount
        .Offset(Idx).Value = Me.ComboBox1
        .Offset(Idx, 1).Value = Me.TextBox1
        Set MyRange = ws.Range("A2:B" & ws.Range("a" & Rows.Count).End(xlUp).Row)
        Me.ComboBox1.List = Application.Index(MyRange.Value, 0, 1)
    Else
        .Offset(Idx - 1).Value = Me.ComboBox1
        .Offset(Idx - 1, 1).Value = Me.TextBox1
    End If
End With
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Set ws = Sheets("Sheet1")
Set MyRange = ws.Range("A2:B" & ws.Range("a" & Rows.Count).End(xlUp).Row)
Me.ComboBox1.List = Application.Index(MyRange.Value, 0, 1)
End Sub

HTH

[LoveCandle]

Hi,

Thank you Krishnakumar for your reply,

Your codes are excellent, but I am so sorry to tell you that the problem is still existing

What is new in your codes is the following,

If ComboBox1 value was changed, the new values are not recorded in the first row of the list like previous examples, but they are added to the worksheet list as a new record.

Anyhow, I think the best and the most satisfactory solution for this case is creating a new Textbox and use the first one for employee name and the other one for badge number and ComboBox1 value will stay as it is without changing.

Thank you all,

[Krishnakumar]

Hi,

The problem is still existing .. the changes are still recorded in the first record.

the new values are not recorded in the first row of the list like previous examples, ..

I'm not sure what you are after.

Let us know what exactly you need.