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

    Use Ranges on User Form (2003 SP2)

    Good evening

    I would like to use a 'userform' to add staff to a rota that I have, however I would like to enter them departmentally. I have 2 ranges Departments and StaffNames which are listed as per the example below. Is it possible on a UserForm to select a department from the Department range and then add a record to the next blank row in the appropriate StaffNames Column?

    Cheers

    Steve
    Cheers

    Steve

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

  2. #2
    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: Use Ranges on User Form (2003 SP2)

    I don't see an example and I am not sure exactly what you want to do...

    Steve

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

    Re: Use Ranges on User Form (2003 SP2)

    Sorry Steve

    I was rushing around and forgot to do the attachment

    Here it is

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

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

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

    Re: Use Ranges on User Form (2003 SP2)

    Your screenshot doesn't seem to correspond with the description in your first post. Could you attempt to explain more clearly what you want?

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use Ranges on User Form (2003 SP2)

    I suspect that youir objective will be easier to achieve with the following layout.
    Attached Images Attached Images
    Regards
    Don

  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: Use Ranges on User Form (2003 SP2)

    Does Debra Dagliesh's Data Validation -- Create Dependent Lists do what you want?


    Steve

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

    Re: Use Ranges on User Form (2003 SP2)

    Thanks to all as usual for your input

    I need the layout on the WS to remain as it is because it would be extremely difficult to rewrite everything that is already going on within it. At the moment I unlock the WS and add staff names manually into whichever department they start to work for. I was just trying to streamline things a little and thought that if I had a button that could call a user form 'New Employee' with 1 combo DEPARTMENT and 1 text box NAME and then somehow code it to say that, for example, If Customer Services was picked in the DEPARTMENT combo (department range ) the information in the text box would be input into the first blank row for that department in the staffname range.

    I hope that makes a little more sense but please don't waste any time if it is not sensible as I can continue as I am without to much trouble.

    Thanks all

    Cheers

    Steve
    Cheers

    Steve

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

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

    Re: Use Ranges on User Form (2003 SP2)

    You should really consider using the approach suggested by Don and Steve. With the current setup, you'll quickly run out of space, and it makes the coding unnecessarily difficult.

  9. #9
    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: Use Ranges on User Form (2003 SP2)

    Is this what you are after?

    Steve
    Attached Files Attached Files

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

    Re: Use Ranges on User Form (2003 SP2)

    Hi Steve

    That looks great, I will try and implement it into my workbook now

    Thanks for all your efforts

    Cheers

    Steve
    Cheers

    Steve

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

  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: Use Ranges on User Form (2003 SP2)

    Hi Steve

    Thank you for all of your help with this user form and code. I have for about 2 months trying to adapt it but have failed in everythin that I have tried, peraps what I am trying to acieve is not possible.

    At the moment all values are added to Column 1, what I would like to try and do is to have Column 1 - 8 showing the deparmental header, for example Column 1 = Directors, Column 2 = Sales, Colum 3 = Operations etc.

    I have put the department names in each of the column but when I run the orm it gives me the msgbox "Dept name not in list", the de###### tells me that it is this part of the code

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">If Cells(lRow, 1).Offset(1, 1) <> "" Then</span hi>
    lRow = Cells(lRow, 1).Offset(0, 1).End(xlDown).Row
    End If
    lRow = lRow + 1
    Range(Cells(lRow, 1), Cells(lRow, 2)).Insert _
    Shift:=xlDown
    Cells(lRow, 2) = sName

    A little further up in the code I changed these lines from Match(sDept, Columns(1), 0), to

    lRow = Application.WorksheetFunction. _
    Match(sDept, Columns(1 - 8), 0)

    To reflect that I wanted it to look at those 8 columns for the match.

    Can you offer any further assistance please

    Cheers

    Steve
    Cheers

    Steve

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

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

    Re: Use Ranges on User Form (2003 SP2)

    Columns(1 - 8) means Columns(-7) which makes no sense.

    It would help if you attached your workbook so that we don't have to guess what you're working with.

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

    Re: Use Ranges on User Form (2003 SP2)

    Hi Hans

    In all honesty it has not changed much since Steve posted it, everytime I close it I do not save as none of my changes make a difference.

    I have attached a copy where I have put in the column header names in the hope that you can see what I am trying to do.

    Thank you

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

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

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

    Re: Use Ranges on User Form (2003 SP2)

    This is a COMPLETELY different setup than the original one, so the code needs to be different too.

    Private Sub cmdGo_Click()
    Dim sDept As String
    Dim sName As String
    Dim lRow As Long
    Dim lCol As Long
    sDept = Me.cboDept.Value
    sName = Me.tbName.Value
    On Error Resume Next
    lCol = Application.WorksheetFunction.Match(sDept, Range("A7:H7"), 0)
    On Error GoTo 0
    If lCol = 0 Then
    MsgBox "Dept Name not in list"
    Me.Hide
    End If
    If Cells(8, lCol) <> "" Then
    lRow = Cells(7, lCol).End(xlDown).Row + 1
    Else
    lRow = 8
    End If
    Cells(lRow, lCol) = sName
    Unload Me
    End Sub

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

    Re: Use Ranges on User Form (2003 SP2)

    Hi Hans

    Thanks for your input and the new code. I have tried to impliment this in the attached WB but I am still getting the "Dept not in list" message. If you have a moment would you mind telling me what I have done wrong this time

    Thanks

    Steve
    Attached Files Attached Files
    Cheers

    Steve

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

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
  •