Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating Cells with UserForm (2003 SP2)

    Good Morning

    Thanks to the usual help from the forum I now have my first VBA UserForm along with a button that opens in on my ws and the 2 combos on the user form are correctly bringing up the required information. My question here is how do I move the information from the UserForm to the cells I wish to populate on another ws.

    The UserForm has 4 fields EmployeeName (cbo), DateFrom (txt), DateTo (txt) and LeaveType (cbo) the fields to be populated start in ws 2008 A,B,C & D6 and I would like to retain the information in those cells and then next time an entry is made it would complete A,B,C & D7 etc. until row 780.

    Any ideas please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    I found this Create User Forms in Microsoft Excel tutorial very helpful!

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    You could place two command buttons on the form: cmdOK with caption 'OK' or 'Save' and cmdCancel with caption 'Cancel'.

    Double click cmdOK to create code for it:

    Private Sub cmdOK_Click()
    Dim r As Long
    With Worksheets("2008")
    r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    .Cells(r, 1) = Me.cboEmployeeName
    .Cells(r, 2) = Me.txtDateFrom
    .Cells(r, 3) = Me.txtDateTo
    .Cells(r, 4) = Me.cboLeaveType
    End With
    Unload Me
    End Sub

    Double click cmdCancel to create code for it:

    Private Sub cmdCancel_Click()
    Unload Me
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Thanks Hans

    I already have 2 buttons Addleave and Close

    The AddLeave button currently has this code behind it

    Private Sub cmdAddLeave_Click()
    Dim Irow As Long
    Dim Iname As Long
    Dim ws As Worksheet
    Set ws = Worksheets("2008")

    'find first empty row in db

    Irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Iname = Me.cboName.ListIndex

    'Check for a name

    If Trim(Me.cboName.Value) = "" Then MsgBox "Please enter an emplyees name"
    Exit Sub


    'Copy the data to the database

    With ws
    .Cells(Irow, 1).Value = Me.cboName
    .Cells(Irow, 2).Value = Me.cboName.List(Iname, 1)
    .Cells(Irow, 3).Value = Me.txtDateFrom.Value
    .Cells(Irow, 4).Value = Me.txtDateTo.Value
    .Cells(Irow, 5).Value = Me.cboLeaveType.Value
    End With

    'Clear the data

    Me.cboName.Value = ""
    Me.txtDateFrom.Value = ""
    Me.txtDateTo.Value = ""
    Me.cmdAddLeave.Value = ""
    Me.cboName.SetFocus

    End Sub

    Can I incoperate your code within this. I still cannot see however how this would place the information into A6,B6,C6, and D6 (there are actually 65 columns in the ws A,B,C,D update these columns)

    Sorry to be a klutz but this is a whole new ball game for me.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Thanks for that Leif, I have bookmarked it for more study at the weekend

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    The University of Bristol's Custom Dialog Boxes is also a nice primer on userforms.

    The have a List of documents by category - Spreadsheets in which you might want to check out some of the other articles including a VBA primer (since you mentioned in other posts about being new to VBA).

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    I'm sorry, you've lost me. I have no idea what you mean by "I still cannot see however how this would place the information into A6,B6,C6, and D6 (there are actually 65 columns in the ws A,B,C,D update these columns)". I thought you wanted to enter new information below the existing information, why should it be entered in row 6?

    Note: in the message box, "emplyees" should be "employees".

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Your code will never add to the cells. The lines:

    If Trim(Me.cboName.Value) = "" Then MsgBox "Please enter an emplyees name"
    Exit Sub

    will put up a message if there is no name, but it ALWAYS does the Exit Sub (whether there is a name or not). Do you mean perhaps:

    If Trim(Me.cboName.Value) = "" Then
    MsgBox "Please enter an emplyees name"
    Exit Sub
    End if

    So that the Exit Sub is only run when there is no name?

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Thanks Steve

    I will have a read up on the W/E

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    I think Steve nailed the problem in his second reply.

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Editted to add the attachment

    Hi Hans

    Sorry to confuse you

    On my ws I have 4 columns as per the screen shot below, data entered into these cells (1st empty cells = A6,B6,C6,D6) update the information contained in cells G6:AK780 in accordance with the name.fromdate,todate and leavetype.

    I had intended that when information was entered into the UserForm and the AddLeave button was pressed the information would be transferred to those 1st 4 empt cells.

    I hope that makes a little more sense

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Hi Steve

    When I originally wrote this piece of code I did have End If but when I teasted the form by completing the 4 fields and pressing the AddLeave button I got a compile error that highlighted the End If statement, I found that by deleting it I could test the box and it worked (visually) although it did not update any information because, as far as I can see it does not know ehre to update it.

    So, should I put the End If back in and how do I resolve the compile error?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Try Steve's suggestion.

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Thanks Steve

    I did not realise that there should be a new line after Then (as per your example)

    If Trim(Me.cboName.Value) = "" Then
    MsgBox "Please enter an emplyees name"
    Exit Sub
    End if

    The source I was using seemed to suggest it was all on one line, I understand it a bit more now from your latter description

    When I press the AddLeave button it now shows calculating cells at the bottom of the screen and gets to 100% and then blanks the information in the UserForm but nothing gets updated in the 2008 ws, does this go back to Hans piece of code example and if so where abouts should it be placed?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating Cells with UserForm (2003 SP2)

    Could you post a stripped down copy (zipped if necessary) of your workbook?

Page 1 of 2 12 LastLast

Posting Permissions

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