[SOLVED] Creating a tick by clicking a cell

I have designed a schedule form for my business using excel 2003 and i would
like to know how i can click a cell (a time block) and have it place a
pre-defined character into the cell such as an (on event)
function...please...any help would be appreciated!

There isn't a single click event that you can tie into. But you can tie into a
doubleclick or rightclick event.

You could tie into a selection event--either by mouse or arrow keys, but that's
always seemed dangerous to me--just arrowing past the cell could change
something you don't want changed.

I'd rather make it so the user has to do something explicit.

If that sounds like something you want to try, you could right click on the
worksheet tab that should have this behavior and select view code. Then paste
this code into the code window:

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

Dim myValues As Variant
Dim iCtr As Long
Dim res As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub

Cancel = True 'don't pop up the rightclick menu

myValues = Array("A", "C", "E", "V", "")

res = Application.Match(Target.Value & "", myValues, 0)

If IsNumeric(res) Then
If res = UBound(myValues) + 1 Then
res = LBound(myValues)
End If
Target.Value = myValues(res)
'Beep
Else
Beep
MsgBox "Not a valid existing character"
'Target.Value = myValues(LBound(myValues))
End If

End Sub

Since you're keeping track of time, I figured that you'd want to use various
codes. I used: "A", "C", "E", "V", ""

And the code only looks in column C with this line:
If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub


Each time you rightclick on a cell in column C, you'll either cycle through that
array (change it to what you want) or get a beep saying that the existing value
wasn't valid.

You can uncomment that line under the msgbox if you want to plop in the first
value in the array.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Justin Abel wrote:
>
> I have designed a schedule form for my business using excel 2003 and i would
> like to know how i can click a cell (a time block) and have it place a
> pre-defined character into the cell such as an (on event)
> function...please...any help would be appreciated!

--

Dave Peterson

Really nice code to know...but kinda a little confused on how to change like
the color of the tick and the size...you know...font stuff...and also...could
you please instruct me how to get an x when i create a tick please?

thank you

"Dave Peterson" wrote:

> There isn't a single click event that you can tie into. But you can tie into a
> doubleclick or rightclick event.
>
> You could tie into a selection event--either by mouse or arrow keys, but that's
> always seemed dangerous to me--just arrowing past the cell could change
> something you don't want changed.
>
> I'd rather make it so the user has to do something explicit.
>
> If that sounds like something you want to try, you could right click on the
> worksheet tab that should have this behavior and select view code. Then paste
> this code into the code window:
>
> Option Explicit
> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
>
> Dim myValues As Variant
> Dim iCtr As Long
> Dim res As Variant
>
> If Target.Cells.Count > 1 Then Exit Sub
> If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
>
> Cancel = True 'don't pop up the rightclick menu
>
> myValues = Array("A", "C", "E", "V", "")
>
> res = Application.Match(Target.Value & "", myValues, 0)
>
> If IsNumeric(res) Then
> If res = UBound(myValues) + 1 Then
> res = LBound(myValues)
> End If
> Target.Value = myValues(res)
> 'Beep
> Else
> Beep
> MsgBox "Not a valid existing character"
> 'Target.Value = myValues(LBound(myValues))
> End If
>
> End Sub
>
> Since you're keeping track of time, I figured that you'd want to use various
> codes. I used: "A", "C", "E", "V", ""
>
> And the code only looks in column C with this line:
> If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
>
>
> Each time you rightclick on a cell in column C, you'll either cycle through that
> array (change it to what you want) or get a beep saying that the existing value
> wasn't valid.
>
> You can uncomment that line under the msgbox if you want to plop in the first
> value in the array.
>
> You can read more about events at:
> Chip Pearson's site:
> http://www.cpearson.com/excel/events.htm
>
> David McRitchie's site:
> http://www.mvps.org/dmcritchie/excel/event.htm
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
>
> Justin Abel wrote:
> >
> > I have designed a schedule form for my business using excel 2003 and i would
> > like to know how i can click a cell (a time block) and have it place a
> > pre-defined character into the cell such as an (on event)
> > function...please...any help would be appreciated!
>
> --
>
> Dave Peterson
>

If you only want an X or blank, then change this line:
myValues = Array("A", "C", "E", "V", "")
to
myValues = Array("X", "")

Maybe something like this:

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

Dim myValues As Variant
Dim iCtr As Long
Dim res As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub

Cancel = True 'don't pop up the rightclick menu

myValues = Array("X", "")

res = Application.Match(Target.Value & "", myValues, 0)

