+ Reply to Thread
Results 1 to 16 of 16

cell value as default text in combobox

  1. #1
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    cell value as default text in combobox

    Hi, I have a combobox linked to a list of dates. The list is made by formula so the current date is always in cell B15 on a worksheet named "lists". B14 is the previous day, and B16 is the next day and so on.

    What I would like is for the text displayed in the combobox to be the current date, which is in cell B15. It would also be nice if the current date were highlighted in the drop down list when you click the arrow on the combobox, but that part is not a necessity for me. Thanks in advance for the help.

    Here is the code I am using to populate the combobox
    HTML Code: 
    Also if someone could tell me how to format the combobox(dddd, mmmm dd, yyyy), that would be awesome too, Thanks again
    btw this isn't working for me
    HTML Code: 
    Last edited by humboldtguy; 04-29-2010 at 07:21 PM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell value as default text in combobox

    Assuming you are using a combobox from the Active-X controls . . .

    First off, dispense with the For . . .Next approach to populate the list box and set the ListFillRange to a named range for the date values, which you already seem to have as shown by "Range("Dates").

    It seems your Date range is comprised of three cells previous date (B14) Today's Date (B15) and next day (B16). If so, then you can set the default selection based on the list index, which is "1" for the above list of dates.

    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    Hi, thank you for the quick response. I tried the code, and neither one seemed to work....if it makes a difference, the date range is more than 3 cells....it is actually 27. Anyway it is a named range. Here is the code exactly as i put it in, perhaps i didnt put it in the right place. Thanks again for the help and the quick response.

    Please Login or Register  to view this content.
    Quote Originally Posted by Palmetto View Post
    Assuming you are using a combobox from the Active-X controls . . .

    First off, dispense with the For . . .Next approach to populate the list box and set the ListFillRange to a named range for the date values, which you already seem to have as shown by "Range("Dates").

    It seems your Date range is comprised of three cells previous date (B14) Today's Date (B15) and next day (B16). If so, then you can set the default selection based on the list index, which is "1" for the above list of dates.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell value as default text in combobox

    I tried the code, and neither one seemed to work
    The code works when implemented correctly. See attached example. It would probably be best if you uploaded a sample workbook sans sensitive info.

    The list index starts at counting at zero, so what ever position your default selection is in the list change it in the code to the applicable value.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    Quote Originally Posted by Palmetto View Post
    The code works when implemented correctly. See attached example. It would probably be best if you uploaded a sample workbook sans sensitive info.

    The list index starts at counting at zero, so what ever position your default selection is in the list change it in the code to the applicable value.
    Oops, did i forget to mention that the combobox is in a user form? is that why its not working?? Anyway what you did in the example is perfect, except I can't make it work in a user form. Any idea's?

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell value as default text in combobox

    Oops, did i forget to mention that the combobox is in a user form? is that why its not working??
    Yes, you forgot to mention this and it is the reason it doesn't work.

    Code for Userform and combo box control. See attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    Hi, I'm just not having any luck. I must be putting the code into the wrong place. I am attaching an example copy of my workbook. Note that the form I have tried your examples on is named LogHoursForm. It can be accessed in the workbook on the sheet named home by clicking the command button named Log Hours. Thanks again, I appreciate the help.

    Quote Originally Posted by Palmetto View Post
    Yes, you forgot to mention this and it is the reason it doesn't work.

    Code for Userform and combo box control. See attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by humboldtguy; 05-01-2010 at 12:13 AM.

  8. #8
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell value as default text in combobox

    humboldtguy,

    Please don't quote whole threads as it clutters up the post and isn't necessary. If needed, just reference the specific portion of the thread using the quote tags. It will keep the thread cleaner.

    First, I don't why see you chose to use a lot of unnecessary For loops to add items to the controls while making reference to named ranges. You can simply enter the named range in the RowSource property of the control and will end up with less code to write and manage.

    For the the combo box in the initialize event, add this line (must be on its own line and not inside the For loop).
    Please Login or Register  to view this content.
    I have Excel 2003 on this PC and have to use the Office 2007 conversion pack to open your file. I get an error when displaying your form and can only attribute this to the conversion. It errors on the line formatting the date value of the combo box. I had no issues with this per the sample workbook I had created, so the code is valid.

    In your "Date" range, unless you need the time component, I would use TODAY(), which does not include the time, instead of NOW() which does - seeing as your form is designed to select time entries separately.

  9. #9
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    Hey Palmetto, I did it that way because thats what the online tutorial i found said to do. It seemed to work so I was fine with it. I found the row source property you were talking about, not sure exactly what to put there to make it work. I don't know VBA, just faking it.

    Using =today, instead of =now is great. thanks for that, and it cleared up my formatting issue. Anyway thanks for the help, sorry for over quoting...my bad.

    Quote Originally Posted by Palmetto View Post
    humboldtguy,
    I don't why see you chose to use a lot of unnecessary For loops to add items to the controls while making reference to named ranges. You can simply enter the named range in the RowSource property of the control and will end up with less code to write and manage.

  10. #10
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell value as default text in combobox

    I found the row source property you were talking about, not sure exactly what to put there to make it work
    Well, you created a named range for the dates and named the range "Date". In the row source property you would enter "Date" (w/o quotes) into the property and delete the lines of code that fill the list using the For loop.

    The RowSource property can use a direct sheet range reference (Sheet1!A1:A10) or a named range reference as shown above. IMO, this is preferable to unnecessary code for filling lists.

  11. #11
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    So this is some new code i found somewhere on the internet, plus the bit you provided to set the combobox default value to the date i want, Interesting thing though, when i open the user form the date displayed is from 2006. Any idea why that happened???

    Please Login or Register  to view this content.

  12. #12
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell value as default text in combobox

    No idea why it changed.

    However, my comment about putting "Date" into the Row Source property was meant to do so via the properties window for the control, not adding lines of code. While in the VB Editor, click on Menu > View > Properties window and look for RowSource and enter the named range there then delete the lines of code.

  13. #13
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    Quote Originally Posted by Palmetto View Post
    No idea why it changed.

    However, my comment about putting "Date" into the Row Source property was meant to do so via the properties window for the control, not adding lines of code. While in the VB Editor, click on Menu > View > Properties window and look for RowSource and enter the named range there then delete the lines of code.
    Hey, I actually posted this before I saw your most recent post, anyway i changed to the Row Source method in properties, works beautifully. Formatting code you provided also works great. Now if I can just get the current date to show as the default value for the LRHMonth Combo box I will be set. The current date is located on the Lists sheet, cell B15. Thank you for your patience.....This is all new to me

  14. #14
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: cell value as default text in combobox

    The Listindex property starts counting at zero, so if the current date is in row 15 then the index value should be 14.

  15. #15
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    Quote Originally Posted by Palmetto View Post
    The Listindex property starts counting at zero, so if the current date is in row 15 then the index value should be 14.
    Ok, got that. Still returns a date that is not on the list....any idea why???

  16. #16
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: cell value as default text in combobox

    I managed to get the right date in the combo box by naming the cell i want to reference and using this code

    Please Login or Register  to view this content.
    Thanks for all the help palmetto, could not have done it without you

+ 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