Tick or Toggle Revisited

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlv - 25 Sep 2007 10:50 GMT

I'm trying to use some event code kindly provided by Bob Phillips in message
news:u$MX4fVxHHA.312@TK2MSFTNGP04.phx.gbl... (13/07/2007) that alternately
puts and removes a tick in each cell of a range of cells.

Bob wrote:

> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Const WS_RANGE As String = "C6:C46"
>
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> If .Count = 1 Then
> If .Value = "" Then
> .Value = "a"
> .Font.Name = "Marlett"
> Else
> .Value = ""
> End If
> .Offset(0, 1).Select
> End If
> End With
> End If
>
> End Sub

I've used this code before and it has worked fine, but this time I have
problems.

Initially when I clicked on a cell, nothing seemed to happen. Then I
realised that each cell was in fact two merged cells, i.e. cell C6 is
actually cells C6 & C7 merged, through to cell C46 actually being C46 & C47
merged.

I changed the code line 'If .Count = 1 Then' to 'If .Count = 2 Then'
on the assumption that 'Count' would still see two cells, even though they
were merged.

Now I keep getting a 'Run-time error 13': Type mismatch. Debug highlights
the line: 'If .Value = "" Then'

Any suggestions?

Thanks

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Bob Phillips - 25 Sep 2007 12:39 GMT

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C6:C46"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Count > 1 Then
If .MergeCells Then
If .Areas(1).Text = "" Then
.Areas(1).Value = "a"
.Font.Name = "Marlett"
Else
.Areas(1).Value = ""
End If
.Offset(0, 1).Select
End If
ElseIf .Count = 1 Then
If .Value = "" Then
.Value = "a"
.Font.Name = "Marlett"
Else
.Value = ""
End If
.Offset(0, 1).Select
End If
End With
End If
End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> I'm trying to use some event code kindly provided by Bob Phillips in
> message news:u$MX4fVxHHA.312@TK2MSFTNGP04.phx.gbl... (13/07/2007) that
[quoted text clipped - 39 lines]
>
> Thanks

Reply to this Message

mlv - 25 Sep 2007 14:49 GMT

> Try this
>
[quoted text clipped - 25 lines]
> End If
> End Sub

Thanks Bob, that seems to have sorted it.

I guessed the problems were being caused by the merged cells, but I didn't
know what changes to make to the code.

I see you have now modified the code to work with both single cells and any
number of merged cells, so I shouldn't have any further problems.

Thanks for your help.

Regards

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Bob Phillips - 25 Sep 2007 16:15 GMT

Yeah, I tested with horizontal and vertically merged cells, and seemed okay.

PS Love your signature :-)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> Try this
>>
[quoted text clipped - 37 lines]
>
> Regards

Reply to this Message

mlv - 27 Sep 2007 10:00 GMT

> Yeah, I tested with horizontal and vertically merged
> cells, and seemed okay.

I've tested it well, with no obvious problems.

> PS Love your signature :-)

Thanks - it's an allusion to my ex-hobby of target pistol shooting,
annihilated by 'Knee-Jerk' Blair following the Dublane atrocity as part of
his "Make the streets (of Britain) safe" campaign that confiscated all the
legally owned handguns, but allowed the criminals to keep theirs, resulting
in something like a 42% increase in gun crime in the UK.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message