Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox, ComboBox or ? (XP; SP3)

    I am looking for a recommendation of how to enter the dollar amounts into the userform.

    Example:
    Each description of the line items are populated by a dynamic range to a listbox. What I would like to accomplish is have the Enduser enter a dollar amount on the appropriate line. Is this possible via a combobox? One should not be able to change the description lines just enter in the amount.

    John

  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: Listbox, ComboBox or ? (XP; SP3)

    Yes, but we would more info for details.

    Essentially, have combobox for the dollar amount. When the listbox item is selected, the value in the combox can be put in the appropriate spot (ie row) based on the selection.

    you could have it check to see if the combobox has a value, and clear it after the transfer to ensure not overwriting with empty values, You could even ask before overwriting any current values.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox, ComboBox or ? (XP; SP3)

    Steve,

    I can add items to ComboBox by using "ComboBox1.AddItem". My issue is with how to display the values I just added. I can only view them if I click within ComboBox1.

    I have attached an example workbook.

    Regards,
    John

  4. #4
    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: Listbox, ComboBox or ? (XP; SP3)

    If you want to display them, why not use a listbox?

    Use a textbox or a combobox to add the items, a listbox to display them(for example if named listbox2

    ListBox2.AddItem (i)

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox, ComboBox or ? (XP; SP3)

    Steve,

    We seem to be straying from my original post. What is the best method for showing a list of items and having the EndUser enter in an amount next to the item.

    John

  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: Listbox, ComboBox or ? (XP; SP3)

    But as mentioned before, I need more details of what you are trying to achieve to give example code. What is the source of the listbox, filled in code or read from a range. When you edit/ add to an item, do you want to change the range or what? Please clarify what you want done.

    As to your new question, this is essentially your original and that was answered (I thought) answered in <post#=522,424>post 522,424</post#>. To expand a little:

    Use a combobox (to choose from a list or add something new) or just a textbox [if the user is always adding something new.]

    Then either use the selection of the listbox to add the new item at that point, or select where to add in the list and have some "Update" button to update the list.

    The code would update the listbox then as appropriate.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox, ComboBox or ? (XP; SP3)

    Steve,

    Sorry for not being more precise in my explanation. I have attached a modified Excel file that provides a pictorial view of what I am after. Think of this as a simple input box. There are two columns, the first being a list of items and the second relating to an amount in which the Enduser is required to enter a dollar amount. Column one is populated by a range and can not be modified by the Enduser. The range may expand/contract at my discretion not the Endusers.

    I am not sure a ComboBox is the correct solution.

    Regards,
    John

  8. #8
    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: Listbox, ComboBox or ? (XP; SP3)

    Is this what you are after? I used a textbox not a combobox since it seems they will enter a value not select a value...

    I expanded your named range to include col B which is where the values are stored. You did not indicate what you wanted to do with the values so I assumed put them next to the items in the list.

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox, ComboBox or ? (XP; SP3)

    Steve,

    Something like that but instead of entering in one amount at a time, the EndUser would see every item and have the ability to enter a value associated with the item selected. As an example the EndUser should be able to enter values associated with four different items all on the same screen. I think of this more like a sheet in a workbook. There is a list of items and you simply select to appropriate cell next to the item and enter the value(s) in and then click on an update button. Your solution has them selecting an individual item, enter an amount and the click on an update button. For any additional entries they would repeat the process.

    Perhaps another way I can describe what I am trying to accomplish is a distribution of monies. Say you receive $100 and it is for payment of Item01, Item03, Item05 and Item06; 25, 35, 15 and 25 respectively. From the list just enter in the values associated with the items; click the update button.

    Regards,
    John

  10. #10
    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: Listbox, ComboBox or ? (XP; SP3)

    You could add several textboxes, one for each row.

    Instead of a listbox and textbox, you could make the form look like a workbook range and have each item in a separate textbox (each item read from the row) and then read the 2nd column. (in your example 16 textboxes, 2 columns of 8 each)

    If your item count would change you would have to add the textboxes with a loop at runtime (and even expand the userform size if needed.

    Another option is just to let them use a workbook. It is already setup in rows/columns. Use a temporary workbook and have them press an update button to copy into the desired location (with validation if needed)

    But what you describe is possible in the file I sent. Enter the number, select the row, and press the button. It is added. Add the next one, then the next etc.

    Steve

  11. #11
    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: Listbox, ComboBox or ? (XP; SP3)

    <!profile=HansV>HansV<!/profile> has some example code in <post#=494,126>post 494,126</post#> to dynamically add textboxes which may be adaptable if you want to create your own "grid". This number could be determined at runtime based on the rows in the range, If you add a command button it can be used to update the spreadsheet with the values. You can even have it validate the values as needed/desired.

    Steve

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox, ComboBox or ? (XP; SP3)

    Thanks,

    I have to give it a try.

    John

Posting Permissions

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