+ Reply to Thread
Results 1 to 14 of 14

Combo Box is not reporting numbers correctly

  1. #1
    Registered User
    Join Date
    12-11-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2011
    Posts
    42

    Combo Box is not reporting numbers correctly

    comboboxproblem.xlsx

    Hi all,

    The work sheet attached is a quick example of what is happening when I make a list of numbers that start under 1.

    Example 0, .25, .5, .75, 1, 1.25
    When I select 0, it displays 1 in the linked cell. When I select 1 it displays 5.

    Even with a list 1, 2, 2, 3, 4, 5, etc. etc.. The second 2 is displayed as a 3 and the 3 as a 4 and so on.

    Some help on this would be much appreciated.

  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: Combo Box is not reporting numbers correctly

    Forms control comboboxes don't work like that. You can use the Index function to get the value you require. See the yellow cellcomboboxproblem.xlsx
    Hope that helps.

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

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    12-11-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2011
    Posts
    42

    Re: Combo Box is not reporting numbers correctly

    Thank you, that explains a few things.

    I was hoping to overwrite the linked cell by typing whatever number I wanted to.
    Having it leap frogged with the index formula will stop me doing this.

    The reason for setting this up, is so I can change the cell from a secondary work sheet.

    Any suggestions?

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

    Re: Combo Box is not reporting numbers correctly

    Can't you simply have a hidden linked cell?

    Alternatively use a Data validation list

  5. #5
    Registered User
    Join Date
    12-11-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2011
    Posts
    42

    Re: Combo Box is not reporting numbers correctly

    I have tried with data validation. and a combo box. It is necessary that the cell on sheet2 be editable.
    The attached sheet should explain a bit better why I am having trouble using a combo box.

    comboboxproblem2.xlsx

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

    Re: Combo Box is not reporting numbers correctly

    Can you explain what you need to do with this. I can't se why you can't use cells on a hidden sheet to work with

  7. #7
    Registered User
    Join Date
    12-11-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2011
    Posts
    42

    Re: Combo Box is not reporting numbers correctly

    I hope this new sheet comboboxproblem3.xlsx explains a lot better of what I am trying to achieve.

    Having the index function gets in the way of having the combo box overwrite a cell that can in itself overwrite itself.

    I'm struggling to explain this anymore sorry. The sheet should explain more.

    Much appreciated

  8. #8
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Combo Box is not reporting numbers correctly

    hi,
    not sure if it helps you but ...just try it!
    Attached Files Attached Files
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  9. #9
    Registered User
    Join Date
    12-11-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2011
    Posts
    42

    Re: Combo Box is not reporting numbers correctly

    Hi john55,
    Thank you for your response.

    ..but the sheet you attached doesn't seem to work at all, changing cells didn't do a lot. Could you please take a look at this sheet for me. comboboxproblem3.xlsx

  10. #10
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Combo Box is not reporting numbers correctly

    try this one and see if it helps you!
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-11-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2011
    Posts
    42

    Re: Combo Box is not reporting numbers correctly

    The 2 cells in the off white colour that are values 0.75 are not working as well as the picture of a drop down box? The blue cell on sheet 2 isn't linked to anything or changing?

    I can't see how this sheet works? There doesn't seem to be any extra coding or cell linking?

  12. #12
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Combo Box is not reporting numbers correctly

    I added a new combobox, it's in right side. (yellow color, columns D:E)
    sheet1
    select a number from combobox, the same number is displayed in cell D8 and cell D12.
    in sheet 1, you can update cell D12.
    the combobox takes what value you want to have. cells D8,D12,C4 have the same value
    sheet2
    you can see cell C4 has the same value.

    and add this piece of code at the begining
    Please Login or Register  to view this content.
    Last edited by john55; 12-18-2011 at 09:45 AM.

  13. #13
    Registered User
    Join Date
    12-11-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2011
    Posts
    42

    Re: Combo Box is not reporting numbers correctly

    Hi john55,

    I cannot make what you have posted work. I have looked at the coding behind it and cannot figure it out.
    The combo box you have made is not selectable.
    Could the problem have something to do with me using a mac with excel 2011? and you using windows with excel 2007?

    Is it completely necessary to use VBA?
    My skills with VBA are almost non existent..

  14. #14
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Combo Box is not reporting numbers correctly

    hi,
    I am sorry!
    I do not have mac, it might be possible to be a difference between them!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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