VBA debug points to End Sub

darrylbster

New Member
Joined
Aug 8, 2016
Messages
42
Hi Everyone.

I have a VBA code that will allow users to correct an error in a spreadsheet by hiding the eroneous row and pasting to the bottom for correction. This is required as a result of some auto tagging that takes place in the spreadhseet, once the tag has been assigned it CAN NOT update.

I made this button, and it worked perfect.


Private Sub CommandButton1_Click()
'Step1: Unprotect the sheet so changes can be made
Sheet1.Unprotect Password:="password"
'Step2: select the erroneous row and copy the contents
ActiveCell.EntireRow.Copy
'Step3: hide the erroneous row and copt the contents
ActiveCell.EntireRow.Hidden = True
'Step4: Jump to the first available blank cell and paste contents from erronaous cell
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
'Step5: Paste contents of Erroneous row
Paste
'Step6: Step 4 will paste the erraneous row as hidden, unhide the row
ActiveCell.EntireRow.Hidden = False
'Step7: Step 4 Deselect All
SendKeys "{ESC}"
'Step8: Re-Protect sheet to prevent Jack-Assery
Sheet1.Protect Password:="password"
End Sub


But then I saw need for deleting rows, I won't get into it, but if the user started adding something, then corrected another, then realized the preceding row was not required...it was stuck there. So I made the following. And it seems to work fine..


Private Sub CommandButton2_Click()
'Step1: Unprotect the sheet so changes can be made
Sheet1.Unprotect Password:="th3w0rk!sm!n3"
'Step2: select the erroneous row and delete
Rows(ActiveCell.Row).Delete
'Step3: Re-Protect sheet to prevent Jack-Assery
Sheet1.Protect Password:="th3w0rk!sm!n3"
End Sub


The I realized that there may be a time that you THINK you needed a revision, or you THINK you needed to wipe the last row, but were mistaken. So I made this..


Private Sub CommandButton3_Click()
'Step1: Unprotect the sheet so changes can be made
Sheet1.Unprotect Password:="th3w0rk!sm!n3"
'Step2: unhide the row
Selection.EntireRow.Hidden = False
'Step3: Re-Protect sheet to prevent Jack-Assery
Sheet1.Protect Password:="th3w0rk!sm!n3"
End Sub




For some reason, this will work through one run,m the next run or so will throw an error.

"Code Excecution has been interupted"

When I debug the highlighted text is "End Sub"...usuallt in button 1. If I step out of debug everything will work fine again for a few runs.

Any ideas?
 

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