Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Combobox selection (MSOFFICE XP)

    Hi All

    I have attached a spreadsheet with my feeble attempt to to select a list of codes for my salesforce to submit to the office for a quotation.
    I am in no doubt that someone can make it more proffesional for me, here's hoping.

    Thanks in advance
    Attached Files Attached Files
    If you are a fool at forty, you will always be a fool

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox selection (MSOFFICE XP)

    Without commenting on the technical programming and deployment of the controls you chose to use, I would question the choice itself. What you are trying to do can be done in a much simpler, more stable manner that even saves on file space.

    See your spreadsheet I attached. You need only use the Data>Validation tool to create a drop down that referneces your lookup table. I used range names instead of cell addresses. Note: I did not bother to create a dynamic named range.

    Click in the cell where the first item goes and then click the down arrow.

    -Lenny
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Combobox selection (MSOFFICE XP)

    Hi LennyJo

    I did say that my attempt was pathetic we novices often take the hard road, but kind people like yourself put us back on track.
    Your reply was excellent and is just what I am looking for thank you very much

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    If you are a fool at forty, you will always be a fool

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox selection (MSOFFICE XP)

    That was it... I was concerned you were working too hard. <g>

    Let me know if you need help with the vlookup function, the naming of ranges, or using a dynamic range. Explore the Data>Validation options, too, and see that you can enter a message to the user should they try to circumvent your drop down selections.

    -Lenny

  5. #5
    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 selection (MSOFFICE XP)

    I comment on the "improvements". For each row you exchanged 3 INDEX lookups for 2 VLOOKUPS. If the number of VLOOKUPS increase, using MATCH (to get the row number in the database) combined with INDEX might prove faster. With each VLOOKUP you must determine the row again and again. With MATCH you do it only once and thus should take less resources.

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Combobox selection (MSOFFICE XP)

    Hi Lenny

    I have brief knowledge of vlookup and naming ranges, but I know nothing of using a dymamic ranges the one I was using was copied from another sheet.

    The bit I do not understand fully, is the to enter a message should the user try to circumvent the drop down selections.

    I have attached the sheet again to show how far I have proceeded with the information you have given me.

    Thanks again
    Attached Files Attached Files
    If you are a fool at forty, you will always be a fool

  7. #7
    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 selection (MSOFFICE XP)

    You protected the spreadsheet and hid the formulas so we couldn't view them.
    One suggestion:
    You should "lock" your cells with calcs I was able to change the description, price and total price.
    Format - cells - protection tab - select "lock"

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Combobox selection (MSOFFICE XP)

    Hi Steve

    Thanks for your input, I have done as you suggested, I have attached the file again and the temporary password is alanita lower case.

    Once again thanks for your input

    Braddy
    Attached Files Attached Files
    If you are a fool at forty, you will always be a fool

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox selection (MSOFFICE XP)

    - I noticed, when you build a table, you have a tendancy to add a blank row under the labels and before the first record. I recommend you build them with the labels directly "touching" the the first record. There should be no separation of the labels from the data.

    - Input messages (i.e., "Select from drop down box") can be programmed via a cell comment (as you did), pop up VBA messages, or with Data Validation. You programmed the drop down box using Data Validation. Now, use the other tabs in the same dialog box (Data>Validation) to set up instructions for when the user clicks the cell, and directions for when the user enters non-valid data. In the attached sheet, I added a few. Take a look.

    - Try not to "stack" labels. I'm not sure if cell D22 was scratch or for real, but try to contain a column label completely in one cell over the data. If you must, make another label over the true label for clarity, but separate it with a blank row and then hide the row. (see sheet)

    - The use of ISERROR is fine to prevent an error code from appearing while the Code cell is blank. You might also consider using the IF statement in the sheet instead. It's just a bit easier to write.

    - There is a named range for a dynamic range in the sheet, athough it's not being used. They're really quite simple...
    Your range is defined as: =OFFSET(Data!$A$3,1,0,COUNTA(Data!$A$4:$A$979),3). That means start at cell A3, but move down 1 cell to A4, and don't move left or right at all. Extend the selection from A4 down as many cells as there are cells filled with data within A4 to A979. (So, if there are 261 cells in that range with data in them, it will select from A4 to A264.) Finally, extend the selection to include 3 columns (column A inclusive) to the right.

    Using this concept, you don't have to worry about redefining the named area should you add too many records to it. The definition will grow on its own. Just use the defined name in your formulas.

    The rest of it looked fine. Have fun.

    -Lenny
    Attached Files Attached Files

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Combobox selection (MSOFFICE XP)

    Hi Lenny

    Thanks for your reply I have been able to make a few refinements with the help of your comments and advice.

    Thanks again

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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