If IsNumeric(res) Then
If res = UBound(myValues) + 1 Then
res = LBound(myValues)
End If
With Target
.Value = myValues(res)
.Font.Name = "Carrier New"
.Font.Size = "18"
.Interior.ColorIndex = 3
.Font.ColorIndex = 18
End With
'Beep
Else
Beep
MsgBox "Not a valid existing character"
'Target.Value = myValues(LBound(myValues))
End If

End Sub

or....

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

Dim myValues As Variant
Dim iCtr As Long
Dim res As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub

Cancel = True 'don't pop up the rightclick menu

myValues = Array(Chr(252), "")

res = Application.Match(Target.Value & "", myValues, 0)

If IsNumeric(res) Then
If res = UBound(myValues) + 1 Then
res = LBound(myValues)
End If
With Target
.Value = myValues(res)
.Font.Name = "wingdings"
If .Value = Chr(252) Then
.Font.Size = "18"
.Interior.ColorIndex = 3
.Font.ColorIndex = 18
Else
.Font.Size = 10
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlColorIndexAutomatic
End If
End With
'Beep
Else
Beep
MsgBox "Not a valid existing character"
'Target.Value = myValues(LBound(myValues))
End If

End Sub

Justin Abel wrote:
>
> Really nice code to know...but kinda a little confused on how to change like
> the color of the tick and the size...you know...font stuff...and also...could
> you please instruct me how to get an x when i create a tick please?
>
> thank you
>
> "Dave Peterson" wrote:
>
> > There isn't a single click event that you can tie into. But you can tie into a
> > doubleclick or rightclick event.
> >
> > You could tie into a selection event--either by mouse or arrow keys, but that's
> > always seemed dangerous to me--just arrowing past the cell could change
> > something you don't want changed.
> >
> > I'd rather make it so the user has to do something explicit.
> >
> > If that sounds like something you want to try, you could right click on the
> > worksheet tab that should have this behavior and select view code. Then paste
> > this code into the code window:
> >
> > Option Explicit
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> >
> > Dim myValues As Variant
> > Dim iCtr As Long
> > Dim res As Variant
> >
> > If Target.Cells.Count > 1 Then Exit Sub
> > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> >
> > Cancel = True 'don't pop up the rightclick menu
> >
> > myValues = Array("A", "C", "E", "V", "")
> >
> > res = Application.Match(Target.Value & "", myValues, 0)
> >
> > If IsNumeric(res) Then
> > If res = UBound(myValues) + 1 Then
> > res = LBound(myValues)
> > End If
> > Target.Value = myValues(res)
> > 'Beep
> > Else
> > Beep
> > MsgBox "Not a valid existing character"
> > 'Target.Value = myValues(LBound(myValues))
> > End If
> >
> > End Sub
> >
> > Since you're keeping track of time, I figured that you'd want to use various
> > codes. I used: "A", "C", "E", "V", ""
> >
> > And the code only looks in column C with this line:
> > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> >
> >
> > Each time you rightclick on a cell in column C, you'll either cycle through that
> > array (change it to what you want) or get a beep saying that the existing value
> > wasn't valid.
> >
> > You can uncomment that line under the msgbox if you want to plop in the first
> > value in the array.
> >
> > You can read more about events at:
> > Chip Pearson's site:
> > http://www.cpearson.com/excel/events.htm
> >
> > David McRitchie's site:
> > http://www.mvps.org/dmcritchie/excel/event.htm
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> >
> > Justin Abel wrote:
> > >
> > > I have designed a schedule form for my business using excel 2003 and i would
> > > like to know how i can click a cell (a time block) and have it place a
> > > pre-defined character into the cell such as an (on event)
> > > function...please...any help would be appreciated!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Is there a way to highlight a certain selection of cells and when i release
the mouse button...to have it fill the cells with x's??
thank you for anyhelp


"Dave Peterson" wrote:

