error 1004?

[Peterke]

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]

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

[Rowan]

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