+ Reply to Thread
Results 1 to 16 of 16

using a userform listbox to fill numbers in a cell

  1. #1
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    using a userform listbox to fill numbers in a cell

    I have a userform listbox that contains the names of every person in our department. As I go down through the workbook filling in data, when I get to the cell for members present, the userform automatically comes up and I check the name for each member that was present. Right now, the userform just takes each name that is selected and creates a message with a comma and a space in between each name. What I need to do in addition to this is to have it put a "1" in a specific cell for each member that is present and to leave the rest of the cells blank.

    ie. Member A, B, C, and D are on the list, only members C and D were present, I want to put a "1" in cell C1 and D1 but leave cells A1 and B1 empty.

    Below are the codes that currently run the userform. I am not sure what other info might be needed, but if you need more info let me know and I will upload it.

    Please Login or Register  to view this content.
    What I am trying to ultimately do is transfer a "1" for each person selected in the userform listbox to a logbook in another workbook that calculates the number of times members have been present. I have the code written to transfer data from other cells to the logbook and so it would not be a problem to have it transfer this data also.

    Thanks in advance
    Last edited by 2funny4words; 05-22-2010 at 01:56 AM.

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

    Re: using a userform listbox to fill numbers in a cell

    How is the ListBox populated? An example workbook would help
    Hope that helps.

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

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    The list is populated from a list in the second worksheet. Under the properties of the listbox, the RowSource says "Members_List". It has been quite a while since I worked on these userforms and I can't say that I remember exactly how I did them.

    I will upload a dummy version with the only differences being the names being changed.

    The uploaded file may also contain a logbook file which you can ignore the one that says report form is the one you will want. Rows 85 and 86 are where the userform is linked to if the macros are enabled.


    Thanks
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    I would be open to any suggestions that would make a simpler solution.

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

    Re: using a userform listbox to fill numbers in a cell

    Which sheet do you want to place the 1's?

  6. #6
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    Sheet 1 "Form" is where I would like to locate the numbers. Probably at the bottom somewhere.

    I have redesigned the section where the userforms are currently in use and if I can get the redesigned section to work as I want, there will be a cell for each name checked in the listbox. Then each name will have a place to go, and from there I will have to figure out how to put a "1" in "Name 1's" cell if "Name 1" is located within a specific region of cells.

    Should I start a new thread with the redesigned worksheet or continue with this thread?

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

    Re: using a userform listbox to fill numbers in a cell

    You can adapt this
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    Thanks RoyUK,

    In trying to adapt the code that you gave me, I ran it to see what the result would be. It is throwing a runtime 1004 error and when debugged, the line of code which is highlighted as the problem is

    Please Login or Register  to view this content.
    What exactly is this line of code saying? I assumed that I would just need to tell it what cell to start in and it would give the first cell a value of 1 if selected and if not would go to the next.

    I tried multiple variations but with the basic idea of...

    Please Login or Register  to view this content.
    but this did not work. Do I have the wrong idea or am I on the right track? Not feeling real smart tonight, not enough caffeine

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

    Re: using a userform listbox to fill numbers in a cell

    The code loops through the ListBox & if a line is selected it posts it to Row1,the column is determined by the ListIndex of the ListBox + 1 to allow for the ListIndex numbering starting at 0.

    If item3 is selected this will refer to D1 on the active sheet

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    Thanks RoyUK,

    The code works if I select only one person. Maybe I did not make it clear that multiple people would be selected. I modified the line so that it reads

    Please Login or Register  to view this content.
    It will place a "1" in row 103, column A,B,or C, etc. but will only do it for the last name that is selected. Therefore if I select the first three people on the list, and then the 6th person, it will only put a "1" in F103. The message will include everyone, but it will not put a "1" in cells A103, B103, or C103.

    I am not sure what to add to make it add the rest of the "1" 's. If I can get this to work my problem should be fixed.

    Thanks

    Keith

  11. #11
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    Bump, no response

  12. #12
    Forum Contributor
    Join Date
    03-02-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: using a userform listbox to fill numbers in a cell

    I'm about to head to bed, so I haven't had a chance to look at your example, but would it be possible to use the control source to link your box in the userform to a specific cell on your sheet? I have several sheets that I don't want people to actually look at, so I just have the information entered put in through a userform that uses the control source.

    Good luck.

  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: using a userform listbox to fill numbers in a cell

    Try this
    Please Login or Register  to view this content.
    Last edited by royUK; 05-22-2010 at 01:26 AM.

  14. #14
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    Thanks RoyUK,

    With a little tweeking, the code does exactly what I needed. The only other thing that I was wondering about is if I would be able to fill a column with the selected names also. I have tried all kinds of variations to the code below and have been able to fill the cells with both names and numbers, but they are always the same i.e. the cells A105:A125 are always the same no matter if I can get it to give me a value or a name. Here is the full sub
    Please Login or Register  to view this content.
    For some reason I am unable to highlight the line that I want to be noticed, but it is in the middle there and is
    Please Login or Register  to view this content.
    With this line, it will fill the range with the name of the last person selected in the listbox. I am sure that I am doing something wrong with the first part of the line, since it is filling everything the same, but I have tried variations of it includeing .coulmns and also with Cells, but with no luck. If I can not easily fix this, I will start a new thread, but I believe that it should be simple

  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: using a userform listbox to fill numbers in a cell

    You are trying to write tothe whole range,you need to specify a cell
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: using a userform listbox to fill numbers in a cell

    You did it again RoyUK, Thanks

    Works perfect

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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