> If you only want an X or blank, then change this line:
> myValues = Array("A", "C", "E", "V", "")
> to
> myValues = Array("X", "")
>
> Maybe something like this:
>
> Option Explicit
> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
>
> Dim myValues As Variant
> Dim iCtr As Long
> Dim res As Variant
>
> If Target.Cells.Count > 1 Then Exit Sub
> If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
>
> Cancel = True 'don't pop up the rightclick menu
>
> myValues = Array("X", "")
>
> res = Application.Match(Target.Value & "", myValues, 0)
>
> If IsNumeric(res) Then
> If res = UBound(myValues) + 1 Then
> res = LBound(myValues)
> End If
> With Target
> .Value = myValues(res)
> .Font.Name = "Carrier New"
> .Font.Size = "18"
> .Interior.ColorIndex = 3
> .Font.ColorIndex = 18
> End With
> 'Beep
> Else
> Beep
> MsgBox "Not a valid existing character"
> 'Target.Value = myValues(LBound(myValues))
> End If
>
> End Sub
>
> or....
>
> Option Explicit
> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
>
> Dim myValues As Variant
> Dim iCtr As Long
> Dim res As Variant
>
> If Target.Cells.Count > 1 Then Exit Sub
> If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
>
> Cancel = True 'don't pop up the rightclick menu
>
> myValues = Array(Chr(252), "")
>
> res = Application.Match(Target.Value & "", myValues, 0)
>
> If IsNumeric(res) Then
> If res = UBound(myValues) + 1 Then
> res = LBound(myValues)
> End If
> With Target
> .Value = myValues(res)
> .Font.Name = "wingdings"
> If .Value = Chr(252) Then
> .Font.Size = "18"
> .Interior.ColorIndex = 3
> .Font.ColorIndex = 18
> Else
> .Font.Size = 10
> .Interior.ColorIndex = xlNone
> .Font.ColorIndex = xlColorIndexAutomatic
> End If
> End With
> 'Beep
> Else
> Beep
> MsgBox "Not a valid existing character"
> 'Target.Value = myValues(LBound(myValues))
> End If
>
> End Sub
>
> Justin Abel wrote:
> >
> > Really nice code to know...but kinda a little confused on how to change like
> > the color of the tick and the size...you know...font stuff...and also...could
> > you please instruct me how to get an x when i create a tick please?
> >
> > thank you
> >
> > "Dave Peterson" wrote:
> >
> > > There isn't a single click event that you can tie into. But you can tie into a
> > > doubleclick or rightclick event.
> > >
> > > You could tie into a selection event--either by mouse or arrow keys, but that's
> > > always seemed dangerous to me--just arrowing past the cell could change
> > > something you don't want changed.
> > >
> > > I'd rather make it so the user has to do something explicit.
> > >
> > > If that sounds like something you want to try, you could right click on the
> > > worksheet tab that should have this behavior and select view code. Then paste
> > > this code into the code window:
> > >
> > > Option Explicit
> > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > >
> > > Dim myValues As Variant
> > > Dim iCtr As Long
> > > Dim res As Variant
> > >
> > > If Target.Cells.Count > 1 Then Exit Sub
> > > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> > >
> > > Cancel = True 'don't pop up the rightclick menu
> > >
> > > myValues = Array("A", "C", "E", "V", "")
> > >
> > > res = Application.Match(Target.Value & "", myValues, 0)
> > >
> > > If IsNumeric(res) Then
> > > If res = UBound(myValues) + 1 Then
> > > res = LBound(myValues)
> > > End If
> > > Target.Value = myValues(res)
> > > 'Beep
> > > Else
> > > Beep
> > > MsgBox "Not a valid existing character"
> > > 'Target.Value = myValues(LBound(myValues))
> > > End If
> > >
> > > End Sub
> > >
> > > Since you're keeping track of time, I figured that you'd want to use various
> > > codes. I used: "A", "C", "E", "V", ""
> > >
> > > And the code only looks in column C with this line:
> > > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> > >
> > >
> > > Each time you rightclick on a cell in column C, you'll either cycle through that
> > > array (change it to what you want) or get a beep saying that the existing value
> > > wasn't valid.
> > >
> > > You can uncomment that line under the msgbox if you want to plop in the first
> > > value in the array.
> > >
> > > You can read more about events at:
> > > Chip Pearson's site:
> > > http://www.cpearson.com/excel/events.htm
> > >
> > > David McRitchie's site:
> > > http://www.mvps.org/dmcritchie/excel/event.htm
> > >
> > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > >
> > >
> > > Justin Abel wrote:
> > > >
> > > > I have designed a schedule form for my business using excel 2003 and i would
> > > > like to know how i can click a cell (a time block) and have it place a
> > > > pre-defined character into the cell such as an (on event)
> > > > function...please...any help would be appreciated!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Selection.Value = "X"
Cancel = True
End Sub

You can also select the range
type the value
and hit ctrl-enter (instead of just enter) to fill each cell in the selection.

