Tick or Toggle Revisited

[mlv]

I'm trying to use some event code kindly provided by Bob Phillips in message
(13/07/2007) that alternately
puts and removes a tick in each cell of a range of cells.

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

 

[Bob Phillips]

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


--
HTH

Bob

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

 

[mlv]

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

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

 

[Bob Phillips]

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

PS Love your signature

--
HTH

Bob

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

 

[mlv]

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.