+ Reply to Thread
Results 1 to 16 of 16

User Form with repetitive comboboxes

  1. #1
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    User Form with repetitive comboboxes

    I want to create a userform for attendance. I figured the easiest way to do it would be to use combo boxes with a dynamic named range, since all the combo boxes will be linked to the same "dynamic named range" I thought this would be easy to do, but I do not understand how to come up with the code.

    Also, I want it to return the name of the line and the zone number, and am not sure how I should format the userform, to be able to do this. I thought if I put each Line in a frame, I would be able to return the Line number( I may be wrong), and I am not sure how to do the zone, but they are always in the order shown?

    I am attaching a worksheet where I have a generic version of what my thoughts are, but if anyone has any suggestions it would be appreciated.
    Last edited by 00Able; 01-22-2011 at 03:09 PM.
    Providing Problems for Your Solutions
    STARS are my Punching Bag, You will be rewarded.

    In the rare event that I may help you, feel free to make me see STARS

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: User Form with repetitive comboboxes

    Hello 00Able,

    I am having a difficult time understanding what you are trying to do. Ninety ComboBoxes??? If you explain in more detail what you want, I am sure we can create a simpler solution.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: User Form with repetitive comboboxes

    There are 18 lines each line has 5 workers (one worker per zone as there are 5 zones on each line) .
    So I need to manually type each persons name in each zone for each line. I was hoping to come up with a user form to display this information in the "Attendance Log" as shown. There will be a total of 90 individual entries. I like the comboboxes, as when I start to type, it will finish what I am typing, to make it more efficient. I am not sure if there is another way to do it. Also I need to do this every day, usually takes me about 30 minutes a day. I am hoping for a better approach.
    As I have done in the past, I put the calendar form, in but, havent hooked it up, as I am not sure how I should do the other task easily....

    thanks in advance for any advice

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: User Form with repetitive comboboxes

    Hello 00Able,

    I see the Zones are in column "A" of "LookUpLists", which is empty, and the names are located in column "M". Why not reduce the number of controls a simply loop through the list and display each employee and associated information. You could then simply click a button to mark them as present or not.

  5. #5
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: User Form with repetitive comboboxes

    employees often switch from line to line and from zone to zone. I use this lookuplist for multiple userforms, that is why those fields are there, but unfortunately I am afraid it won't work for this instance...

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: User Form with repetitive comboboxes

    Hello 00Able,

    It would be best to provide detailed examples of the desired results and fill in the missing data on the worksheets. There is simply to much guess work at this point to suggest possible solutions.

  7. #7
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: User Form with repetitive comboboxes

    I hid the fields that did not need to be shown on the "lookuplists", as they are in no way related to the employee name (besides badge # or company). I can't say that "employee A" works on "Line 1" and in "zone 2". I can't even say what job function that employee is, as everday is a different puzzle, and we move people frequently.

    I use this standard "lookuplist", to reference to all of my userforms, that way I only have to update one, and it changes across the board. I hope that makes sence. This way I do not have to go in 8 different userforms and update the information on this worksheet individually. Usually all this data is cell referenced from the "Master version".


    So basically I need a form that puts the information how it is displayed in the "Attendance Log" as illustrated:

    Date Line Zone Employee
    1/3/2011 51 1 The Beast
    1/3/2011 51 2 The Dragonfly
    1/3/2011 51 3 Bad Navy Duck
    1/3/2011 51 4 The Ranger
    1/3/2011 51 5 Toxic Gangster
    1/3/2011 52 1 Concrete Killer
    1/3/2011 52 2 Moose Silly
    1/3/2011 52 3 Hideous Soldier
    1/3/2011 52 4 Endless Venus Hawk
    1/3/2011 52 5 The Scorpion

    All the way to Line 73, now as you can see in the below example for 1/4 people can move to different lines to different zones..

    1/4/2011 67 1 Rainbow Beta Devil
    1/4/2011 67 2 Persistent Emperor
    1/4/2011 67 3 The Flash Prince
    1/4/2011 67 4 Devil Sleepy
    1/4/2011 67 5 Bad Albatross
    1/4/2011 73 1 The Ranger
    1/4/2011 73 2 The Beast
    1/4/2011 73 3 Bad Navy Duck
    1/4/2011 73 4 The Dragonfly
    1/4/2011 73 5 Toxic Gangster

    So we can see on 1/3 The Ranger worked on line 51 zone4, but on 1/4 we see The Ranger worked on Line 73 zone 1.

    People miss work or are moved to different lines based on what the needs are. So I need to be able to input every employee from the dynamic range "NameList" into each field. I need to collect this data in this format so, I can guage their production with the help of some pivot tables...

    The issue is how can I input this information in this format every day easier than what I am currently doing?...so I thought ok, well if I used a userform with a bunch of comboboxes, maybe that would be the easiest...

    I have attached a better example here in the "attendance log" worksheet. and once again look at the userform in vb to see what I created to help fill in the blanks...

    any ideas how to get that result?
    thoughts?
    Last edited by 00Able; 01-22-2011 at 01:06 AM.

  8. #8
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Question Re: User Form with repetitive comboboxes

    Ok so if the first userform (frmattendancelog) is too complicated, as there are so many comboboxes, I created a smaller one, just to input one line at a time...

    See frmattendance for more details in the updated attachment...

    I have already written most of the code, but I am not sure if I am able to retrieve the zone names the way I have it configured...please see attachment

    Also I am not sure if it will enter the information correctly the way I have the code written

    Please Login or Register  to view this content.
    notice the "iRow", "jRow", "kRow", "lRow", "mRow", not sure if this is correct, I am really just learning code don't understand too much...

    (also keep in mind, this is not a good method, as I will have to open this up 18 times and hopefully I don't forget which Line I was just working on...but its better than nothing)
    Last edited by 00Able; 01-22-2011 at 03:11 PM.

  9. #9
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: User Form with repetitive comboboxes

    Ok, I also am attempting another solution, please see my newest post, unless someone can help me figure this out using userforms...

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: User Form with repetitive comboboxes

    I'm sure that I've amended this userform in another post

    Your code to clear the controls cannot work

    What's jRow, kRow etc?
    Last edited by royUK; 01-22-2011 at 12:35 PM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: User Form with repetitive comboboxes

    I think this does what you want
    Attached Files Attached Files

  12. #12
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: User Form with repetitive comboboxes

    What's jRow, kRow etc?
    well thats my logic to say, drop to new row,

    if I understand the original code to say drop data in iRow, well in this instance, I want it to drop into the next 5 rows, (which would be j,k,l,m) as each combobox will represent a different "Zone",

    I know I don't understand the code, but I am trying

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: User Form with repetitive comboboxes

    You can't do that without giving the variables a value. Don't you debug the code or test it? Try using Option Explicit to avoid these problems

  14. #14
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: User Form with repetitive comboboxes

    Don't you debug the code or test it?
    Of course, but somehow in code, logic isn't the same as equations, and am doing my best to learn, with what I am working with, outside the realm of what projects I have done, I have very little knowledge, which is to say...I struggle often...lol. I don't give up, and I easily adapt to look at things in a different perspective to achieve desired results...but often I need guidance along the way.

    One day, I am hoping to have a full understanding, the only way to do that is to step away the format that I am used to and try to discover new things.

    Your results above are exactly what I desired, however, in struggling with this issue, I thought of a different approach, one that you could argue might make more sence, in how the data is kept...

    so I like this userform, and want to keep it, but instead of posting information in the "Attendance Log" as it is currently set up, can we "also" input the information into a seperate worksheet, based upon what is inputted into the form:

    so, if I have a workbook named "January" and inside that workbook there are 31 worksheets named 1-31 respectively (each worksheet represents a day), so if you wanted to look at the attendance on 1/3/2011, you would open up the January workbook, and select the tab "3".
    Since the layout is the same in all 31 worksheets, as the Line and Zones do not change. the only variables would be the date and the employee names, could we say...ok if I input this date, and this Line, put these name in this worksheet?

    example: If I fill out the userform, and input this information:
    Date:1/3/2011
    Line:51
    Zone1:The Beast
    Zone2:The Dragonfly
    Zone3:Bad Navy Duck
    Zone4:The Ranger
    Zone5:Toxic Gangster

    It will enter this information in the worksheet labeled "3" as that is the date, and then search and find Line 51 zone 1 and input "The Beast", search and find zone 2 and input "The Dragonfly", etc, in addition to storing the information in the attendance log, that way if I wanted to see the attendance for a certain day easily, it would be there, and I would know if I missed a line...
    thoughts?
    I attached an updated workbood with the scenerio shown
    Last edited by 00Able; 01-22-2011 at 03:10 PM.

  15. #15
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: User Form with repetitive comboboxes

    It's a really poor idea to have separate sheets for each day. The best approach is what you already have - all data stored in one sheet, then you can use AutoFilter to view specific data, e.g. by name or by date

  16. #16
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: User Form with repetitive comboboxes

    I agree, besides if I really needed that information, I could pull it other ways like index match,
    thanks for your help....once again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1