check box help!

[riccifs]

Hi to everyone, I'm using this code to simulate a check box
functionality, after format the cell with Wingdings font.
What I'd like to do is to modify it to be able to that:
Instead of use "ActiveCell" I'd like to able to set my own range of
cells with a character Value for check and uncheck AND for a second
range of cells I'd like to set a different character Value.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
If ActiveCell.Value = Chr(254) Then
ActiveCell.Value = Chr(168)
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Value = Chr(254)
ActiveCell.Offset(1, 0).Select
End If
End Sub

Hope someone will be able to understand my request!
Many thanks in advantage...
Stefano.

 

[FSt1]

hi
i've played with your code and it seems to work. i don't understand why your
are moving down the sheet with it. but what are these other ranges your
mentions.
seems that you could just replace activecell with range("A1") or Cells(1,1)
or what ever. I understand your theory but what are you trying to do with it?

Regards
FSt1

 

[Dave Peterson]

First, it's better to use Target instead of Activecell. Target is the range
passed to the function anyway.

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

Dim Rng1 As Range
Dim Rng2 As Range

Dim ChkOn As String
Dim ChkOff As String

Set Rng1 = Me.Range("a1:c9")
Set Rng2 = Me.Range("e3:f4")

If Target.Cells.Count > 1 Then Exit Sub

If Not (Intersect(Target, Rng1) Is Nothing) Then
'inside rng1
ChkOn = Chr(254)
ChkOff = Chr(168)
ElseIf Not (Intersect(Target, Rng2) Is Nothing) Then
'inside rng2
ChkOn = "On" 'change to what you want
ChkOff = "Off"
Else
Exit Sub
End If

Cancel = True 'stop editing in cell

If Target.Value = ChkOn Then
Target.Value = ChkOff
Else
Target.Value = ChkOn
End If

Target.Offset(1, 0).Select

End Sub

 

[riccifs]

First, it's better to use Target instead of Activecell. Target is the range
passed to the function anyway.

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

Dim Rng1 As Range
Dim Rng2 As Range

Dim ChkOn As String
Dim ChkOff As String

Set Rng1 = Me.Range("a1:c9")
Set Rng2 = Me.Range("e3:f4")

If Target.Cells.Count > 1 Then Exit Sub

If Not (Intersect(Target, Rng1) Is Nothing) Then
'inside rng1
ChkOn = Chr(254)
ChkOff = Chr(168)
ElseIf Not (Intersect(Target, Rng2) Is Nothing) Then
'inside rng2
ChkOn = "On" 'change to what you want
ChkOff = "Off"
Else
Exit Sub
End If

Cancel = True 'stop editing in cell

If Target.Value = ChkOn Then
Target.Value = ChkOff
Else
Target.Value = ChkOn
End If

Target.Offset(1, 0).Select

End Sub

Hi Dave, many many thanks for your help, your code is simply perfect!
I mean, it does EXACTLY what I was asking for, no more no less!!

I really do appreciate all that!
Bye, Stefano.

P.S.
thank even to FSt1 for his first attempt to solve my problem.