check box help!
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. |
check box help!
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 " wrote: 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. |
check box help!
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 wrote: 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. -- Dave Peterson |
check box help!
On Jan 5, 6:52 pm, Dave Peterson wrote:
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 wrote: 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. -- Dave Peterson 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. |
All times are GMT +1. The time now is 12:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com