Help with a form

[Brent Sweet]

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

 

[JLGWhiz]

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]

Actually, that did the trick. Adding the .value did. What exactly does that
do?

 

[JLGWhiz]

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.