Unprotect then protect to run VBA code

[BobH]

I have 2 worksheets in the same workbook that are protected with the same password. The Sheet "SEARCH" allows me to select information and post it to the sheet named "PRICE SHEET" When I run the command button the codes wont work when protected. The coding below seems to work when only the "PRICE SHEET" is not protected .

The sheet "SEARCH" allows me to enter "select" in Column D then selects a code in Column A and transfers that to the next available row on "PRICE SHEET" Column "B" starting at cell "B9". I have tried lots of combinations of coding but no joy, I have managed to work out codes on other worksheets but this has got me stumped. Any help would be very much appreciated.

Regards Rob

Private Sub CommandButton3_Click()
Dim i, LastRow
LastRow = Sheets("SEARCH").Range("D" & Rows.Count).End(xlUp).Row

Sheets("PRICE SHEET").Range("B9:B300").ClearContents

For i = 2 To LastRow

If Sheets("SEARCH").Cells(i, "d").Value = "select" Then

Sheets("SEARCH").Cells(i, "a").Cells.Copy Destination:=Sheets("PRICE SHEET").Range("b" & Rows.Count).End(xlUp).Offset(1)

Worksheets("PRICE SHEET").Activate
End If

Next i

End Sub

 

[sykes]

Not tested but....

Private Sub CommandButton3_Click()
Dim i, LastRow

On Error GoTo err_hndlr
LastRow = Sheets("SEARCH").Range("D" & Rows.Count).End(xlUp).Row

    With Sheets("PRICE SHEET")
        .Unprotect "pword"
        .Range("B9:B300").ClearContents
        Sheets("SEARCH").Unprotect "pword"
        For i = 2 To LastRow
        
        If Sheets("SEARCH").Cells(i, "d").Value = "select" Then
        
        Sheets("SEARCH").Cells(i, "a").Cells.Copy Destination:=.Range("b" & Rows.Count).End(xlUp).Offset(1)
        
            .Activate
        End If
        
        Next i
    
    End With
err_hndlr: Sheets("SEARCH").Protect "pword": Sheets("PRICE SHEET").Protect "pword"
End Sub

My suggestion is to just unprotect both sheets, then re-protect them at the end.
Replace pword, with your actual password for the sheets.
NB untested (not much time) so test on a COPY of your WB first.

The other consideration is that this allows anyone who knows how to access the VBA module, to see your hard-coded password.
No Excel stuff is very secure, to be honest, but in order to put the more nosey users off a little more, protect your VBA module, too - by going into it's properties, and setting a password - which is then reqd to even vies it.

 

[BobH]

Works perfect thank you so much for your help on this I will definitely password up the module. The unprotecting and protecting of the password makes the process a bit clunky but I can live with that. Thanks again

 

[BobH]

I have found one issue is that the Column B9:B300 on "PRICE SHEET",the column to which the date is transferred needs to be free to edit also and it becomes protected once I apply the command, is their a way round this when protecting the "PRICE SHEET" Regards Rob

 

[sykes]

Pleasure, and thanks for the feedback.

Your first reply: in what way is it "Chunky?" The code unprotects & re-protects, which should happen in the background; you shouldn't see anything (or have to take any action).

Your second reply: You can lock & unlock all, or only desired ranges. Ranges which are locked, will not allow editing, once sheet protection's applied.
However, ranges which remain unlocked, allow editing - even when sheet protection's applied, and this sounds like what you're after in your col B.

So - unlock B9:B300 on "PRICE SHEET" but lock all of the other cells, and you should be fine.

For a good look at locking/unlocking/protecting, see this Microsoft article.

 

[BobH]

Hi Thanks should have said "Clunky" but not important.
Its hard to explain but when i hit the command button it transfers the required information and I can edit the "B" Column on the Price Sheet, When I alter the selection on the "SEARCH" sheet and hit the command button some of the cells in column B on the Price sheet then become locked for editing. It may be easier to send you a reduced copy of the spreadsheet if you have the time to look at this for me. kind regards Rob

 

[BobH]

Pleasure, and thanks for the feedback.

Your first reply: in what way is it "Chunky?" The code unprotects & re-protects, which should happen in the background; you shouldn't see anything (or have to take any action).

Your second reply: You can lock & unlock all, or only desired ranges. Ranges which are locked, will not allow editing, once sheet protection's applied.
However, ranges which remain unlocked, allow editing - even when sheet protection's applied, and this sounds like what you're after in your col B.

So - unlock B9:B300 on "PRICE SHEET" but lock all of the other cells, and you should be fine.

For a good look at locking/unlocking/protecting, see this Microsoft article.

Hi I have managed to sort out the issue thank you for your help
Regards Rob

 

[sykes]

Jolly good. Thanks for letting us know.