VBA code to set date format for a specific column as "yyyy-mm-dd"

Question

I have a macro which I specify the date (in mm/dd/yyyy) in a textbox and I want to set this value for column A in yyyy-mm-dd format. I have the following code:

Sheets("Sheet1").Range("A2", "A50000").Value = TextBox3.Value Sheet1.Range("A2", "A50000") = Format(Date, "yyyy-mm-dd") 

...and when I run the macro, the date is still in mm/dd/yyyy format.

How can I change this so that it is in the format I want?? I've been trying many kinds of code researched through google and nothing will set the format the way I want it.

Any help will be appreciated...

EDIT: Full code from OP's comment below:

 Workbooks.Add  Range("A1") = "Acctdate"  Range("B1") = "Ledger"  Range("C1") = "CY"  Range("D1") = "BusinessUnit"  Range("E1") = "OperatingUnit"  Range("F1") = "LOB"  Range("G1") = "Account"  Range("H1") = "TreatyCode"  Range("I1") = "Amount"  Range("J1") = "TransactionCurrency"  Range("K1") = "USDEquivalentAmount"  Range("L1") = "KeyCol"  Sheets("Sheet1").Range("A2", "A50000").Value = TextBox3.Value  Sheet1.Range("A2", "A50000").NumberFormat = "yyyy-mm-dd" 

Answer 1

Use the range's NumberFormat property to force the format of the range like this:

Sheet1.Range("A2", "A50000").NumberFormat = "yyyy-mm-dd" 

Answer 2

You are applying the formatting to the workbook that has the code, not the added workbook. You'll want to get in the habit of fully qualifying sheet and range references. The code below does that and works for me in Excel 2010:

Sub test() Dim wb As Excel.Workbook Set wb = Workbooks.Add With wb.Sheets(1)     .Range("A1") = "Acctdate"     .Range("B1") = "Ledger"     .Range("C1") = "CY"     .Range("D1") = "BusinessUnit"     .Range("E1") = "OperatingUnit"     .Range("F1") = "LOB"     .Range("G1") = "Account"     .Range("H1") = "TreatyCode"     .Range("I1") = "Amount"     .Range("J1") = "TransactionCurrency"     .Range("K1") = "USDEquivalentAmount"     .Range("L1") = "KeyCol"     .Range("A2", "A50000").Value = Me.TextBox3.Value     .Range("A2", "A50000").NumberFormat = "yyyy-mm-dd" End With End Sub