PDA

View Full Version : VB coding for using excel userform to edit and overwrite an existing database



raseen20
2013-03-29, 05:46 PM
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
2013-03-29, 10:09 PM
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
2013-04-01, 08:12 AM
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
2013-04-01, 05:52 PM
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
2013-04-06, 06:16 PM
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


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
2013-04-07, 11:02 PM
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
2013-04-08, 07:55 AM
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.