+ Reply to Thread
Results 1 to 5 of 5

PLEASE HELP! Copy sheet with password protected cells debug error

  1. #1
    Registered User
    Join Date
    07-06-2006
    Posts
    29

    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

  2. #2
    Duncan
    Guest

    Re: PLEASE HELP! Copy sheet with password protected cells debug error

    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



  3. #3
    Registered User
    Join Date
    07-06-2006
    Posts
    29
    Thats great!

    Thanks very much for your help Duncan

    Andrew

  4. #4
    Duncan
    Guest

    Re: PLEASE HELP! Copy sheet with password protected cells debug error

    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



  5. #5
    Norman Jones
    Guest

    Re: PLEASE HELP! Copy sheet with password protected cells debug error

    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" <duncan.mutchblache@googlemail.com> wrote in message
    news:1152528414.690356.223460@75g2000cwc.googlegroups.com...
    > 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1