Userform keeps adding data to the first row!!!

SIR JONES

New Member
Joined
Mar 3, 2005
Messages
12
HELLO EVERYONE!! I built a Userform to enter information to a spreadsheet. The problem I am having is the info will not always go to the next available empty row. It comes back to the first row after any data is entered in the 3rd or forth column. Its driving me crazy and I new at writing codes. Here is what me code looks like:

Private Sub CmdOK_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = txtRA.Text
LastRow.Offset(1, 1).Value = txtJB.Text
LastRow.Offset(1, 2).Value = txtRH.Text
LastRow.Offset(1, 3).Value = txtGD.Text
LastRow.Offset(1, 4).Value = txtGK.Text
LastRow.Offset(1, 5).Value = txtAS.Text
LastRow.Offset(1, 6).Value = txtCJ.Text
LastRow.Offset(1, 7).Value = txtNQ1.Text
LastRow.Offset(1, 8).Value = txtNQ2.Text
LastRow.Offset(1, 9).Value = txtNQ3.Text
LastRow.Offset(1, 10).Value = txtA.Text
LastRow.Offset(1, 11).Value = txtCC.Text
LastRow.Offset(1, 12).Value = txtC.Text
LastRow.Offset(1, 13).Value = txtDTV.Text
LastRow.Offset(1, 14).Value = txtFE.Text
LastRow.Offset(1, 15).Value = txtNSTAR.Text
LastRow.Offset(1, 16).Value = txtNYSEG.Text
LastRow.Offset(1, 17).Value = txtCAP.Text
LastRow.Offset(1, 18).Value = txtPC.Text
LastRow.Offset(1, 19).Value = txtSCG.Text
LastRow.Offset(1, 20).Value = cboPROJECT.Text
LastRow.Offset(1, 21).Value = cboMONTH.Text
LastRow.Offset(1, 22).Value = txtNAME.Text
LastRow.Offset(1, 23).Value = txtMONITOR.Text
LastRow.Offset(1, 24).Value = cboINCIDENT.Text
LastRow.Offset(1, 25).Value = cboSUP.Text
LastRow.Offset(1, 26).Value = txtCORRECTED.Text

MsgBox "One record entered"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
txtRA.Text = ""
txtJB.Text = ""
txtRH.Text = ""
txtGD.Text = ""
txtGK.Text = ""
txtAS.Text = ""
txtCJ.Text = ""
txtNQ1.Text = ""
txtNQ2.Text = ""
txtNQ3.Text = ""
txtA.Text = ""
txtCC.Text = ""
txtC.Text = ""
txtDTV.Text = ""
txtFE.Text = ""
txtNSTAR.Text = ""
txtNYSEG.Text = ""
txtCAP.Text = ""
txtPC.Text = ""
txtSCG.Text = ""
cboPROJECT.Text = ""
cboMONTH.Text = ""
txtNAME.Text = ""
txtMONITOR.Text = ""
cboINCIDENT.Text = ""
cboSUP.Text = ""
txtCORRECTED.Text = ""

txtRA.SetFocus

Else
Unload Me
End If

End Sub



Can some please help!! You can also email me at chukujones234@msn.com
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
i think you should change
Code:
 Set LastRow = Sheet1.Range("a65536").End(xlUp)
to
Code:
 Set LastRow = Sheet1.Range("a1").End(xlDown)

Also, is Sheet1 a valid object?
 
Upvote 0
Hiya,

Im using exactly the same code for a user form and mine works fine. The only differences is when you ask the user for the next record. Instead of trying to blank out your combo/text boxes, why not just reload the original form.

ie. MsgBox "Do you want to enter another record?",vbokYesNo,"New Record?"

If vbYes then
unload me
userform1.show

if vbNo then
unload me

end if
end sub

Thats what i do - works a treat.

Lisa
 
Upvote 0
I tried all you suggessions but still getting the same problem. Maybe its my excel sheet?

What do you mean: "is sheet1 a real object?" that may be the problem...
 
Upvote 0
Hi,

Have you renamed your sheet 1 at all? If so exchange sheet1 for Sheets("Sheet Name").range...cont

Just a suggestion.

Lisa
 
Upvote 0
This form is a night mare

I really appreciate all your help. I just spent $60.00 dollars explaining the problem to a consultant who wanted me to spend another $150.00 for him to fix my problem. He is a nice guy but now you see why I value your sugessions. This is what the macro looks like right now. I still have the same problem. Any thim I enter data for the first name "Rob" on my form, it inputs the data into the next available row and all is fine. However, if I enter data for anyone else in column B - n, the previous entry is replaced. It is not entering data to the next row for anyone else but "Rob" who is in column "a". The consultant said something about my range....

Here is the macro:

Private Sub CmdOK_Click()
Dim Row As Object

Set Row = Sheets("DATABASE").Range("a1").End(xlDown)

