PLEASE HELP! Copy sheet with password protected cells debug error

Hi All, hope you are ok.

I am struggling with an error runtime error "1004" which keeps on occuring when the macro runs, I protected a the master sheet and have a macro to currently copy the sheet and match the entered cell value to an 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

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



bsnapool wrote:
> Hi All, hope you are ok.
>
> I am struggling with an error runtime error "1004" which keeps on
> occuring when the macro runs, I protected a the master sheet and have a
> macro to currently copy the sheet and match the entered cell value to an
> 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
>
>
> --
> bsnapool
> ------------------------------------------------------------------------
> bsnapool's Profile: http://www.excelforum.com/member.php...o&userid=36115
> View this thread: http://www.excelforum.com/showthread...hreadid=559827

Thats great!

Thanks very much for your help Duncan

Andrew

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



Duncan wrote:
> 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
>
>
>
> bsnapool wrote:
> > Hi All, hope you are ok.
> >
> > I am struggling with an error runtime error "1004" which keeps on
> > occuring when the macro runs, I protected a the master sheet and have a
> > macro to currently copy the sheet and match the entered cell value to an
> > 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
> >
> >
> > --
> > bsnapool
> > ------------------------------------------------------------------------
> > bsnapool's Profile: http://www.excelforum.com/member.php...o&userid=36115
> > View this thread: http://www.excelforum.com/showthread...hreadid=559827

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
'<<===========


---
Regards,
Norman



"Duncan" <[email protected]> wrote in message
news:[email protected]...
> 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