| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form
Greetings,
I need some help. I have a userform that I have created that basically updates the information a person inputs into an excel spreadsheet. We have a tracking database for emails we send and receive. Prior to the form we would just cut and paste everything into an excel spreadsheet. This form really brings efficency to the process. I have one error that I cannot find resolution to. I have two text boxes one needs to be the email that a customer sent us, and one is the email we send back to the client. Some of these are so long that I get a runtime error 1004. Before these would update fine to the spreadsheet, you just had to double click on the cell and paste it. I have put my code below. Is there any adjustment I can make so that the code will update the spreadsheet. Basically the email is cut and pasted into a text box, then an update button is clicked which executes the following code, and takes each of the text boxes and updates it to their respective cell. Private Sub CommandButton1_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'update respective cells from the form ws.Cells(iRow, 3).Value = Date ws.Cells(iRow, 2).Value = TextBox7 ws.Cells(iRow, 1).Value = TextBox8 ws.Cells(iRow, 4).Value = TextBox1 ws.Cells(iRow, 5).Value = TextBox2 ws.Cells(iRow, 6).Value = TextBox3 ws.Cells(iRow, 8).Value = TextBox9 ws.Cells(iRow, 9).Value = TextBox5 ws.Cells(iRow, 10).Value = TextBox6 ws.Cells(iRow, 7).Value = ComboBox1 'clear the form for next entry TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" TextBox9.Value = "" TextBox5.Value = "" TextBox6.Value = "" TextBox7.Value = "" TextBox8.Value = "" ComboBox1.Value = "" End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a form
Your problem description is a little ambiguous, but I can offer one
suggestion. Add .Value after your control names: ws.Cells(iRow, 3).Value = Date ws.Cells(iRow, 2).Value = TextBox7.Value ws.Cells(iRow, 1).Value = TextBox8.Value ws.Cells(iRow, 4).Value = TextBox1.Value ws.Cells(iRow, 5).Value = TextBox2.Value ws.Cells(iRow, 6).Value = TextBox3.Value ws.Cells(iRow, 8).Value = TextBox9.Value ws.Cells(iRow, 9).Value = TextBox5.Value ws.Cells(iRow, 10).Value = TextBox6.Value ws.Cells(iRow, 7).Value = ComboBox1.Value You mention that you have two text boxes that handle email that cause a problem and then your code lists eight textboxes and a combo box. You mention that you get a 1004 runtime error but don't say what the message is. If you could be a little more specific about which textboxes, by number, cause the problem, and what the error message says, maybe one of us can offer a better solution. "Brent Sweet" wrote: Greetings, I need some help. I have a userform that I have created that basically updates the information a person inputs into an excel spreadsheet. We have a tracking database for emails we send and receive. Prior to the form we would just cut and paste everything into an excel spreadsheet. This form really brings efficency to the process. I have one error that I cannot find resolution to. I have two text boxes one needs to be the email that a customer sent us, and one is the email we send back to the client. Some of these are so long that I get a runtime error 1004. Before these would update fine to the spreadsheet, you just had to double click on the cell and paste it. I have put my code below. Is there any adjustment I can make so that the code will update the spreadsheet. Basically the email is cut and pasted into a text box, then an update button is clicked which executes the following code, and takes each of the text boxes and updates it to their respective cell. Private Sub CommandButton1_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'update respective cells from the form ws.Cells(iRow, 3).Value = Date ws.Cells(iRow, 2).Value = TextBox7 ws.Cells(iRow, 1).Value = TextBox8 ws.Cells(iRow, 4).Value = TextBox1 ws.Cells(iRow, 5).Value = TextBox2 ws.Cells(iRow, 6).Value = TextBox3 ws.Cells(iRow, 8).Value = TextBox9 ws.Cells(iRow, 9).Value = TextBox5 ws.Cells(iRow, 10).Value = TextBox6 ws.Cells(iRow, 7).Value = ComboBox1 'clear the form for next entry TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" TextBox9.Value = "" TextBox5.Value = "" TextBox6.Value = "" TextBox7.Value = "" TextBox8.Value = "" ComboBox1.Value = "" End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a form
Actually, that did the trick. Adding the .value did. What exactly does that
do? "JLGWhiz" wrote: Your problem description is a little ambiguous, but I can offer one suggestion. Add .Value after your control names: ws.Cells(iRow, 3).Value = Date ws.Cells(iRow, 2).Value = TextBox7.Value ws.Cells(iRow, 1).Value = TextBox8.Value ws.Cells(iRow, 4).Value = TextBox1.Value ws.Cells(iRow, 5).Value = TextBox2.Value ws.Cells(iRow, 6).Value = TextBox3.Value ws.Cells(iRow, 8).Value = TextBox9.Value ws.Cells(iRow, 9).Value = TextBox5.Value ws.Cells(iRow, 10).Value = TextBox6.Value ws.Cells(iRow, 7).Value = ComboBox1.Value You mention that you have two text boxes that handle email that cause a problem and then your code lists eight textboxes and a combo box. You mention that you get a 1004 runtime error but don't say what the message is. If you could be a little more specific about which textboxes, by number, cause the problem, and what the error message says, maybe one of us can offer a better solution. "Brent Sweet" wrote: Greetings, I need some help. I have a userform that I have created that basically updates the information a person inputs into an excel spreadsheet. We have a tracking database for emails we send and receive. Prior to the form we would just cut and paste everything into an excel spreadsheet. This form really brings efficency to the process. I have one error that I cannot find resolution to. I have two text boxes one needs to be the email that a customer sent us, and one is the email we send back to the client. Some of these are so long that I get a runtime error 1004. Before these would update fine to the spreadsheet, you just had to double click on the cell and paste it. I have put my code below. Is there any adjustment I can make so that the code will update the spreadsheet. Basically the email is cut and pasted into a text box, then an update button is clicked which executes the following code, and takes each of the text boxes and updates it to their respective cell. Private Sub CommandButton1_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'update respective cells from the form ws.Cells(iRow, 3).Value = Date ws.Cells(iRow, 2).Value = TextBox7 ws.Cells(iRow, 1).Value = TextBox8 ws.Cells(iRow, 4).Value = TextBox1 ws.Cells(iRow, 5).Value = TextBox2 ws.Cells(iRow, 6).Value = TextBox3 ws.Cells(iRow, 8).Value = TextBox9 ws.Cells(iRow, 9).Value = TextBox5 ws.Cells(iRow, 10).Value = TextBox6 ws.Cells(iRow, 7).Value = ComboBox1 'clear the form for next entry TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" TextBox9.Value = "" TextBox5.Value = "" TextBox6.Value = "" TextBox7.Value = "" TextBox8.Value = "" ComboBox1.Value = "" End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a form
One analogy would be the difference between going to the market and going to
the market for groceries. Textbox is the market, Value is the groceries. "Brent Sweet" wrote: Actually, that did the trick. Adding the .value did. What exactly does that do? "JLGWhiz" wrote: Your problem description is a little ambiguous, but I can offer one suggestion. Add .Value after your control names: ws.Cells(iRow, 3).Value = Date ws.Cells(iRow, 2).Value = TextBox7.Value ws.Cells(iRow, 1).Value = TextBox8.Value ws.Cells(iRow, 4).Value = TextBox1.Value ws.Cells(iRow, 5).Value = TextBox2.Value ws.Cells(iRow, 6).Value = TextBox3.Value ws.Cells(iRow, 8).Value = TextBox9.Value ws.Cells(iRow, 9).Value = TextBox5.Value ws.Cells(iRow, 10).Value = TextBox6.Value ws.Cells(iRow, 7).Value = ComboBox1.Value You mention that you have two text boxes that handle email that cause a problem and then your code lists eight textboxes and a combo box. You mention that you get a 1004 runtime error but don't say what the message is. If you could be a little more specific about which textboxes, by number, cause the problem, and what the error message says, maybe one of us can offer a better solution. "Brent Sweet" wrote: Greetings, I need some help. I have a userform that I have created that basically updates the information a person inputs into an excel spreadsheet. We have a tracking database for emails we send and receive. Prior to the form we would just cut and paste everything into an excel spreadsheet. This form really brings efficency to the process. I have one error that I cannot find resolution to. I have two text boxes one needs to be the email that a customer sent us, and one is the email we send back to the client. Some of these are so long that I get a runtime error 1004. Before these would update fine to the spreadsheet, you just had to double click on the cell and paste it. I have put my code below. Is there any adjustment I can make so that the code will update the spreadsheet. Basically the email is cut and pasted into a text box, then an update button is clicked which executes the following code, and takes each of the text boxes and updates it to their respective cell. Private Sub CommandButton1_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'update respective cells from the form ws.Cells(iRow, 3).Value = Date ws.Cells(iRow, 2).Value = TextBox7 ws.Cells(iRow, 1).Value = TextBox8 ws.Cells(iRow, 4).Value = TextBox1 ws.Cells(iRow, 5).Value = TextBox2 ws.Cells(iRow, 6).Value = TextBox3 ws.Cells(iRow, 8).Value = TextBox9 ws.Cells(iRow, 9).Value = TextBox5 ws.Cells(iRow, 10).Value = TextBox6 ws.Cells(iRow, 7).Value = ComboBox1 'clear the form for next entry TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" TextBox9.Value = "" TextBox5.Value = "" TextBox6.Value = "" TextBox7.Value = "" TextBox8.Value = "" ComboBox1.Value = "" End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Call user form from ThisWorkbook; close file if form closed | Excel Programming | |||
| Pass variable from form to form to form | Excel Programming | |||
| Strange issue freezing parent form when unloading a child form | Excel Programming | |||
| Help! Animated gif-image in form does not show animation when form loaded | Excel Programming | |||
| Is it possible to open the VBA form with a link in a sheet and to pass variable from a cell to the VBA form? | Excel Programming | |||
Linear Mode
