Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Conjurer NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    222
    Articles
    0
    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

  3. #3

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

    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


    Quote Originally Posted by NoS View Post
    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

  4. #4
    Conjurer NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    222
    Articles
    0
    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

  5. #5

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




    Quote Originally Posted by NoS View Post
    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
    Attached Files Attached Files

  6. #6
    Conjurer NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    222
    Articles
    0
    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.

  7. #7
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    162
    Articles
    0


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Circumference of a circle = 2πr²



    ²the circle's radius

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •