Add data to next empty row not working

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
145
Office Version
  1. 365
Platform
  1. Windows
Hi Guys

This code is what I am using to add Data recorded from a userform to the next empty row in a sheet

It was working but now nothing is being added to the chosen sheet

Do one of you amazing guys out there have any ideas why???

Rich (BB code):
Private Sub cmbSave_Click()
Dim LastRow As Long, ws As Worksheet
Set ws = ActiveSheet
LastRow = ws.Range("a" & Rows.Count).End(xlUp).Row +1
If cmbStatus.Value = "" Then
MsgBox "You mustadd a Document Status", vbCritical
cmbStatus.SetFocus
Exit Sub
Else
If txtTitle.Value = "" Then
MsgBox "You mustenter a Title", vbCritical
txtTitle.SetFocus
Exit Sub
Else
If txtDate.Value = "" Then
MsgBox "You mustenter a Date", vbCritical
txtDate.SetFocus
Exit Sub
Else
If txtAuthor.Value = "" Then
MsgBox "You mustenter the Author's name", vbCritical
txtAuthor.SetFocus
Exit Sub
Else
If cmbReview.Value = "" Then
MsgBox "you mustselect the number of Reviewers", vbCritical
cmbReview.SetFocus
Exit Sub
If txtReviewer1.Visible = True And txtReviewer1.Value ="" Then
MsgBox "You mustenter the Reviewer's Name", vbCritical
txtReviewer1.SetFocus
Exit Sub
Else
If txtReviewer2.Visible = True And txtReviewer1.Value ="" Then
MsgBox "You mustenter the Reviewer's Name", vbCritical
txtReviewer2.SetFocus
Exit Sub
Else
If txtReviewer3.Visible = True And txtReviewer1.Value ="" Then
MsgBox "You mustenter the Reviewer's Name", vbCritical
txtReviewer3.SetFocus
Exit Sub
Else
If cmbStatus.Value = "Approved" And txtApprover.Value ="" Then
MsgBox "You mustenter the name of the Approver", vbCritical
txtApprover.SetFocus
Exit Sub
Else
If cmbStatus.Value = "Approved" AndtxtDateApproved.Value = "" Then
MsgBox "You mustenter the date of the Approval", vbCritical
txtDateApproved.SetFocus
Exit Sub
Else
'Call unprotect_sheet
ws.Range("A" & LastRow).Value = Me.lblRef.Caption
ws.Range("B" & LastRow).Value = Me.txtVersion.Value
ws.Range("C" & LastRow).Value = Me.cmbStatus.Value
ws.Range("D" & LastRow).Value = Me.txtTitle.Value
ws.Range("E" & LastRow).Value = Me.txtDate.Value
ws.Range("F" & LastRow).Value = Me.txtAuthor.Value
ws.Range("G" & LastRow).Value =Me.txtReviewer1.Value
ws.Range("H" & LastRow).Value =Me.txtReviewer2.Value
ws.Range("I" & LastRow).Value =Me.txtReviewer3.Value
ws.Range("J" & LastRow).Value = Me.txtApprover.Value
ws.Range("K" & LastRow).Value =Me.txtDateApproved.Value
ws.Range("L" & LastRow).Value =Me.txtMasterLink.Value
ws.Range("M" & LastRow).Value = Me.txtDocLink.Value
ws.Range("S" & LastRow).Value = Me.txtRefNum.Value
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Columns("A:M").Select
Columns("A:M").EntireColumn.AutoFit
Range("A1").Select
'    Call protect_sheet
Unload Me

Many Thanks

Derek
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
1. Is the active sheet actually the one you want the data to be added to?
2. Why is the call to unprotect commented out?
 
Upvote 0
Hey Jan
1.Yes the Active Sheet is the sheet to be added to
2.Unprotect has been commented out because the sheet is no longer protected due to user rights
 
Upvote 0
Missing "Else" from code as marked

Thanks for looking though

Derek


Hi Guys

This code is what I am using to add Data recorded from a userform to the next empty row in a sheet

It was working but now nothing is being added to the chosen sheet

Do one of you amazing guys out there have any ideas why???

Rich (BB code):
Private Sub cmbSave_Click()
Dim LastRow As Long, ws As Worksheet
Set ws = ActiveSheet
LastRow = ws.Range("a" & Rows.Count).End(xlUp).Row +1
If cmbStatus.Value = "" Then
MsgBox "You mustadd a Document Status", vbCritical
cmbStatus.SetFocus
Exit Sub
Else
If txtTitle.Value = "" Then
MsgBox "You mustenter a Title", vbCritical
txtTitle.SetFocus
Exit Sub
Else
If txtDate.Value = "" Then
MsgBox "You mustenter a Date", vbCritical
txtDate.SetFocus
Exit Sub
Else
If txtAuthor.Value = "" Then
MsgBox "You mustenter the Author's name", vbCritical
txtAuthor.SetFocus
Exit Sub
Else
If cmbReview.Value = "" Then
MsgBox "you mustselect the number of Reviewers", vbCritical
cmbReview.SetFocus
Exit Sub
If txtReviewer1.Visible = True And txtReviewer1.Value ="" Then
MsgBox "You mustenter the Reviewer's Name", vbCritical
txtReviewer1.SetFocus
Exit Sub
Else
If txtReviewer2.Visible = True And txtReviewer1.Value ="" Then
MsgBox "You mustenter the Reviewer's Name", vbCritical
txtReviewer2.SetFocus
Exit Sub
Else
If txtReviewer3.Visible = True And txtReviewer1.Value ="" Then
MsgBox "You mustenter the Reviewer's Name", vbCritical
txtReviewer3.SetFocus
Exit Sub
Else
If cmbStatus.Value = "Approved" And txtApprover.Value ="" Then
MsgBox "You mustenter the name of the Approver", vbCritical
txtApprover.SetFocus
Exit Sub
Else
If cmbStatus.Value = "Approved" AndtxtDateApproved.Value = "" Then
MsgBox "You mustenter the date of the Approval", vbCritical
txtDateApproved.SetFocus
Exit Sub
Else
'Call unprotect_sheet
ws.Range("A" & LastRow).Value = Me.lblRef.Caption
ws.Range("B" & LastRow).Value = Me.txtVersion.Value
ws.Range("C" & LastRow).Value = Me.cmbStatus.Value
ws.Range("D" & LastRow).Value = Me.txtTitle.Value
ws.Range("E" & LastRow).Value = Me.txtDate.Value
ws.Range("F" & LastRow).Value = Me.txtAuthor.Value
ws.Range("G" & LastRow).Value =Me.txtReviewer1.Value
ws.Range("H" & LastRow).Value =Me.txtReviewer2.Value
ws.Range("I" & LastRow).Value =Me.txtReviewer3.Value
ws.Range("J" & LastRow).Value = Me.txtApprover.Value
ws.Range("K" & LastRow).Value =Me.txtDateApproved.Value
ws.Range("L" & LastRow).Value =Me.txtMasterLink.Value
ws.Range("M" & LastRow).Value = Me.txtDocLink.Value
ws.Range("S" & LastRow).Value = Me.txtRefNum.Value
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Columns("A:M").Select
Columns("A:M").EntireColumn.AutoFit
Range("A1").Select
'    Call protect_sheet
Unload Me

Many Thanks

Derek
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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