1004' method 'range' of object '_worksheet' failed

[adamcfishman]

Please Please Please Help
This is currently working but when I try and add more cells to reference it gives me a dreaded "1004' method 'range' of object '_worksheet' failed" error. Does anyone have any suggestions or feedback?


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'if they double click on the cell once it shows "a" in marlett,
'if they double-click on it again it shows "r" in Marlett.
'Then if they click on it a third time it is blank ""

If Target.Count > 1 Or Intersect(Target, Range("k3:k6, l3:l6, z3:ab3, z5:aa6, k11:l11, k12:m16, z11:ab14, z22:z27, ab22:ab27, k31:m32, z31:ab32, x44:x59, z44:z59, aa44, aa46, aa48, aa50, aa52, aa54, aa56, aa58, k63:m64, k68:m68, z69:aa69, k85:l87, l97:m98, l100:m100, l103:m103, l107:m107, z85:aa86, z88")) Is Nothing Then Exit Sub
Select Case Target.Value
Case Is = ""
Target.Font.Name = "Marlett"
Target.Value = "a"
Case Is = "a"
Target.Font.Name = "Marlett"
Target.Value = "r"
Case Is = "r"
Target.ClearContents
End Select
Cancel = True
End Sub

Thanks for your help.

 

[wigi]

You could make a Named range for these cells.
Then in your code, you can simply replace all these cell address with the name of the named range.

 

[adamcfishman]

Sounds wonderful, though I am very green with VBA how would I go about doing that?

Thanks for you very quick reply.

 

[wigi]

Select the cells using the Ctrl key, and in the Name box (to the left of the formula bar) type in the name. Next:

If Target.Count > 1 Or Intersect(Target, Range("MyRange")) Is Nothing Then Exit Sub

Change MyRange for the name you just used.

 

[Norie]

As well as Wigi's suggestion I think you can probably combine some of those ranges.

eg K3:K6, L3:L6 = K3:K6

You've also got K12:M16, K32:M32, K63:M64 ... Z11:AB14 and so on.

Then there's the columns - you only seem to be working with columns, K:M, X and Z:AB.

So you could check the column first:

If Target.Count > 1 Or Intersect(Target, Range("L:M, X:X, Z:AB")) Is Nothing Then Exit Sub

 

[adamcfishman]

@ wigi, works like a champ, thank you so much for your help.

@ Norie, There are a lot of merged cells I was under the impression that column selection is not possible in that scenario or is it stillpossible with merged cells? In any case that you for your feedback.

 

[adamcfishman]

Actually came up with one more question for you, how do I go about changing from a double click to a single click?

 

[RSXchin]

Actually came up with one more question for you, how do I go about changing from a double click to a single click?

Use the Sheet_SelectionChange event.

 

[Norie]

Adam

Sorry I don't quite understand, there's no selection in the code I posted.

I've you mean that the code won't get triggered at all when a merged cell is selected then you can just remove the Target.Count part.

You might also need to change the Select case:

Select Case Target.Cells(1, 1).Value

Or, and this is perhaps better overall, get rid of the merged cells.