[SOLVED] Edit worksheet data thru userform

LoveCandle · #1 07-01-2009, 07:22 AM

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** · #2 07-01-2009, 01:37 PM

Take a look at the DataBaseForm example here

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

LoveCandle · #3 07-01-2009, 04:14 PM

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.

Code:

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

**royUK** · #4 07-02-2009, 02:33 AM

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

LoveCandle · #5 07-02-2009, 05:56 AM

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** · #6 07-02-2009, 06:02 AM

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 · #7 07-02-2009, 06:56 PM

Hi,

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

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

Code:

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

LoveCandle · #8 07-03-2009, 04:49 PM

Hi,

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

Thanks,

**royUK** · #9 07-04-2009, 03:43 AM

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

LoveCandle · #10 07-04-2009, 07:22 AM

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** · #11 07-04-2009, 08:00 AM

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 · #12 07-04-2009, 08:54 AM

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** · #13 07-04-2009, 09:15 AM

Amend the Edit Button

Code:

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 · #14 07-04-2009, 09:40 AM

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** · #15 07-04-2009, 10:32 AM

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.

Welcome to the Excel Forum

Forum Statistics