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?
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?