+ Reply to Thread
Results 1 to 17 of 17

Change comboboxes to listboxes

  1. #1
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Change comboboxes to listboxes

    Hi, I have a userform that was created and wish to make some modifications. Firstly, I am trying to the change the dropdown comboxes in the attached form to a listbox. The only reason for the change is to have the user select more than one option. I was hoping a code would exist to make that change instead of reworking the format of the form, but it seems like listboxes is the only way to go.

    Secondly, I cant seem to figure out how to make when I hit submit, it opens up microsoft outlook and the subject "A PIR has been submitted to you" automatically enters in the subject line. Is this even possible?

    If I cna help some help with the attached document that would be amazing.

    thanks you guys.
    Attached Files Attached Files
    Last edited by Jcanguy; 12-06-2010 at 07:08 PM.

  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: Change comboboxes to listboxes

    Hello Jcanguy,

    I made some changes to the UserForm. I removed th ComboBoxes and replaced with them ListBoxes. See the screen shot for the changes. Is this what you want or something else?

    Muliple ListBox Selection Example

  3. #3
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    Leith,
    That is perfect! Thank you much. I really appreciate it. The only challenge now is having the the submit button opens outlook.

    Again, thank you.

  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: Change comboboxes to listboxes

    Hello Jcanguy,

    The Outlook button isn't a problem. I can easily add that in. But, now that you can select multiple items in the List boxes, will one checked item result in adding a line to the PIR sheet?

  5. #5
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    Leith,

    Ideally, I would like each item selected from the listbox to be in the same row, same cell.

    Thanks again.

  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: Change comboboxes to listboxes

    Hello Jcanguy,

    Can you give me an example of what that would like?

  7. #7
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    Hi Leith,

    the items when checked would be separated by a comma in the error detail colum in the PIR sheet, similar to the attached screen shot.
    Attached Files Attached Files

  8. #8
    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: Change comboboxes to listboxes

    Hello Jcanguy,

    Okay, I see now. Each list Box would be on separate line with all the choices listed in the same cell, correct?

  9. #9
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    Leith,

    Yes you are correct.

    Thank you genius

  10. #10
    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: Change comboboxes to listboxes

    Hello Jcanguy,

    I made a few changes to your UserForm and tested i. The results look correct to me, but you are the real judge. Test it out and let me know what you find.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    Leith,

    Thanks for all of your help on this, I just want you to know that you have done a fascinating job helping me. You've expanded the boundaries of impossibilities for me and I really appreciate it. I ran the trial userform, and everything works well (codes etc), however instead of having three rows entered after clicking submit, could it only be one row? Each row is one submittal and only that error type should show in the PIR worksheet and then the different error details would be in the cell next to it, separated by comma.

    So if Patient ID is checked under preanalytical, then only preanalytical as an error type and Patient ID as error detail would be transferred to the PIR worksheet (and all the other information of course) in one row.

    I hope I'm making sense, again I really appreciate your help with this project.
    Last edited by Jcanguy; 11-28-2010 at 12:20 PM.

  12. #12
    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: Change comboboxes to listboxes

    Hello Jcanguy,

    I made the ListBoxes exclusive. When you make a selection in one ListBox the other ListBoxes' selections are cleared. Outlook is brought up after all the information has been transferred to the worksheet with a subject line of "A PIR has been submitted to you". No recipient(s) is/are included since you didn't specify this information. It can be included. I just need to where the recipient is located and what information you want to send. Here is the code for the main form.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    This is great, thanks Leith. There are still three rows copied into the “PIR” worksheet though. I tried changing it but couldn’t figure how. I understand why you’d think all three error types should appear in the worksheet, but for tracking purposes I only need one. If there are three lines, it appears there were three different forms submitted. So, is it possible so that only the tag with a checked item in the listbox would appear in the PIR worksheet? Please see a sample of a mock file I created for illustrations. I’m sorry to be such a bother.
    Attached Files Attached Files

  14. #14
    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: Change comboboxes to listboxes

    Hello Jcanguy,

    You're correct that there were three separate entries in the workbook. Each from one of the list boxes. With that said, I am confused about what you want.

    The list boxes are now exclusive. Only one will have its selections copied to the worksheet each time the submit button is clicked. So, what am I missing about what you want to do?

  15. #15
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    Hi Leith Sorry I made this confusing. I will see if I can explain it better. Right now the worksheet shows in the error type column, three rows.
    PreAnalytical
    Analytical
    Post Analytical
    What I am looking for is for one row to show up in the column Error Type each time the submit button is hit..
    The Column should show only one of the three options (PreAnalytical, Analytical, or PostAnalytical).
    The Error Description looks great.
    As it stands now the worksheet makes me think three people submitted the form each time. That is why I am looking for only one row total for each time the submit button is hit. Does that make sense?

  16. #16
    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: Change comboboxes to listboxes

    Hello Jcanguy,

    I thought each entry ,as it is submitted, would be saved to the PIR worksheet and only the last entry would be emailed. From your reply, I believe this not what you intended. If you only want one only line filled in, row 2, each time the submit button is clicked then I can do that.

  17. #17
    Registered User
    Join Date
    10-04-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Change comboboxes to listboxes

    Hi Leith,
    The email portion is fine, not an issue at all, it works great,yes only the last entry will be emailed and the others will be saved. However, when the submit button is hit, the information is transferred three times in the workwook. There are three rows of the same information, until error type and error description columns. The information should only be transferred once (resulting in one row/line of information) similar to the second document I attached.

+ 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