Hello,
Most of the codes that I use are either obtained by a combination of internet search, a couple of books, or by using the recorder. Hence I realise they may be very clunky, but eventually they work and do the job.
Anyway when a cell in a range is selected, the cell colour is yellow and the cell value is displayed in cell D3, when another cell in the range is selected the previous cell is no longer yellow but the new cell is now yellow and its value is in D3. This works OK.
The other puts a tick into the cell when double-clicked, and removed when double clicked again. This works OK.
There are 3 problems:
1. I have conditional formatting applied (if a tick - colour cell green) to the named range of cells that the tick is inserted into, but the conditional formatting is removed when ticked
2. The value in cell D3 is removed when a tick is applied, so it no longer displays the value of the yellow cell.
3. To ease navigation I would normally lock cells and protect the sheet without the option for the user to select locked cells. If I do this then the code won’t work, so I have the select locked cells option applied, but then navigation is all over the place. The yellow cells contain formula so must be locked, the tick cells only have a tick or not. Any suggestions, maybe code to only apply select locked cells to the named range?
Thanks in anticipation.
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
'====DOUBLE CLICK INSERTS A TICK INTO THE CELL, DOUBLE CLICK AGAIN TO CLEAR THE TICK
'these are the tick boxes in the TickBoxes range
'I4:I19,L4:L19,O4:O19,R4:R19,U4:U19,X4:X19,AA4:AA19,
'AD4:AD19,AG4:AG19,AJ4:AJ19,AM4:AM19,AP4:AP19
‘,AS4:AS19,AV4:AV19,AY4:AY19,BB4:BB19
If Not Intersect(target, Range("TickBoxes")) Is Nothing Then
Application.EnableEvents = False
If ActiveCell.Value = ChrW(&H2713) Then
ActiveCell.ClearContents
Else
ActiveCell.Value = ChrW(&H2713) 'character for tick mark
End If
Cancel = True
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal target As Range)
'==== SELECT A CELL IN THE RANGE, CELL GOES YELLOW TO SHOW IT'S SELECTED, VALUE IS DISPLAYED IN D3 =======
'PROTECTS SHEET AT END WITH SELECT LOCKED CELLS*****
'CELLS IN NAMED RANGE ("J2:J30,M2:M30,P2:P30,S2:S30,V2:V30,Y5:Y22,AB2:AB30,AE2:AE30"))
Sheets("CHIPS").Unprotect 'Password:="123456" '
'PUTS THE SHOP NAME INTO D3
If Application.Intersect(target, Range("Shops")) Is Nothing Then ' the selected cell is not in the named range
Range("D3") = "" 'so don't put the cell value into D3
Else: Range("D3").Value = ActiveCell.Value 'but if in the range put selected shop name into D3
End If
'CHANGES THE SELECTED CELL COLOUR TO YELLOW
If Application.Intersect(target, Range("Shops")) Is Nothing Then
Range("A3") = ""
Else:
With target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbYellow
End With
End If
Sheets("CHIPS").Protect 'Password:="123456"
End Sub
Most of the codes that I use are either obtained by a combination of internet search, a couple of books, or by using the recorder. Hence I realise they may be very clunky, but eventually they work and do the job.
Anyway when a cell in a range is selected, the cell colour is yellow and the cell value is displayed in cell D3, when another cell in the range is selected the previous cell is no longer yellow but the new cell is now yellow and its value is in D3. This works OK.
The other puts a tick into the cell when double-clicked, and removed when double clicked again. This works OK.
There are 3 problems:
1. I have conditional formatting applied (if a tick - colour cell green) to the named range of cells that the tick is inserted into, but the conditional formatting is removed when ticked
2. The value in cell D3 is removed when a tick is applied, so it no longer displays the value of the yellow cell.
3. To ease navigation I would normally lock cells and protect the sheet without the option for the user to select locked cells. If I do this then the code won’t work, so I have the select locked cells option applied, but then navigation is all over the place. The yellow cells contain formula so must be locked, the tick cells only have a tick or not. Any suggestions, maybe code to only apply select locked cells to the named range?
Thanks in anticipation.
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
'====DOUBLE CLICK INSERTS A TICK INTO THE CELL, DOUBLE CLICK AGAIN TO CLEAR THE TICK
'these are the tick boxes in the TickBoxes range
'I4:I19,L4:L19,O4:O19,R4:R19,U4:U19,X4:X19,AA4:AA19,
'AD4:AD19,AG4:AG19,AJ4:AJ19,AM4:AM19,AP4:AP19
‘,AS4:AS19,AV4:AV19,AY4:AY19,BB4:BB19
If Not Intersect(target, Range("TickBoxes")) Is Nothing Then
Application.EnableEvents = False
If ActiveCell.Value = ChrW(&H2713) Then
ActiveCell.ClearContents
Else
ActiveCell.Value = ChrW(&H2713) 'character for tick mark
End If
Cancel = True
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal target As Range)
'==== SELECT A CELL IN THE RANGE, CELL GOES YELLOW TO SHOW IT'S SELECTED, VALUE IS DISPLAYED IN D3 =======
'PROTECTS SHEET AT END WITH SELECT LOCKED CELLS*****
'CELLS IN NAMED RANGE ("J2:J30,M2:M30,P2:P30,S2:S30,V2:V30,Y5:Y22,AB2:AB30,AE2:AE30"))
Sheets("CHIPS").Unprotect 'Password:="123456" '
'PUTS THE SHOP NAME INTO D3
If Application.Intersect(target, Range("Shops")) Is Nothing Then ' the selected cell is not in the named range
Range("D3") = "" 'so don't put the cell value into D3
Else: Range("D3").Value = ActiveCell.Value 'but if in the range put selected shop name into D3
End If
'CHANGES THE SELECTED CELL COLOUR TO YELLOW
If Application.Intersect(target, Range("Shops")) Is Nothing Then
Range("A3") = ""
Else:
With target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbYellow
End With
End If
Sheets("CHIPS").Protect 'Password:="123456"
End Sub
