VB coding for using excel userform to edit and overwrite an existing database

[raseen20]

Dear All,

I've tried to make 2 userforms for a succession plan office project.

Userform1 (Data Entry)
1st part of the form extracts data from the "master database" sheet.
2nd part of the form is used to enter data into the "succession database" sheet.
This userform1 works perfect.

Userform2 (Data Edit)
This form is used to first extract and show the data entered into the "succession databse" sheet. The information is shown in textboxes and I expect the changes made in those textboxes will overwrite the existing initially entered information in the "succession databse"; but that's not happening.
I also wanted to have a combobox with just the employee's name to filter/dropdown and auto-retrieve the rest of the information but couldn't make anything happen so opted for the listbox.
The template is attached herewith with one hypothetical row of information. I need help on the working of UserForm2.

PLEASE HELP ME!

[NoS]

raseen20,

Your info from userform2 isn't being written because your RowCount variable of the update button always remains empty.

Put something like RowCount = 3 at the top of the update record procedure and you'll see the info is written. Make sure you have the correct sheet active at the time.

Personally, I would use the match function to find the actual row number in the AB column for the employee's number and use offset to both populate the form and write info back to the worksheet.

Hope this is of some assistance
NoS

[raseen20]

Dear NoS,

Thank you so much for the reply. I tried the rowcount option but all that it does is create a new row (if rowcount = 1) or make no changes (if rowcount = 0). However, I wanted the data to be replaced and changed in the same row
Please note that I am a novice in excel coding and don't really have any expertise on putting in a match function.
Would you be kind enough to guide me further please?

Thanks
Raseen

raseen20,

Your info from userform2 isn't being written because your RowCount variable of the update button always remains empty.

Put something like RowCount = 3 at the top of the update record procedure and you'll see the info is written. Make sure you have the correct sheet active at the time.

Personally, I would use the match function to find the actual row number in the AB column for the employee's number and use offset to both populate the form and write info back to the worksheet.

Hope this is of some assistance
NoS

[NoS]

raseen20, must confess to never using list boxes so don't know about dealing with them directly.

This is what I would do to update the data

Sub CommandButton1_Click()
' To write edited info of userform2 to Sheets("Succession Database")
    Dim LastRow As Long
    Dim ABnum As Double
    Dim ABrng As Range
    Dim WriteRow As Long

' Make sure we're on the right sheet
Sheets("Succession Database").Select
    With ActiveSheet
' Get the last row used so can set up the search range
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
' Set the range to search for the AB number
        Set ABrng = .Range("A1:A" & LastRow)
' Get the AB number from what is selected on userform2
    ABnum = txt17.Value
' Get the row of sheet for this AB number
    WriteRow = Application.Match(ABnum, ABrng, 0)

' Make this AB number the active cell
        Cells(WriteRow, 1).Select
' Write in all the editable stuff, don't bother with the non-editable things
        With ActiveCell
            .Offset(0, 7).Value = txt29.Value
            .Offset(0, 8).Value = txt30.Value
            'etc.
            'etc.
        End With
' Put the cursor in upper left corner
    Cells(1, 1).Select
    End With
' Unload the userform
    Unload Me
End Sub

Hope this helps

[raseen20]

Dear NOS,

Once again a big thanks for taking time out and offering me a solution. However, the code is not working for some reason . The button seems to run but changes do not come through. I'm attaching the revised document (with the codes that you had proposed). Can you PLEASE have a look as to what has gone wrong? As I said earlier, I'm a cmoplete novice and badly need your help to make this stand.
Thank you for your kind support.

Regards,
Raseen

raseen20, must confess to never using list boxes so don't know about dealing with them directly.

This is what I would do to update the data

Sub CommandButton1_Click()
' To write edited info of userform2 to Sheets("Succession Database")
    Dim LastRow As Long
    Dim ABnum As Double
    Dim ABrng As Range
    Dim WriteRow As Long

' Make sure we're on the right sheet
Sheets("Succession Database").Select
    With ActiveSheet
' Get the last row used so can set up the search range
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
' Set the range to search for the AB number
        Set ABrng = .Range("A1:A" & LastRow)
' Get the AB number from what is selected on userform2
    ABnum = txt17.Value
' Get the row of sheet for this AB number
    WriteRow = Application.Match(ABnum, ABrng, 0)

' Make this AB number the active cell
        Cells(WriteRow, 1).Select
' Write in all the editable stuff, don't bother with the non-editable things
        With ActiveCell
            .Offset(0, 7).Value = txt29.Value
            .Offset(0, 8).Value = txt30.Value
            'etc.
            'etc.
        End With
' Put the cursor in upper left corner
    Cells(1, 1).Select
    End With
' Unload the userform
    Unload Me
End Sub

Hope this helps

[NoS]

You must have missed this comment in the code

' Write in all the editable stuff, don't bother with the non-editable things

I comment out the non-editable things and it works for me.

UserForm2 loads 13 non-editable text boxes and 9 editable text boxes. None of the non-editable need to be written back to your table because it is not possible for these to be changed from UserForm2. The 9 editable ones are all that can be changed and therefore the only ones needing to be written back to the table.

Writing to the columns used by the list box seems to be the issue, don't know why. Maybe someone else can explain why, I don't really know how list boxes operate, I've never used them.

Hope you get things sorted out
NoS



PS: count how many things are being written to the table.

[JoePublic]

The listbox has been bound to the worksheet (using its Rowsource property) so as soon as you overwrite the data that it's using, you change its source data and thereby trigger its click event. As a rule I generally recommend not binding controls to ranges like that - you're using code anyway so you may as well populate the control in code too.