Default Date in TextBox

John thanks !

Please see my code below. I am not sure if it is good practice, or possible to use a private sub with an option explicit? Furthermore my code involves a command click.

Now the userform shows at the instant the excel workbook open. I think the problem here is assigning the default date to the tetbox at the instant the excel book opens.

Option Explicit
Public Sub CommandButton1_Click()

Dim constFilePath As String
Dim constFileNameMTMReport As String
Dim constFileNameDataSource As String
Dim constCPSelection As String

'---------------------------------------
txtDateBox.Text = Format(Now(), "dd/mm/yy") 'Default to todays date in textbox. <------not working

constFilePath = "N:\CBI-DEPT\XXXXXXXX\ZZZZZZZZZZ\AAAAAAAASpreadsheet Automation\Product\Excel Automation\SSSSSS\Communication\Portfolio list\"
constFileNameMTMReport = txtCPNameBox.Text & "_mtm_portfolio_report.xls"
constFileNameDataSource = "mtm_report_asof_" & Format(txtDateBox.Text, "yyyymmdd") & ".xls"

  With Workbooks.Add
    .SaveAs constFilePath & constFileNameMTMReport
    Range("A1") = "Cust"
    Range("B1") = "ExternalTradedID"
    Range("C1") = "ProductGroup"
    Range("D1") = "DeskID"
    Range("E1") = "BookID"
    Range("F1") = "Type"
    Range("G1") = "Type"
    Range("H1") = "MaturityDate"
    Range("I1") = "USDNotional"
    Range("J1") = "CcyPair"
    Range("K1") = "RecMTM"
    Range("L1") = "PayMTM"
    Range("M1") = "MTM"
    Range("N1") = "COB"
    Range("O1") = "TradeDate"
    Range("P1") = "Threshold"
    Range("P1") = "Min Trans Amt"
  End With
Workbooks.Open constFilePath & constFileNameDataSource

constCPSelection = Application.InputBox(prompt:="Please highlight (or select) the area you want with your mouse", Type:=8).Address

Workbooks(constFileNameMTMReport).Sheets(1).Cells(2, 1).Resize(Range(constCPSelection).Rows.Count, Range(constCPSelection).Columns.Count) = Workbooks(constFileNameDataSource).Sheets("MTM Detail").Range(constCPSelection).Value

Workbooks(constFileNameMTMReport).Sheets(1).Range("I:I").NumberFormat = "#,##0.00" 'Format to a number
Workbooks(constFileNameMTMReport).Sheets(1).Range("K:M").NumberFormat = "#,##0.00" 'Format to a number
Workbooks(constFileNameMTMReport).Sheets(1).Range("A:Q").AutoFit


End Sub

Charles, thanks. it tried this (see below) and it did not work. Am I missing the point?

Option Explicit
Public Sub CommandButton1_Click()

Dim constFilePath As String
Dim constFileNameMTMReport As String
Dim constFileNameDataSource As String
Dim constCPSelection As String

'---------------------------------------
With UserForm1
.txtDateBox.Text = Format(Now(), "dd/mm/yy")
End With 'Default to todays date in textbox.

Thank you. I can be dense at times. Out of curiousity. How would I add a day to the Now() date or subtract a day? Other than that it works.

Hmmm,

With UserForm1
.txtDateBox.Text = Format(Now() - 1, "dd/mm/yy")
End With 'Default to todays date in textbox

.

yeilds, 08/06/11.

Its all good. I can figure that part out. Thanks.