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

raseen20

New member
Joined
Mar 29, 2013
Messages
3
Reaction score
0
Points
0
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!
 

Attachments

  • Succession Plan 2.xlsm
    64.8 KB · Views: 2,851

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

New member
Joined
Mar 29, 2013
Messages
3
Reaction score
0
Points
0
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 :sad:
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

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

Code:
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

New member
Joined
Mar 29, 2013
Messages
3
Reaction score
0
Points
0
The code doesn't work for data overwrite via userform

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 :frown:. 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

Code:
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
 

Attachments

  • Succession Plan 2.xlsm
    66.2 KB · Views: 2,127

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
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.
 
Top