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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
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?
 

lisauk5583

Board Regular
Joined
Dec 1, 2004
Messages
56
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
 

SIR JONES

New Member
Joined
Mar 3, 2005
Messages
12
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...
 

lisauk5583

Board Regular
Joined
Dec 1, 2004
Messages
56

ADVERTISEMENT

Hi,

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

Just a suggestion.

Lisa
 

SIR JONES

New Member
Joined
Mar 3, 2005
Messages
12
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
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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
 

SIR JONES

New Member
Joined
Mar 3, 2005
Messages
12
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,901
Messages
5,598,766
Members
414,259
Latest member
beetle12

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
Top