I have just completed a weeks crash course in VBA programming and would like
to use some of the knowledge I was supposed to learn; however, it isn't
working to well.

We have an Excel spreadsheet that the company requires us to use. It fills
in the names of personnel attending training depending on which shop the are
assigned to. An Excel form was created to input the names and works great.
However, the trainers would like to make the forms up in advance and want to
input a date on the form and have it transfer to the spreadsheet before it
prints.

I have tried to do this, but can't get it to work. The coding so far that
is not working for me is:
Private Sub Execute_Click()
'declare variables
Dim StrRangeDate As String
Dim sht8253Form As Worksheet
Set sht8253Form = Application.Workbooks("OTD
F8253.xls").Worksheets("8253 Form")
'Assign user input txtRangeDate to variables
StrRangeDate = Val(txtRangeDate.Text)
MsgBox (StrRangeDate)

What happens is the if we input "10-21-06" all that shows up in the MsgBox
is 10.
And when we print the spreadsheet form, nothing appears.

Help Please!

Hi there

Not sure what it is you are really asking but I think I may be able to
help a bit.
You could try removing the Val() from the line before the last which
would then show the correct info in the MsgBox.

Private Sub Execute_Click()
'declare variables
Dim StrRangeDate As String
Dim sht8253Form As Worksheet
Set sht8253Form = Application.Workbooks("OTD
F8253.xls").Worksheets("8253 Form")
'Assign user input txtRangeDate to variables
StrRangeDate = txtRangeDate.Text
MsgBox (StrRangeDate)


You may want to set up some formatting to the txtrangedate textbox to
ensure some sort of continuity when the doc prints by adding a line
like the one below to your code before you declare what strrangedate
is.

txtRangeDate = Format$(txtRangeDate.Text, "dd/mmm/yyyy")

Then I guess just pass the string to a cell before you print
Ie range("a1") = StrRangeDate

I hope this is of some help to you.

S

HarryJ wrote:
> I have just completed a weeks crash course in VBA programming and would like
> to use some of the knowledge I was supposed to learn; however, it isn't
> working to well.
>
> We have an Excel spreadsheet that the company requires us to use. It fills
> in the names of personnel attending training depending on which shop the are
> assigned to. An Excel form was created to input the names and works great.
> However, the trainers would like to make the forms up in advance and want to
> input a date on the form and have it transfer to the spreadsheet before it
> prints.
>
> I have tried to do this, but can't get it to work. The coding so far that
> is not working for me is:
> Private Sub Execute_Click()
> 'declare variables
> Dim StrRangeDate As String
> Dim sht8253Form As Worksheet
> Set sht8253Form = Application.Workbooks("OTD
> F8253.xls").Worksheets("8253 Form")
> 'Assign user input txtRangeDate to variables
> StrRangeDate = Val(txtRangeDate.Text)
> MsgBox (StrRangeDate)
>
> What happens is the if we input "10-21-06" all that shows up in the MsgBox
> is 10.
> And when we print the spreadsheet form, nothing appears.
>
> Help Please!

if you look in help at val, you see it stops when it hits the first hyphen.

use cdate instead

StrRangeDate = cdate(txtRangeDate.Text)

however, if you are going to put it into a string variable with no change in
formatting, why not just do

StrRangeDate = txtRangeDate.Text

You haven't added a step to write anything to the spreadsheet form.

Activesheet.Range("B9").Value = StrRangeDate

for example.

--
Regards,
Tom Ogilvy


"HarryJ" wrote:

> I have just completed a weeks crash course in VBA programming and would like
> to use some of the knowledge I was supposed to learn; however, it isn't
> working to well.
>
> We have an Excel spreadsheet that the company requires us to use. It fills
> in the names of personnel attending training depending on which shop the are
> assigned to. An Excel form was created to input the names and works great.
> However, the trainers would like to make the forms up in advance and want to
> input a date on the form and have it transfer to the spreadsheet before it
> prints.
>
> I have tried to do this, but can't get it to work. The coding so far that
> is not working for me is:
> Private Sub Execute_Click()
> 'declare variables
> Dim StrRangeDate As String
> Dim sht8253Form As Worksheet
> Set sht8253Form = Application.Workbooks("OTD
> F8253.xls").Worksheets("8253 Form")
> 'Assign user input txtRangeDate to variables
> StrRangeDate = Val(txtRangeDate.Text)
> MsgBox (StrRangeDate)
>
> What happens is the if we input "10-21-06" all that shows up in the MsgBox
> is 10.
> And when we print the spreadsheet form, nothing appears.
>
> Help Please!

Gentlemen,

Thank You so much.
It is working now. It helps when you have someone to ask questions of.

Again, Thank You!



"Tom Ogilvy" wrote:

> if you look in help at val, you see it stops when it hits the first hyphen.
>
> use cdate instead
>
> StrRangeDate = cdate(txtRangeDate.Text)
>
> however, if you are going to put it into a string variable with no change in
> formatting, why not just do
>
> StrRangeDate = txtRangeDate.Text
>
> You haven't added a step to write anything to the spreadsheet form.
>
> Activesheet.Range("B9").Value = StrRangeDate
>
> for example.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "HarryJ" wrote:
>
> > I have just completed a weeks crash course in VBA programming and would like
> > to use some of the knowledge I was supposed to learn; however, it isn't
> > working to well.
> >
> > We have an Excel spreadsheet that the company requires us to use. It fills
> > in the names of personnel attending training depending on which shop the are
> > assigned to. An Excel form was created to input the names and works great.
> > However, the trainers would like to make the forms up in advance and want to
> > input a date on the form and have it transfer to the spreadsheet before it
> > prints.
> >
> > I have tried to do this, but can't get it to work. The coding so far that
> > is not working for me is:
> > Private Sub Execute_Click()
> > 'declare variables
> > Dim StrRangeDate As String
> > Dim sht8253Form As Worksheet
> > Set sht8253Form = Application.Workbooks("OTD
> > F8253.xls").Worksheets("8253 Form")
> > 'Assign user input txtRangeDate to variables
> > StrRangeDate = Val(txtRangeDate.Text)
> > MsgBox (StrRangeDate)
> >
> > What happens is the if we input "10-21-06" all that shows up in the MsgBox
> > is 10.
> > And when we print the spreadsheet form, nothing appears.
> >
> > Help Please!