Advice to format column with real dates

[ipbr21054]

Please advise how i do as per title so these current dates are not overwritten as Text
Thanks

 

[6StringJazzer]

Are the dates entered as values, or are they formulas?

What do you mean "overwritten as Text"? How are they getting overwritten and what is causing them to be converted from date values to Text?

We need a lot more information.

 

[ipbr21054]

Referance post #42

Show message if specific text in cell & date check are met

Morning. On my worksheet in column G will be various dates & Text. In column A will be a date. The code advice im looking for is as follows. If text in column G = POSTED & date in column A is 30 days old then show Msgbox Once im shown the Msgbox i can take it from there so it doesnt have to...

www.mrexcel.com

 

[6StringJazzer]

Sorry, not going to read 43 posts to understand your question.

 

[ipbr21054]

I was advised that the date in the column cells are not true dates.

When i was in a cell & looking at the address bar i could see the date but there was another character before the date.
Now looking in the setting its been changed so now no odd character is shown, but just the date.

If i right click on the date its formatted to short date.

So i had an issue where the helper said that when i format that date it should be, Then he advised a number but when i did it / checked it was a different number.
At this point i was then advised to do as per this posts title.

Thanks

 

[NoSparks]

for the existing data...
have a look at this

for new entries...
adjust the code in whatever userform you're using to write those dates so they will be real dates.

 

[ipbr21054]

So ive watched the video & at the same time looked at my sheet.
I noticed that the dates in column A when i selected one it then using the format drop down it had shown a date under the NUMBER heading.
Having said that for some reason the cell that gave us the Msg pop up about 364 days etc was actually correct,so strange how one was correct but all the others wrong.

Anyway ive followed the video & done as shown, now any date i select is shown correctly, Thanks.

You then mention the part about For new entries.
Clicking in any empty cell in column A down the page when i add a date manually then look at format drop down it is shown correctly so is that now covered or do i still need to adjust code in whatever userfom to write dates so they will be real dates.

Thanks

 

[Alex Blakenburg]

Typically a UserForm captures the input as Text. Since you are presumably using UK date format of dd/mm/yyyy are you using CDate when you write it to the spreadsheet eg.
Range("A1").Value = CDate(TextBox1.Value)
If not, you will need to adjust the code accordingly.

 

[ipbr21054]

This is the code on the command button that sends values from userform to worksheet
I understand its long so i have put these few lines that represent the ones in question so you can look & advise

TextBox1

 With ThisWorkbook.Worksheets("POSTAGE")
    .Cells(lastRow + 1, 1).Value = TextBox1.Text

        TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
        TextBox1.Value = Now

Private Sub PostageSheetTransferButton_Click()

Cancel = 0
If TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "CUSTOMER`S NAME NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox2.SetFocus
    
ElseIf CommonPurchasedItem.Text = "" Then
    Cancel = 1
    MsgBox "PURCHASED ITEM NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    CommonPurchasedItem.SetFocus
    
ElseIf TextBox9.Visible = True And TextBox9.Text = "" Then
    Cancel = 1
    MsgBox "EBAY USERNAME NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox9.SetFocus
    
ElseIf OptionButton15.Value = False And OptionButton16.Value = False Then
    Cancel = 1
    MsgBox "SECURITY QUESTION NOT ANSWERED", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton12.Value = False And OptionButton13.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT A USER NAME OPTION", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf TextBox4.Visible = True And TextBox4.Text = "" Then
    Cancel = 1
    MsgBox "TRACKING NUMBER NOT ENTERED", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox4.SetFocus
    
ElseIf OptionButton7.Value = False And OptionButton10.Value = False And OptionButton17.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT A POSTAL COMPANY", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton4.Value = False And OptionButton5.Value = False And OptionButton6.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT AN ORIGIN", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton1.Value = False And OptionButton2.Value = False And OptionButton3.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT AN EBAY ACCOUNT", vbCritical, "POSTAGE TRANSFER SHEET"
        
ElseIf OptionButton13.Value = True And TextBox9.Value = "" Then
    Cancel = 1
    MsgBox "YOU MUST ENTER AN EBAY USER NAME", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox9.SetFocus
    
End If

If Cancel = 1 Then
Exit Sub
End If

Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim lastRow As Long
Dim LArea As Long
Dim xShape As Shape
Dim Mycomments As Variant
Dim answer As Integer

lastRow = ThisWorkbook.Worksheets("POSTAGE").Cells(Rows.count, 1).End(xlUp).Row
    