Row.Offset(1, 0).Value = txtRA.Text
Row.Offset(1, 1).Value = txtJB.Text
Row.Offset(1, 2).Value = txtRH.Text
Row.Offset(1, 3).Value = txtGD.Text
Row.Offset(1, 4).Value = txtGK.Text
Row.Offset(1, 5).Value = txtAS.Text
Row.Offset(1, 6).Value = txtCJ.Text
Row.Offset(1, 7).Value = txtNQ1.Text
Row.Offset(1, 8).Value = txtNQ2.Text
Row.Offset(1, 9).Value = txtNQ3.Text
Row.Offset(1, 10).Value = txtA.Text
Row.Offset(1, 11).Value = txtCC.Text
Row.Offset(1, 12).Value = txtC.Text
Row.Offset(1, 13).Value = txtDTV.Text
Row.Offset(1, 14).Value = txtFE.Text
Row.Offset(1, 15).Value = txtNSTAR.Text
Row.Offset(1, 16).Value = txtNYSEG.Text
Row.Offset(1, 17).Value = txtCAP.Text
Row.Offset(1, 18).Value = txtPC.Text
Row.Offset(1, 19).Value = txtSCG.Text
Row.Offset(1, 20).Value = cboPROJECT.Text
Row.Offset(1, 21).Value = cboMONTH.Text
Row.Offset(1, 22).Value = txtNAME.Text
Row.Offset(1, 23).Value = txtMONITOR.Text
Row.Offset(1, 24).Value = cboINCIDENT.Text
Row.Offset(1, 25).Value = cboSUP.Text
Row.Offset(1, 26).Value = txtCORRECTED.Text

MsgBox "Do you want to enter another record?", vbYesNo

If vbYes Then
Unload Me
INSITE.Show

Else
Unload Me
End If

End Sub
 
Upvote 0
I give you credit for trying to figure this out on your own, and for not paying some "consultant" to tell you who is buried in Grant's tomb.

If your first column of data is column A, try this.



Private Sub CmdOK_Click()

Dim NextRow As Long
NextRow = Sheet1.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row + 1

Cells(NextRow, 1).Value = txtRA.Text
Cells(NextRow, 2).Value = txtJB.Text
Cells(NextRow, 3).Value = txtRH.Text
Cells(NextRow, 4).Value = txtGD.Text
Cells(NextRow, 5).Value = txtGK.Text
Cells(NextRow, 6).Value = txtAS.Text
Cells(NextRow, 7).Value = txtCJ.Text
Cells(NextRow, 8).Value = txtNQ1.Text
Cells(NextRow, 9).Value = txtNQ2.Text
Cells(NextRow, 10).Value = txtNQ3.Text
Cells(NextRow, 11).Value = txtA.Text
Cells(NextRow, 12).Value = txtCC.Text
Cells(NextRow, 13).Value = txtC.Text
Cells(NextRow, 14).Value = txtDTV.Text
Cells(NextRow, 15).Value = txtFE.Text
Cells(NextRow, 16).Value = txtNSTAR.Text
Cells(NextRow, 17).Value = txtNYSEG.Text
Cells(NextRow, 18).Value = txtCAP.Text
Cells(NextRow, 19).Value = txtPC.Text
Cells(NextRow, 20).Value = txtSCG.Text
Cells(NextRow, 21).Value = cboPROJECT.Text
Cells(NextRow, 22).Value = cboMONTH.Text
Cells(NextRow, 23).Value = txtNAME.Text
Cells(NextRow, 24).Value = txtMONITOR.Text
Cells(NextRow, 25).Value = cboINCIDENT.Text
Cells(NextRow, 26).Value = cboSUP.Text
Cells(NextRow, 27).Value = txtCORRECTED.Text

MsgBox "One record entered"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then

NextRow = NextRow + 1

txtRA.Text = ""
txtJB.Text = ""
txtRH.Text = ""
txtGD.Text = ""
txtGK.Text = ""
txtAS.Text = ""
txtCJ.Text = ""
txtNQ1.Text = ""
txtNQ2.Text = ""
txtNQ3.Text = ""
txtA.Text = ""
txtCC.Text = ""
txtC.Text = ""
txtDTV.Text = ""
txtFE.Text = ""
txtNSTAR.Text = ""
txtNYSEG.Text = ""
txtCAP.Text = ""
txtPC.Text = ""
txtSCG.Text = ""
cboPROJECT.Text = ""
cboMONTH.Text = ""
txtNAME.Text = ""
txtMONITOR.Text = ""
cboINCIDENT.Text = ""
cboSUP.Text = ""
txtCORRECTED.Text = ""

txtRA.SetFocus

Else
Unload Me
End If

End Sub
 
Upvote 0
YES!!

Tom!! You are the man!! Thank you so much for the help. I see what I was doing wrong now and after I used you macro and it works great! I am for ever greatful!! Thanks a bunch for all your help!!


:biggrin: :biggrin: :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top