Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Interior class error/runtime error 1004 | Excel Programming |