Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Insert Userform (Excel 97)

    Some time ago I received a lot of assistance from the lounge with my "staff leave management" spreadsheet. At the time I did not know how to create a userform. Now I have created one, but am not sure how to include it to replace the input boxes.

    The "Add" button runs the old routine and the "new Add" contains the userform. When I run the new userform it enters the new staff member in the "staff" worksheet but it does not insert the person into the monthly sheets and subsequently run the sort routine.

    Any assistance is always appreciated.

    Kerry

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Userform (Excel 97)

    Hi Kerry,
    I had a look at the user form, and see that the code behind the OK button only adds the details to the Staff sheet. It needs to be modified sothat it loops through the sheets and adds the details to these sheets to. This is what your old ADD button does. I will modify the code overnight, as there is a bit to do to it. If no-one has posted a quicker reply, I'll hopefully be able to give you something tomorrow!
    Regards,
    Rudi

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Insert Userform (Excel 97)

    I am glad you understood my question Rudi. Look forward to hearing from you tomorrow. Midnight here - so I have to pack it in for the night.

    Thankyou.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Userform (Excel 97)

    Hi Kerry,
    Try this revised version...
    It took less time than I expected to correct, but try it out and see if this is what you were after!
    Regards,
    Rudi

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Userform (Excel 97)

    Does changing the click event routine for the OK command button on the form to this fix the problem:

    <pre>Private Sub cmdOK_Click()
    Dim oNextCell As Range, oSheet As Worksheet, oCell As Range
    Application.ScreenUpdating = False
    Set oNextCell = Worksheets("Staff").Range("A65536").End(xlUp).Offs et(1, 0)
    oNextCell.Value = txtLName.Value
    oNextCell.Offset(0, 1) = txtFName.Value
    oNextCell.Offset(0, 3) = txtPosition.Value
    oNextCell.Offset(0, 4) = cboLocation.Value
    oNextCell.Offset(0, 5) = cboSector.Value
    For Each oSheet In Worksheets
    If oSheet.Name <> "Public Holidays" And oSheet.Name <> "Instructions" And oSheet.Name <> "Staff" Then
    oSheet.Unprotect
    If oSheet.Range("A5").Value = "" Then
    Set oCell = oSheet.Range("A4")
    Else
    Set oCell = oSheet.Range("A65536").End(xlUp)
    End If
    oCell.Offset(1, 0).Value = txtLName.Value
    oCell.Offset(1, 1).Value = txtFName.Value
    oSheet.Protect
    End If
    Next oSheet
    SortSheets
    Unload Me
    Application.ScreenUpdating = True
    End Sub
    </pre>


    I also had to add a missing formula to column C on the Staff worksheet.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Insert Userform (Excel 97)

    Thankyou Rudi and Legare! It just goes to show you can do the same thing many ways. Both solutions appear to work.

    I have another question relating to combo boxes but I will make it a separate post.

    Thanks again.

  7. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Insert Userform (Excel 97)

    Sorry - I am back with my userform problem. I want to use the same userform to Edit records. Editing currently works from input boxes. I thought it would be simple to add in my EditStaff Userform, but I am struggling with this.

    I have attached the most up to date version of the spreadsheet.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Userform (Excel 97)

    Is the attached close?
    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Insert Userform (Excel 97)

    Legare it is perfect!!

    I have only one more thing and it will be complete. I am concerned that on the "Staff" Sheet I cannot protect the cells that are filled by the userform. If incell editing takes place the info is not transferred into the monthly sheets. If I protect the cells the userform wont work.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Userform (Excel 97)

    Sorry to butt in again, but if I understand you kerry - all that needs to be done is to unprotect the sheet when the user form attempts to add the data to the Staff sheet. The staff sheet must have sheet protection active, and then the protect method must be added to the code under the OK button, as with the red text in the example below!

    <pre>Private Sub cmdOK_Click()
    Dim oNextCell As Range, oSheet As Worksheet, oCell As Range
    Application.ScreenUpdating = False
    <font color=red>ActiveSheet.Unprotect "Pass"</font color=red>
    Set oNextCell = Worksheets("Staff").Range("A65536").End(xlUp).Offs et(1, 0)
    oNextCell.Value = txtLName.Value
    oNextCell.Offset(0, 1) = txtFName.Value
    oNextCell.Offset(0, 3) = cboPosition.Value
    oNextCell.Offset(0, 4) = cboLocation.Value
    oNextCell.Offset(0, 5) = cboSector.Value
    For Each oSheet In Worksheets
    If oSheet.Name <> "Instructions" And oSheet.Name <> "Staff" And oSheet.Name <> "SetUp" Then
    oSheet.Unprotect
    If oSheet.Range("A5").Value = "" Then
    Set oCell = oSheet.Range("A4")
    Else
    Set oCell = oSheet.Range("A65536").End(xlUp)
    End If
    oCell.Offset(1, 0).Value = txtLName.Value
    oCell.Offset(1, 1).Value = txtFName.Value
    oSheet.Protect
    End If
    Next oSheet
    SortSheets
    Unload Me
    <font color=red>ActiveSheet.Protect "Pass"</font color=red>
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Regards,
    Rudi

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Insert Userform (Excel 97)

    Hey Rudi butt in anytime.

    Thanks - I included this and it works. I also did the same in the edit userform. I had tried something similar but I wasnt putting it in right place.

    Kerry

Posting Permissions

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