Hi,
At the heart of the solution you asked for is this code, contained in a normal code module:
- Code: Select all
Option Explicit
Option Base 0
Const PW As String = "Password"
Public Enum Act
ActAdd = 1
ActDel
End Enum
Public Sub AddOrDeleteRow(ByVal ActWhat As Long)
Dim Ws() As Variant
Dim R As Long
Dim i As Integer
Ws = Array("Sheet1", "Sheet2")
With Selection
If .Rows.Count > 1 Then Exit Sub
R = .Row
End With
For i = LBound(Ws) To UBound(Ws)
With Worksheets(Ws(i))
.Unprotect PW
If ActWhat = ActDel Then
.Rows(R).Delete
Else
.Rows(R).Insert
End If
.Protect Password:=PW, UserInterfaceOnly:=True
End With
Next i
End Sub
At the top of the module you will find your password. I have put it there so that you can put it somewhere else. It shouldn't be in the code.
Below that you find a Public enum, assigning the value 1 to ActAdd and 2 to ActDel. 2 isn't declared because the next enum always has the next number.
This enum is used to communicate with the buttons. In the code sheet of Sheet1 you have the following code:
- Code: Select all
Option Explicit
Private Sub CmdAdd_Click()
AddOrDeleteRow ActAdd
End Sub
Private Sub CmdDelete_Click()
AddOrDeleteRow ActDel
End Sub
Both buttons on the worksheet call the same routine but with a different argument. The Add Row button will send an ActAdd (=1), the Delete Row button an ActDel (=2), so that the AddDeleteRow procedure knows what to do.
This procedure, first of all checks if your instruction is clear. If you have selected more than one row it will do nothing. But if you have selected only one row - never mind, how many cells in it - that row will either be deleted or a row inserted in its place, depending upon which button you pressed.
- Code: Select all
Ws = Array("Sheet1", "Sheet2")
This array decides on which worksheets the action will be carried out. If a worksheet by the name mentioned in this array doesn't exist the macro will crash. If you add a third, fourth etc. sheet to it the same action will be arried out on all of them. No need to make any other change anywhere else.
- Code: Select all
.Protect Password:=PW, UserInterfaceOnly:=True
When you set the protection using VBA you have a property at your disposal which allows you to access using code the worksheet without the need for unprotecting it while the user must still enter a password to do the same thing. This is the UserInterfaceOnly property which the above code sets to True. So, on second use unprotecting the sheets will not be necessary, and should you decide to run other code on them, don't be surprised if you can do so without lifting the protection. The reason is right here.
You do not have the required permissions to view the files attached to this post.
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)