Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Combo Box

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have to be the dumbest guy in the world. Here goes. I have a combo box. The Target cell for the box is Y1. It is formated as Date style 03-Apr-02. The list range for the combox is set to Y48:Y79. Each of these cells contain the formula =TODAY(), =TODAY()-1, etc. The cells are formated as Date style 03-Apr-02 also. Now, when I click the arrow on the drop down box, I see this list. Thats kewl. Now, I select an entry in the list. Here is where I am stuck. The list goes away as it should, but in the combo box appears one of those goofy date numbers that Bill Gates programmers are so fond of. The Target cell also goes to that goofy number. I want the actual date to appear in the target cell and the combox. Any way I can do this? I cant seem to find a format for this?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Remove the linked cell reference in your
    combobox properties.
    Place this code in.
    Set the format on the cell to the correct
    date format mm-ddd-yy



    Private Sub ComboBox1_Change()
    ComboBox1.Text = Format(ComboBox1.Text, "dd-mmm-yy")
    Range("Y1") = Str(Format(ComboBox1.Text, "dd-mmm-yy"))
    End Sub



    Tom

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks heaps, tom. I will give it a try

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, When I did as you instructed, and clicked on a date in the drop down, I got a syntax error on the Range("Y1") = str....line. I checked it and it is typed in exact.

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OOPS, I found a " that I left out. Now I get a type mismatch on that entry.????

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I gave you the wrong code...
    BRB

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I gave you the wrong code...
    The str() was the problem.
    Sorry.



    Private Sub ComboBox1_Change()
    ComboBox1.Text = Format(ComboBox1.Text, "dd-mmm-yy")
    Range("Y1") = Format(ComboBox1.Text, "dd-mmm-yy")
    End Sub



    This is probably obvious, but just in case, make sure you replace ComboBox1 with the name of your combobox...

    Tom

    [ This Message was edited by: TsTom on 2002-05-04 10:56 ]

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeppers, Tom, that was it. Works now. Thanks a bunch, thought I was going to have to use a list box for this one and didn't want to.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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