error 1004?
Hi,
Why does the error 1004 occure here? (error marked between <<) and what do I do to avoid it in the future? Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect Password:="peterke" Application.ScreenUpdating = False Range("C6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C,1)"<< Range("C6").Select ActiveSheet.Protect Password:="peterke" Application.ScreenUpdating = True End Sub greets, Peterke |
error 1004?
Your code calls itself--you have range("c6").select. If you didn't start in C6,
then excel sees that .select as a selection change and fires. Since it's calling itself it runs twice. The second time through the worksheet is protected. Kablewie! You could do this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) me.Unprotect Password:="peterke" Application.ScreenUpdating = False application.enableevents = false me.Range("C6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C,1)" me.Range("C6").Select me.Protect Password:="peterke" Application.ScreenUpdating = True application.enableevents = false End Sub (I like to use the me. keyword for stuff behind the worksheet.) But even better, you don't have to select: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect Password:="peterke" Application.ScreenUpdating = False me.Range("C6").FormulaR1C1 = "=SUM(R[1]C,1)" me.Protect Password:="peterke" Application.ScreenUpdating = True End Sub Peterke wrote: Hi, Why does the error 1004 occure here? (error marked between <<) and what do I do to avoid it in the future? Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect Password:="peterke" Application.ScreenUpdating = False Range("C6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C,1)"<< Range("C6").Select ActiveSheet.Protect Password:="peterke" Application.ScreenUpdating = True End Sub greets, Peterke -- Dave Peterson |
error 1004?
The only time I get that error is when the sheet is still protected. However
if the password you have supplied is incorrect then the error should occur on the unprotect line. Also you should probably disable events here so that you don't get into a loop. Do you want to make C6 the only cell which can be active on the sheet? This is currently the case. Finally, are you sure this is the formula you are wanting to place is C6: =SUM(C7,1) Maybe your event should look like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False ActiveSheet.Unprotect Password:="peterke" Range("C6").FormulaR1C1 = "=SUM(R[1]C,1)" 'supply correct formula ActiveSheet.Protect Password:="peterke" ErrorHandler: Application.EnableEvents = True End Sub Hope this helps Rowan "Peterke" wrote: Hi, Why does the error 1004 occure here? (error marked between <<) and what do I do to avoid it in the future? Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect Password:="peterke" Application.ScreenUpdating = False Range("C6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C,1)"<< Range("C6").Select ActiveSheet.Protect Password:="peterke" Application.ScreenUpdating = True End Sub greets, Peterke |
All times are GMT +1. The time now is 03:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com