I'm am part of a charitable initiative helping people with disabilities / life challenges get into work. Myself a colleague are trying to put an interactive excel calendar together so that they identify the days that they can work, which we help them calculate earnings ect.
I have no issue with creating a calendar but we have been trying to use vba code to create a calendar whereby they can highlight days by clicking on them and unhighlight them by clicking on the day again.
This code seems to pretty much do the job:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 4: Target.Interior.ColorIndex = 3
Case Else: Target.Interior.ColorIndex = 4
End Select
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub
The issue is we would need this to affect only one sheet of the workbook, use only one color, and a range of cells (i.e. not the whole sheet, for example A:3 to G:6. It's driving us mad that we can't figure this out because we're so new to this!
It would be cool and helpful if every time they selected/deselected a day it also created a value for the total number of days that they can work somewhere on the spreadsheet, but I realise this might be too difficult to achieve.
Any help would be greatly appreciated.
Thanks.
I have no issue with creating a calendar but we have been trying to use vba code to create a calendar whereby they can highlight days by clicking on them and unhighlight them by clicking on the day again.
This code seems to pretty much do the job:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 4: Target.Interior.ColorIndex = 3
Case Else: Target.Interior.ColorIndex = 4
End Select
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub
The issue is we would need this to affect only one sheet of the workbook, use only one color, and a range of cells (i.e. not the whole sheet, for example A:3 to G:6. It's driving us mad that we can't figure this out because we're so new to this!
It would be cool and helpful if every time they selected/deselected a day it also created a value for the total number of days that they can work somewhere on the spreadsheet, but I realise this might be too difficult to achieve.
Any help would be greatly appreciated.
Thanks.