Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA- ControlSource - formatting (97 SR2)

    I have found that textboxes that are linked to spreadsheet cells do not carry over the cell formatting...Dates appear to use the System setting, and the count of decimal places can't be limited. Anybody know of workarounds? I want my dates to be unambiguous, as in "3 April 2002" but they appear as "4/3/02"... which is ambiguous.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA- ControlSource - formatting (97 SR2)

    You could point your Rowsource to a different range which contains the text value of the date formatted as you want. Say th edate is in A1, you could have a hidden cell with the formula = TEXT(A1,"d, mmmm yyyy") and then use that cell as the row source. Or use the Userform Initilaize event to do the same thing, and skip the row source property.

    Private Sub UserForm_Initialize()
    TextBox1.Value = Format(Range("A1"), "d,mmmm yyyy")
    End Sub

    Andrew C

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA- ControlSource - formatting (97 SR2)

    Maybe I'm missing something, but I don't see how this could allow user input to the textbox control. Any time the user tried to make a change, they'd be changing the wrong cell...and what is Rowsource? I looked at the properties of a textbox and find no such property. Is that a property of textboxes in some other app such as Word?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA- ControlSource - formatting (97 SR2)

    Sorry for the confusion. I thought your textbox was picking up and displaying a date value in a format you did not like, when it is the other way round. I think what you need is a control capable of handling dates. You can obtain a freeware control for such a purpose at <A target="_blank" HREF=http://www.mvps.org/ccrp/>CCRP</A> (Common Controls Replacement Project). They a Date/TRime picker control which yoi can download and register on your system. If you are distributing the project to other users you will need to include and register the control on their systems.

    The ControlSource propert applies to List/Combo boxes and not Textboxes, sorry again.

    Andrew C

  5. #5
    New Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA- ControlSource - formatting (97 SR2)

    well...

    that obviates the advantage of programming entirely within Office, and introduces one more maintenance item, besides the installation work. I can think of at least one way to work around it (identically placed, identical twin controls that regulate their own and each other's visibility to react to the user)

    Used with your formatting suggeston, that would also be a way to address the other formatting I asked about, that of decimal places. I think it's worth noting that textboxes (to be used for input as well as display) do not have any number formatting capabilities...this seems a gross omission.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA- ControlSource - formatting (97 SR2)

    I do agree about the short commings of the basic TextBox control, both for formatting and validation.

    If you just interested in the display of the date on the spreadsheet, you could use something like <pre> Range("A1") = DateValue(UserForm1.TextBox1.Value)</pre>

    , and have the cell formatted to display the formatting you want. So if the Textbox contained 4/3/02, it should place 3 April 2002 if the cell is formatted as "d mmmm yyyy". However this means you cannot use the control source property, and must use code to transfer the value. And also requires that the date be entered into the textbox in a manner that excel can convert it to a date value.

    Andrew C

Posting Permissions

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