On Error Resume Next
    
 With ThisWorkbook.Worksheets("POSTAGE")
    .Cells(lastRow + 1, 1).Value = TextBox1.Text
    .Cells(lastRow + 1, 2).Value = TextBox2.Text
    .Cells(lastRow + 1, 3).Value = CommonPurchasedItem.Text
    .Cells(lastRow + 1, 5).Value = TextBox4.Text
    .Cells(lastRow + 1, 4).Value = TextBox6.Text
    .Cells(lastRow + 1, 9).Value = TextBox9.Text
    .Cells(lastRow + 1, 7).Value = "POSTED"
    .Cells(lastRow + 1, 4).NoteText Text:=TextBox10.Text
    
    If OptionButton1.Value = True Then .Cells(lastRow + 1, 8).Value = "DR": OptionButton1.Value = True
    If OptionButton2.Value = True Then .Cells(lastRow + 1, 8).Value = "IVY": OptionButton2.Value = True
    If OptionButton3.Value = True Then .Cells(lastRow + 1, 8).Value = "N/A": OptionButton3.Value = True
    If OptionButton4.Value = True Then .Cells(lastRow + 1, 6).Value = "EBAY": OptionButton4.Value = True
    If OptionButton5.Value = True Then .Cells(lastRow + 1, 6).Value = "WEB SITE": OptionButton5.Value = True
    If OptionButton6.Value = True Then .Cells(lastRow + 1, 6).Value = "N/A": OptionButton6.Value = True
    If OptionButton7.Value = True Then .Cells(lastRow + 1, 10).Value = "TRACKED 24": OptionButton7.Value = True
    If OptionButton10.Value = True Then .Cells(lastRow + 1, 7).Value = "COLLECTION"
    If OptionButton10.Value = True Then .Cells(lastRow + 1, 10).Value = "COLLECTION": OptionButton10.Value = True
    If OptionButton12.Value = True Then .Cells(lastRow + 1, 9).Value = "N/A": OptionButton12.Value = True
    If OptionButton15.Value = True Then .Cells(lastRow + 1, 11).Value = "YES": OptionButton15.Value = True
    If OptionButton16.Value = True Then .Cells(lastRow + 1, 11).Value = "NO": OptionButton16.Value = True
    If OptionButton17.Value = True Then .Cells(lastRow + 1, 10).Value = "EVRI": OptionButton17.Value = True
    
On Error Resume Next
        
With ThisWorkbook.Worksheets("POSTAGE").Cells(lastRow + 1, 4).Comment
        .Shape.Autoshapetype = msoShapeRoundedRectangle
        .Shape.TextFrame.Characters.Font.NAME = "Times Roman" ' FONT FAMILY STYLE
        .Shape.TextFrame.Characters.Font.Size = 12 ' TEXT SIZE
        .Shape.TextFrame.Characters.Font.ColorIndex = 5 ' TEXT COLOR
        .Shape.LINE.ForeColor.RGB = RGB(0, 0, 0) ' ARROW & LINE COLOR
        .Shape.Fill.Visible = msoTrue
        .Shape.Fill.ForeColor.RGB = RGB(255, 255, 255) ' FILL COLOR
        .Shape.TextFrame.AutoSize = True
        
    End With
    
On Error GoTo 0

Dim colorHTML As String, r As String, g As String, b As String

        TextBox2.Value = ""
        CommonPurchasedItem.Value = ""
        TextBox4.Value = ""
        TextBox6.Value = ""
        TextBox9.Value = ""
        TextBox10.Value = ""
        Application.ScreenUpdating = True
        OptionButton1.Value = False
        OptionButton2.Value = False
        OptionButton3.Value = False
        OptionButton4.Value = False
        OptionButton5.Value = False
        OptionButton6.Value = False
        OptionButton7.Value = False
        OptionButton10.Value = False
        OptionButton12.Value = False
        OptionButton13.Value = False
        OptionButton15.Value = False
        OptionButton16.Value = False

        Application.Goto Sheets("POSTAGE").Range("B" & Rows.count).End(xlUp), True
        Call UserForm_Initialize
        
        If Worksheets("POSTAGE").Cells(lastRow + 1, 11).Value = "" Then MsgBox "THERE ISNT A YES / NO VALUE IN SECURITY CELL", vbCritical, "SECURITY MESSAGE"
  
        TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
        TextBox1.Value = Now
        TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
        TextBox2.Value = ""
        CommonPurchasedItem.Value = ""
        TextBox4.Value = ""
        TextBox6.Value = ""
        TextBox9.Value = ""
        TextBox10.Value = ""
        TextBox2.SetFocus
        
        ListBox2.Clear
        UserForm_Initialize
        End With
      
        answer = MsgBox("IS THERE A PHOTO TO HYPERLINK FOR THIS CUSTOMER ?", vbYesNo + vbInformation, "HYPERLINK PHOTO MESSAGE")
        If answer = vbNo Then
        Exit Sub
        Else
        End If
       
        With ActiveCell
         Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
        If ActiveCell.Column = Columns("B").Column Then
          
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        With ActiveCell
        .Font.Size = 12
        End With
        MsgBox "HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        If MsgBox("THERE IS NO PHOTO FOR THIS CUSTOMER" & vbNewLine & "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?", vbYesNo + vbCritical, "HYPERLINK CUSTOMER PHOTO MESSAGE.") = vbYes Then
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
        End If
 
        End If
        End With
        
        End Sub

 

[Alex Blakenburg]

1) Populating the Text Box:
Note: "Now" is Date AND Time and you only want the Date (which means using Date)
Delete

        TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
        TextBox1.Value = Now
        TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")

You only need:

        TextBox1.Value = Format(Date, "dd/mm/yyyy")

2) Writing to the sheet:
Add CDate

    .Cells(lastRow + 1, 1).Value = CDate(TextBox1.Text)