VBA - how to change Text Box to date format

[yandy]

Hi,

I need help with the text box changing to the date format.

I have 8 text boxes in sheet1, which link to the cell of B6 to B13 on Sheet 2, and I named the text boxes from "TextBoxSD1" to "TextBoxSD8", how can I mass change the text in the text boxes in date format? Do I need to specify the text boxes on sheet 1?


e.g. Date input on Sheet 2: 02/11/23
Date showing on Textbox on Sheet1: 45232


Here is the code I tried to write:

Sub Date_Format()
Dim i As Long
For i = 6 To 13
    Sheet2.Cells(i, 2) = Format(Me.Controls("TextBoxSD" & i - 5, "dd/mm/yy")
Next i
End Sub

[anish.ms]

attaching a sample file would help.
I tried your example, and the text box appears to be in date format for me.

[yandy]

Tried many times but still came out with error. See attached.

[p45cal]

Short answer:

Sheet2.Cells(i, 2) = Format(Sheet1.OLEObjects("TextBoxSD" & i - 5).Object.Value, "dd/mm/yy")

Long answer:
Problems.
You're going to end up with plain text in those cells, not real dates.
Compounded by you already having each of the textbox's linked cell set as those cells. Changing what's in those cells will change what's in the text boxes and vice versa.

So what are you trying to do?

A guess: Unlink all the textboxes linked cells and use:

Sheet1.OLEObjects("TextBoxSD" & i - 5).Object.Value = Format(Sheet2.Cells(i, 2), "dd/mm/yy")

in your loop

[yandy]

Now I got it. The issue was due to my linked cells.

It works now with your solution provided thanks a lot