Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Take Data from XL Form and input to spreadsheet?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Take Data from XL Form and input to spreadsheet?
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Take Data from XL Form and input to spreadsheet?
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Take Data from XL Form and input to spreadsheet?
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel form. Need data extracted to spreadsheet each time a form co | Excel Discussion (Misc queries) | |||
How do I create a User form to input Data into an Ecel spreadsheet | Excel Programming | |||
Data Input - User Form Drop-Down Box | Excel Programming | |||
Using a form to input data into a schedule | Excel Programming | |||
Data Input Execl Form | Excel Programming |