Add data to next empty row not working

T

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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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

tigerdel said:
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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I
D
  • Question Question
Error Retrieving data with XLOOKUP after using VBA to copy to source cells.
Replies
6
Views
421
Alex Blakenburg
A
I
  • Question Question
Use the majority of an existing working code in new project advice
Replies
1
Views
220
ipbr21054
I
I
  • Solved
Enter row number in listbox1 when value on worksheet found
2
Replies
10
Views
588
ipbr21054
I
I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
236
ipbr21054
I

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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