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

    ComboBox / Formula problems (2003 SP2)

    Editted to add PS

    Good morning

    I am still struggling on trying to get a formaula to work with a ComboBox, in the attached sample you can see a ListBox in B2 and a formula in H5 which partly takes its value from the information supplied by the ListBox.

    In O1 I have made my ComboBox selection the same as that for the Listbox in B2 and then in O5 copied the formula and changed the B2 references to O1 but to no avail.

    Help, as usual, welcome

    Steve

    PS: when you open the workbook it says that ther is a Macro but as far as I am aware there is not, I have scanned it for anything malicious but there is nothing there?
    Attached Files Attached Files
    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: ComboBox / Formula problems (2003 SP2)

    The macro comes since in sheet1 you have code:

    <pre>Private Sub ComboBox1_Change()

    End Sub</pre>


    There is a difference between the Listbox from FORMS toolbar (B2) and the combobox from the control toolbox (O1). The items from the Forms toolbar give the INDEX value (a number 1-whatever). The "value" of the item from the control toolbax yields the "text" (eg "Belarus - +65km from Minsk" not a number. You can use MATCH to look it up:

    =INDEX(C26:F138,match(O1,B26:B138,0),D2)+J3*INDEX( G26:J138,match(O1,B26:B138,0),D2)

    Or you could set the "BoundCOlumn" property of the combobox to 0 (from 1) to give the "offset" (not the offset is "0-based" so starts with 0 and not 1 like the "index" of the items from the forms toolbar and then use:

    =INDEX(C26:F138,O1+1,d2)+J3*INDEX(G26:J138,O1+1,D2 )

    You could also just use the combobox from the FORMS toolbar if desired, it works like the listbox you used...

    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: ComboBox / Formula problems (2003 SP2)

    Thanks Steve

    That works a treat but I must admit to not fully understanding yet why, I will have a play to see if I can get it to work in other scenarios so that I may understand it

    "You could also just use the combobox from the FORMS toolbar if desired, it works like the listbox you used.."

    The overriding reason I wanted the ComboBox from the control toolbox is that you can type in the first letter of a word to avoid scrolling all of the way down a list as the list will eventually be 2000 to maybe 3000 items long. In an earlier post HansV did show me how to use the ComboBox to do this I just could not get it working with the formula.

    Thanks alot for your help

    Steve Hocking
    Cheers

    Steve

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

  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: ComboBox / Formula problems (2003 SP2)

    What don't you understand? To help understand, while creating it, you may not want to hide the B2 or O1 cell so you can see the value displayed that you are putting into your formula. INDEX takes an index value.

    If the list is going to be that long, I would recommend putting in a series of Sub lists (some logical subgrouping). Personally, as a user, I abhor having to scroll through a list that large. I find it easier to click on several smaller lists than on huge list.

    Steve

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

    Re: ComboBox / Formula problems (2003 SP2)

    Cheers Steve

    Thanks for the advice and explanation

    Steve
    Cheers

    Steve

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

Posting Permissions

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