Justin Abel wrote:
>
> Is there a way to highlight a certain selection of cells and when i release
> the mouse button...to have it fill the cells with x's??
> thank you for anyhelp
>
> "Dave Peterson" wrote:
>
> > If you only want an X or blank, then change this line:
> > myValues = Array("A", "C", "E", "V", "")
> > to
> > myValues = Array("X", "")
> >
> > Maybe something like this:
> >
> > Option Explicit
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> >
> > Dim myValues As Variant
> > Dim iCtr As Long
> > Dim res As Variant
> >
> > If Target.Cells.Count > 1 Then Exit Sub
> > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> >
> > Cancel = True 'don't pop up the rightclick menu
> >
> > myValues = Array("X", "")
> >
> > res = Application.Match(Target.Value & "", myValues, 0)
> >
> > If IsNumeric(res) Then
> > If res = UBound(myValues) + 1 Then
> > res = LBound(myValues)
> > End If
> > With Target
> > .Value = myValues(res)
> > .Font.Name = "Carrier New"
> > .Font.Size = "18"
> > .Interior.ColorIndex = 3
> > .Font.ColorIndex = 18
> > End With
> > 'Beep
> > Else
> > Beep
> > MsgBox "Not a valid existing character"
> > 'Target.Value = myValues(LBound(myValues))
> > End If
> >
> > End Sub
> >
> > or....
> >
> > Option Explicit
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> >
> > Dim myValues As Variant
> > Dim iCtr As Long
> > Dim res As Variant
> >
> > If Target.Cells.Count > 1 Then Exit Sub
> > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> >
> > Cancel = True 'don't pop up the rightclick menu
> >
> > myValues = Array(Chr(252), "")
> >
> > res = Application.Match(Target.Value & "", myValues, 0)
> >
> > If IsNumeric(res) Then
> > If res = UBound(myValues) + 1 Then
> > res = LBound(myValues)
> > End If
> > With Target
> > .Value = myValues(res)
> > .Font.Name = "wingdings"
> > If .Value = Chr(252) Then
> > .Font.Size = "18"
> > .Interior.ColorIndex = 3
> > .Font.ColorIndex = 18
> > Else
> > .Font.Size = 10
> > .Interior.ColorIndex = xlNone
> > .Font.ColorIndex = xlColorIndexAutomatic
> > End If
> > End With
> > 'Beep
> > Else
> > Beep
> > MsgBox "Not a valid existing character"
> > 'Target.Value = myValues(LBound(myValues))
> > End If
> >
> > End Sub
> >
> > Justin Abel wrote:
> > >
> > > Really nice code to know...but kinda a little confused on how to change like
> > > the color of the tick and the size...you know...font stuff...and also...could
> > > you please instruct me how to get an x when i create a tick please?
> > >
> > > thank you
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > There isn't a single click event that you can tie into. But you can tie into a
> > > > doubleclick or rightclick event.
> > > >
> > > > You could tie into a selection event--either by mouse or arrow keys, but that's
> > > > always seemed dangerous to me--just arrowing past the cell could change
> > > > something you don't want changed.
> > > >
> > > > I'd rather make it so the user has to do something explicit.
> > > >
> > > > If that sounds like something you want to try, you could right click on the
> > > > worksheet tab that should have this behavior and select view code. Then paste
> > > > this code into the code window:
> > > >
> > > > Option Explicit
> > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > >
> > > > Dim myValues As Variant
> > > > Dim iCtr As Long
> > > > Dim res As Variant
> > > >
> > > > If Target.Cells.Count > 1 Then Exit Sub
> > > > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> > > >
> > > > Cancel = True 'don't pop up the rightclick menu
> > > >
> > > > myValues = Array("A", "C", "E", "V", "")
> > > >
> > > > res = Application.Match(Target.Value & "", myValues, 0)
> > > >
> > > > If IsNumeric(res) Then
> > > > If res = UBound(myValues) + 1 Then
> > > > res = LBound(myValues)
> > > > End If
> > > > Target.Value = myValues(res)
> > > > 'Beep
> > > > Else
> > > > Beep
> > > > MsgBox "Not a valid existing character"
> > > > 'Target.Value = myValues(LBound(myValues))
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > > Since you're keeping track of time, I figured that you'd want to use various
> > > > codes. I used: "A", "C", "E", "V", ""
> > > >
> > > > And the code only looks in column C with this line:
> > > > If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
> > > >
> > > >
> > > > Each time you rightclick on a cell in column C, you'll either cycle through that
> > > > array (change it to what you want) or get a beep saying that the existing value
> > > > wasn't valid.
> > > >
> > > > You can uncomment that line under the msgbox if you want to plop in the first
> > > > value in the array.
> > > >
> > > > You can read more about events at:
> > > > Chip Pearson's site:
> > > > http://www.cpearson.com/excel/events.htm
> > > >
> > > > David McRitchie's site:
> > > > http://www.mvps.org/dmcritchie/excel/event.htm
> > > >
> > > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > > >
> > > >
> > > >
> > > > Justin Abel wrote:
> > > > >
> > > > > I have designed a schedule form for my business using excel 2003 and i would
> > > > > like to know how i can click a cell (a time block) and have it place a
> > > > > pre-defined character into the cell such as an (on event)
> > > > > function...please...any help would be appreciated!
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson