Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 -- bsnapoo ----------------------------------------------------------------------- bsnapool's Profile: http://www.excelforum.com/member.php...fo&userid=3611 View this thread: http://www.excelforum.com/showthread.php?threadid=55982 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP! Copy sheet with password protected cells debug error
Thats great! Thanks very much for your help Duncan 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ups.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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to unlock and lock cells in password protected sheet | Excel Discussion (Misc queries) | |||
no password for protected sheet | Excel Worksheet Functions | |||
How to Copy a Password Protected Excel Worksheet | Excel Worksheet Functions | |||
password protected Sheet | Excel Discussion (Misc queries) | |||
VBa, Password protected sheet fails to get unprotected with the same password | Excel Programming |