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

On Sat, 22 Nov 2008 10:26:30 -0800, "Patrick C. Simonds"
wrote:

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

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





"Patrick C. Simonds" wrote:

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

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 wrote:

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

"Patrick C. Simonds" wrote:

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



--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

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

"Patrick C. Simonds" wrote:

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



--

Dave Peterson

I don't understand what a variable assignment means.

JLGWhiz wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Patrick C. Simonds" wrote:

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



--

Dave Peterson


--

Dave Peterson

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" wrote:

I don't understand what a variable assignment means.

JLGWhiz wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Patrick C. Simonds" wrote:

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



--

Dave Peterson


--

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.

JLGWhiz wrote:

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" wrote:

I don't understand what a variable assignment means.

JLGWhiz wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Patrick C. Simonds" wrote:

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



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson