Marking a cell on click

[DoctorC]

Hi,
sorry if I bother you, but I need a fast solution as the boss is waiting
for the spreadsheet.

I need an holiday planner where clicking on a cell marks it as
holiday/working day.
I.e. each employee marks on this spreadsheet the holiday days clicking
on the cells that indicate the days he chooses.

How to add such a functionality?
I never used macros or VBA, but I'm a programmer and keen to learn

Please help me.

Enrico

 

[Rick Rothstein \(MVP - VB\)]

There is no "click event" for a worksheet. Sometime SelectionChange can be
used as a substitute, but for the functionality you want, I don't think it
is a good choice (it won't allow you to correct a mistake without clicking a
different cell and then clicking back into the erroneous one). Would a
double-click to mark the cell be acceptable? If so, do the following...
Right-click the tab for the worksheet you want this functionality on. This
will take you to the code window for the worksheet you right-clicked on
within the Visual Basic editor. Copy/Paste this event procedure into that
code window...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
' Make sure cell within the specified range was select
If Not Intersect(Target, Range("C2:E5")) Is Nothing Then
' Make sure the double click does not enter Edit Mode
Cancel = True
' Alternate between "X" and ""
If Target.Value = "X" Then
Target.Value = ""
Else
Target.Value = "X"
End If
End If
End Sub

In the first If-Then statement, I specified an example range of
Range("C2:E5")... change that to the range of cells you want the be able to
mark by double-clicking. That is it. Go back to the worksheet and
double-click a blank cell (actually a cell not containing an "X"... if it
has text in it other than an "X", it shouldn't though, it will be treated as
if it were blank) within the specified range to place an "X" in it.
Double-click a cell with an "X" in it to blank it out.

Rick

 

[DoctorC]

Rick Rothstein (MVP - VB) pisze:

There is no "click event" for a worksheet. Sometime SelectionChange can
be used as a substitute, but for the functionality you want, I don't
think it is a good choice (it won't allow you to correct a mistake
without clicking a different cell and then clicking back into the
erroneous one). Would a double-click to mark the cell be acceptable? If
so, do the following... Right-click the tab for the worksheet you want
this functionality on. This will take you to the code window for the
worksheet you right-clicked on within the Visual Basic editor.
Copy/Paste this event procedure into that code window...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
' Make sure cell within the specified range was select
If Not Intersect(Target, Range("C2:E5")) Is Nothing Then
' Make sure the double click does not enter Edit Mode
Cancel = True
' Alternate between "X" and ""
If Target.Value = "X" Then
Target.Value = ""
Else
Target.Value = "X"
End If
End If
End Sub

In the first If-Then statement, I specified an example range of
Range("C2:E5")... change that to the range of cells you want the be able
to mark by double-clicking. That is it. Go back to the worksheet and
double-click a blank cell (actually a cell not containing an "X"... if
it has text in it other than an "X", it shouldn't though, it will be
treated as if it were blank) within the specified range to place an "X"
in it. Double-click a cell with an "X" in it to blank it out.

Rick

Great!!
Thanks a lot

Could you make so that the cell color changes also?

Enrico

 

[Bob Phillips]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
' Make sure cell within the specified range was select
If Not Intersect(Target, Range("C2:E5")) Is Nothing Then
' Make sure the double click does not enter Edit Mode
Cancel = True
' Alternate between "X" and ""
If Target.Value = "X" Then
Target.Value = ""
Target.Interior.ColorIndex = xlColorindexNone
Else
Target.Value = "X"
Target.Interior.ColorIndex = 3 ' red
End If
End If
End Sub

 

[DoctorC]

DoctorC pisze:

Great!!
Thanks a lot

Could you make so that the cell color changes also?

Enrico

I managed alone

Thanks for your help

Enrico

 

[DoctorC]

Bob Phillips pisze:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
' Make sure cell within the specified range was select
If Not Intersect(Target, Range("C2:E5")) Is Nothing Then
' Make sure the double click does not enter Edit Mode
Cancel = True
' Alternate between "X" and ""
If Target.Value = "X" Then
Target.Value = ""
Target.Interior.ColorIndex = xlColorindexNone
Else
Target.Value = "X"
Target.Interior.ColorIndex = 3 ' red
End If
End If
End Sub

I used Target.Interior.Color = vbRed

Thanks anyway for your help

Enrico