CODEWITHSUNDEEP
excel
10101
10101

Reputation: 2412

ActiveX textbox displayes dates as number

I have ActiveX textbox and linked cell that is I21. I21 is set to be a date but in ActiveX textbox I see number. What is wrong here?

enter image description here

Here are the properties:

enter image description here

Upvotes: 0

Views: 953

Answers (2)

10101
10101

Reputation: 2412

Variatus suggestion works fine. However I have tried myself another approach and decided to use this one. Originally posted here

Insert this to standard module:

Public Function FMT$(ByVal Value, ByVal strFormat)
    FMT = VBA.Format$(Value, strFormat)
End Function

Then convert date by using this formula for example in cell I22: =FMT(I21;"dd.mm.yyyy")

Finally link ActiveX textbox to I22. Your date should be displayed in the correct way.

Upvotes: 0

Variatus
Variatus

Reputation: 14383

I found no way to copy the format from the linked cell to the control, nor to directly set a format for the control. Therefore the only way I could figure is to use the Worksheet Change event to fill the TextBox control with the value on the worksheet. Unfortunately, this is a one-way street.

Paste the code below to the code module of the worksheet on which you have your textbox. Leave the ActiveX control's LinkedCell property blank.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LinkedCell As Range

    Set LinkedCell = Range("I21")
    With Target
        If .Address = LinkedCell.Address Then
            Me.TextBox1.Value = Format(LinkedCell.Value, "dd mmm yyyy")
        End If
    End With
End Sub

Upvotes: 1

Related Questions