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"
<(E-Mail Removed)> 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