+ Reply to Thread
Results 1 to 3 of 3

Excel:How change the textbox format from text to date format?

  1. #1
    Registered User
    Join Date
    10-27-2013
    Location
    Ludwigsburg
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question Excel:How change the textbox format from text to date format?

    Hi@all
    I am using Excel 2007 (German Version) and hope someone can help me with the following:
    I have created a user form with several input boxes. Two of these Boxes are TextBoxes that are used to get dates from a pic up calendar next to them. These dates are then transferred from the userform to a DataList (which is connected to a calendar that auto-populates itself from this DataList).

    calendar.jpg

    The problem is: The dates that are supplied from the userform TextBoxes to the DataList are dates in text format. So... I get valid numbers and everything seems to be O.K. (dates are displayed normally as "dd.mm.yyyy") but the calendar cannot use them because they are all in text format. I have tried reformatting the date columns in the DataList to the date format but this has no effect whatsoever on the dates that come from the userform.

    I therefore need the right VBA string - something that goes between the two lines


    Please Login or Register  to view this content.


    in the two data TextBoxes in my userform.

    Can anybody help?

    Any suggestions would be greatly appreciated.
    Last edited by alansidman; 11-08-2013 at 09:24 AM.

  2. #2
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: Excel:How change the textbox format from text to date format?

    Textboxes can only contain text... much as their name implies.

    Changing the format in the TextBox_change event, or any other event will still result in a String rather than a date.

    If you are passing the contents of a textbox to another control (or function) that only accepts a valid date then you need to convert the contents of the textbox when passing it. For example:
    Please Login or Register  to view this content.
    In this situation, I usually test the contents of a textbox in the exit event and use the Format() function, but it is still just text, not a date.
    Please Login or Register  to view this content.
    You should also note the Exit event is not availabe for ActiveX textboxes inserted directly on a worksheet...

  3. #3
    Registered User
    Join Date
    10-27-2013
    Location
    Ludwigsburg
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Excel:How change the textbox format from text to date format?

    Many thanks for your feedback. I now understand why my calendar is not functioning.

    cytop:If you are passing the contents of a textbox to another control (or function) that only accepts a valid date then you need to convert the contents of the textbox when passing it. For example:
    [Select Code] copy to clipboard

    Calendar.Date = cDate(TextBox1.Text)
    I am passing the contetens of the textbox via a submit command button as follows:

    Private Sub CommandButton2_Click()
    Dim nextrow As Integer
    '=COUNTA(A:A)+1
    nextrow = WorksheetFunction.CountA(Sheets("Liste").Range("A:A")) + 1

    Sheets("Liste").Cells(nextrow, 1) = Now
    Sheets("Liste").Cells(nextrow, 2) = UserForm1.ComboBoxTestbench.Value
    Sheets("Liste").Cells(nextrow, 3) = UserForm1.TextBox1.Value (calendar date "from")
    Sheets("Liste").Cells(nextrow, 4) = UserForm1.TextBox2.Value (calendar date "to")
    Sheets("Liste").Cells(nextrow, 5) = UserForm1.TextBox4Abt.Value
    Sheets("Liste").Cells(nextrow, 6) = UserForm1.TextBox3Name.Value
    Sheets("Liste").Cells(nextrow, 7) = UserForm1.TextBox6Email.Value
    Sheets("Liste").Cells(nextrow, 8) = UserForm1.ComboBox2Kompo.Value
    Sheets("Liste").Cells(nextrow, 9) = UserForm1.TextBox5SAP.Value
    Sheets("Liste").Cells(nextrow, 10) = UserForm1.TextKurzbeschreibung.Value
    Unload UserForm1
    End Sub
    Since I have little experiance in VBA programming I would be grateful if someone could tell me where to insert the code "Calendar.Date = cDate(TextBox1.Text" .

    I have also found another possible solution

    Private Sub CommandButton1_Click()
    TextBox1 = Format(Date, "dd/mm/yyyy")
    End Sub
    But here the same question...
    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Date Format in userform textbox is different from excel worksheet?
    By z-eighty2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2013, 06:46 AM
  2. [SOLVED] Change textbox format to date
    By imran91 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-06-2012, 04:01 AM
  3. Not able to change date format into proper excel date format
    By excel5111987 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2011, 08:19 AM
  4. Change Date Format to Text Format
    By lglover39 in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 12-20-2007, 05:53 PM
  5. change date format into text format.
    By adsxvii in forum Excel General
    Replies: 1
    Last Post: 03-22-2007, 02:07 AM

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