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

User · Accepted Answer

Use the range s NumberFormat property to force the format of the range like this   Sheet1 Range  A2    A50000   NumberFormat    yyyy-mm-dd

User · Answer

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

User · Answer

It works  when you use both lines   Application ActiveWorkbook Worksheets  data   Range  C1    C20000     Format Date   yyyy-mm-dd   Application ActiveWorkbook Worksheets  data   Range  C1    C20000   NumberFormat    yyyy-mm-dd

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

Examples related to excel

Examples related to vba