Date value

[Patrick C. Simonds]

The user inputs a year Value to TextBox1. How can I amend the code below so
that January 1 of the year is inputted to into the cell?




Private Sub CommandButton1_Click()

Dim rng
Set rng = Cells(ActiveCell.Row, 1)

rng(1, 2).Value = TextBox1.Value

Module2.Rename_Worksheets
Unload Year

End Sub

 

[Ron Rosenfeld]

The user inputs a year Value to TextBox1. How can I amend the code below so
that January 1 of the year is inputted to into the cell?




Private Sub CommandButton1_Click()

Dim rng
Set rng = Cells(ActiveCell.Row, 1)

rng(1, 2).Value = TextBox1.Value

Module2.Rename_Worksheets
Unload Year

End Sub

Use the DateSerial function

For example:

rng(1, 2).Value = DateSerial(TextBox1.Value,1,1)
--ron

 

[JLGWhiz]

If you want it as a date:

rng(1, 2) = CDate("January 1, " & TextBox1.Value)

If you want it as text:

rng(1, 2) = "January 1, " & TextBox1.Value

 

[Dave Peterson]

I bet that excel will see that "text" entry and parse it as a date.

If I wanted text, I'd use:
rng(1, 2) = "'January 1, " & TextBox1.Value '<-- with leading apostrophe

But I'd be more inclined to use:

with rng(1, 2)
.numberformat = "mm/dd/yyyy"
if isnumeric(me.textbox1.value) then
.value = dateserial(me.textbox1.value, 1, 1)
end if
end with

or for text:

with rng(1, 2)
.numberformat = "@"
if isnumeric(me.textbox1.value) then
.value = "January 1, " & me.textbox1.value
end if
end with

(Maybe even add some more validity checks, too.)

 

[JLGWhiz]

Probably so, depending on how he has the range formatted. But I had variable
assignment in mind when I wrote that.

 

[Dave Peterson]

I don't understand what a variable assignment means.

 

[JLGWhiz]

Playing semantics? <g>

myDate = "January 1, " & TextBox1.Value

is a variable assignment. A value is assigned to a variable. You knew what
I meant.

 

[Dave Peterson]

I didn't understand your point. I thought you were answering the question about
putting the date into the cell--not creating a string variable and populating
that with a value.

But I understand what you mean now <bg>.

Playing semantics? <g>

myDate = "January 1, " & TextBox1.Value

is a variable assignment. A value is assigned to a variable. You knew what
I meant.