Reputation: 2412
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?
Here are the properties:
Upvotes: 0
Views: 953
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
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