PLEASE HELP! Copy sheet with password protected cells debug error

[bsnapool]

Hi All, hope you are ok.

I am struggling with an error runtime error "1004" which keeps o
occuring when the macro runs, I protected a the master sheet and have
macro to currently copy the sheet and match the entered cell value to a
exsisting sheet within the workbook.

Current code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Target.Address(0, 0) = "Q7" Then
On Error Resume Next
Set sh = Worksheets(Target.Value)
On Error GoTo 0
If Not sh Is Nothing Then
Cells.Copy sh.Cells
Else
MsgBox Target.Value & " does not exist"
End If
End If

End Sub


Hope some body can help.

Many thanks

Andrew :mad

 

[Duncan]

Hi,

If the error occurs because the cells are protected, can you not
unprotect with your code and then reprotect again by code afterwards?

activeworkbook.unprotect
activeworkbook.protect

look in the help as there are other parameters you can set to the
protection.

Or: you can protect via macro on startup and state user only so that
the macro is not affected by the protection, the protection will only
work at sheet level (typing into cells etc) and will not stop a macro
from changing cells thus solving the error

I cant remember the words though, something like 'UserInputOnly' or
something like that...

Duncan

 

[Duncan]

Found it: its UserInterfaceOnly = true.

as in: Sheets("Sheet1").Protect password:="password",
UserInterfaceOnly:=True

This will allow your macro to use cells as it wishes (without bugging
out) whilst leaving the protection on the sheet for users.

Hope this helps

Duncan

 

[bsnapool]

Thats great!

Thanks very much for your help Duncan

Andrew

 

[Norman Jones]

Hi Bsnapool, Hi Duncan,

I cant remember the words though, something like 'UserInputOnly' or
something like that

With reference to Duncan's latter suggestion, setting the Protect method's
UserInterfaceOnly parameter to true enables vba manipulation of the
protected sheet.

However, this setting is not persistent and needs to be reset each time the
workbook is opened.

Perhaps, therefore, protection could be set in the Workbook_Open or
Auto_Open procedures, e.g.:

'===========>>
Sub Auto_Open()
With Worksheets("Sheet1")
.Protect Password:="drowssap", UserInterfaceOnly:=True
End With
End Sub
'<<===========