[SOLVED] Creating a tick by clicking a cell

Justin Abel · 04-19-2006, 10:15 PM

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 · 04-20-2006, 09:10 AM

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

Justin Abel · 04-20-2006, 11:10 PM

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 · 04-21-2006, 08:35 AM

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

Justin Abel · 04-22-2006, 08:15 PM

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 · 04-22-2006, 09:20 PM

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

Justin Abel · 04-23-2006, 08:10 PM

Alright...i really appreciate that last post...how about when i release the
button to have it fill the cells with x, plus have it highlight the same
cells?
thank you

"Dave Peterson" wrote:

> 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
>

Dave Peterson · 04-23-2006, 09:00 PM

You mean like change the fill color?

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

If you record a macro when you change the fill color to what you want, you'll
see the number you should use.

(.colorindex = 3 gave me a red fill color)

Justin Abel wrote:
>
> Alright...i really appreciate that last post...how about when i release the
> button to have it fill the cells with x, plus have it highlight the same
> cells?
> thank you
>
> "Dave Peterson" wrote:
>
> > 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
> >

--

Dave Peterson

Justin Abel · 04-24-2006, 12:00 AM

how would i go about knowing what each color is?
and can i assign like...different keys for different colors?

like...make a certain range of cells so that you can only insert the right
click x's in them and then use different keys to assign different colors in
use with the right click function? would it be easier for me to post the file
on my website for u? lol

thank you

"Dave Peterson" wrote:

> You mean like change the fill color?
>
>
> Option Explicit
> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> With Selection
> .Value = "X"
> .Interior.ColorIndex = 3
> End With
> Cancel = True
> End Sub
>
> If you record a macro when you change the fill color to what you want, you'll
> see the number you should use.
>
> (.colorindex = 3 gave me a red fill color)
>
> Justin Abel wrote:
> >
> > Alright...i really appreciate that last post...how about when i release the
> > button to have it fill the cells with x, plus have it highlight the same
> > cells?
> > thank you
> >
> > "Dave Peterson" wrote:
> >
> > > 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
> > >
>
> --
>
> Dave Peterson
>

Justin Abel · 04-24-2006, 08:20 AM

I have got a column list of employees on the left...is there a way...that if
they are not vertically sceduled to work for the day....then there name
dissappears....or any other suggestions besides the color lines that shows
they are scheduled...like if they are scheduled...then they're name is
highlighted....maybe a validation or something....any help would be
appreciated

"Justin Abel" wrote:

> how would i go about knowing what each color is?
> and can i assign like...different keys for different colors?
>
> like...make a certain range of cells so that you can only insert the right
> click x's in them and then use different keys to assign different colors in
> use with the right click function? would it be easier for me to post the file
> on my website for u? lol
>
> thank you
>
> "Dave Peterson" wrote:
>
> > You mean like change the fill color?
> >
> >
> > Option Explicit
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > With Selection
> > .Value = "X"
> > .Interior.ColorIndex = 3
> > End With
> > Cancel = True
> > End Sub
> >
> > If you record a macro when you change the fill color to what you want, you'll
> > see the number you should use.
> >
> > (.colorindex = 3 gave me a red fill color)
> >
> > Justin Abel wrote:
> > >
> > > Alright...i really appreciate that last post...how about when i release the
> > > button to have it fill the cells with x, plus have it highlight the same
> > > cells?
> > > thank you
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

Dave Peterson · 04-24-2006, 08:30 AM

I would drop the coloring of the cells.

And replace it with an autofilter.

You can select your range, then apply Data|Filter|Autofilter to that range.

Then you can use the arrow in the header row's cell to select the values you
want to see (and hide the others).

There's options for blanks and non-blanks, too. In fact, you can use that
arrow, choose custom and even choose from a bunch.

Justin Abel wrote:
>
> I have got a column list of employees on the left...is there a way...that if
> they are not vertically sceduled to work for the day....then there name
> dissappears....or any other suggestions besides the color lines that shows
> they are scheduled...like if they are scheduled...then they're name is
> highlighted....maybe a validation or something....any help would be
> appreciated
>
> "Justin Abel" wrote:
>
> > how would i go about knowing what each color is?
> > and can i assign like...different keys for different colors?
> >
> > like...make a certain range of cells so that you can only insert the right
> > click x's in them and then use different keys to assign different colors in
> > use with the right click function? would it be easier for me to post the file
> > on my website for u? lol
> >
> > thank you
> >
> > "Dave Peterson" wrote:
> >
> > > You mean like change the fill color?
> > >
> > >
> > > Option Explicit
> > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > With Selection
> > > .Value = "X"
> > > .Interior.ColorIndex = 3
> > > End With
> > > Cancel = True
> > > End Sub
> > >
> > > If you record a macro when you change the fill color to what you want, you'll
> > > see the number you should use.
> > >
> > > (.colorindex = 3 gave me a red fill color)
> > >
> > > Justin Abel wrote:
> > > >
> > > > Alright...i really appreciate that last post...how about when i release the
> > > > button to have it fill the cells with x, plus have it highlight the same
> > > > cells?
> > > > thank you
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > 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
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson

Justin Abel · 04-24-2006, 12:10 PM

Also...each time i open the workbook...i keep seeing the stupid template help
thing on the right hand side...how do i go about stopping that from coming up
and could you please explain the auto filter option a little clearer...thank
you!

"Dave Peterson" wrote:

> I would drop the coloring of the cells.
>
> And replace it with an autofilter.
>
> You can select your range, then apply Data|Filter|Autofilter to that range.
>
> Then you can use the arrow in the header row's cell to select the values you
> want to see (and hide the others).
>
> There's options for blanks and non-blanks, too. In fact, you can use that
> arrow, choose custom and even choose from a bunch.
>
> Justin Abel wrote:
> >
> > I have got a column list of employees on the left...is there a way...that if
> > they are not vertically sceduled to work for the day....then there name
> > dissappears....or any other suggestions besides the color lines that shows
> > they are scheduled...like if they are scheduled...then they're name is
> > highlighted....maybe a validation or something....any help would be
> > appreciated
> >
> > "Justin Abel" wrote:
> >
> > > how would i go about knowing what each color is?
> > > and can i assign like...different keys for different colors?
> > >
> > > like...make a certain range of cells so that you can only insert the right
> > > click x's in them and then use different keys to assign different colors in
> > > use with the right click function? would it be easier for me to post the file
> > > on my website for u? lol
> > >
> > > thank you
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You mean like change the fill color?
> > > >
> > > >
> > > > Option Explicit
> > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > With Selection
> > > > .Value = "X"
> > > > .Interior.ColorIndex = 3
> > > > End With
> > > > Cancel = True
> > > > End Sub
> > > >
> > > > If you record a macro when you change the fill color to what you want, you'll
> > > > see the number you should use.
> > > >
> > > > (.colorindex = 3 gave me a red fill color)
> > > >
> > > > Justin Abel wrote:
> > > > >
> > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > cells?
> > > > > thank you
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
>
> --
>
> Dave Peterson
>

Justin Abel · 04-24-2006, 12:35 PM

One more question please...before I spread this corporately...at the bottom
of my workbook is my work sheets...tuesday through monday...for the pay
period...is there a way if i create another worksheet...with a button in it
to create a blank workbook...to have it name it with the next week period
beginning date and also have it insert the previously weeks schedule but all
clear?? i know sounds complicated...any help is...as always...greatly
appreciated!

"Justin Abel" wrote:

> Also...each time i open the workbook...i keep seeing the stupid template help
> thing on the right hand side...how do i go about stopping that from coming up
> and could you please explain the auto filter option a little clearer...thank
> you!
>
> "Dave Peterson" wrote:
>
> > I would drop the coloring of the cells.
> >
> > And replace it with an autofilter.
> >
> > You can select your range, then apply Data|Filter|Autofilter to that range.
> >
> > Then you can use the arrow in the header row's cell to select the values you
> > want to see (and hide the others).
> >
> > There's options for blanks and non-blanks, too. In fact, you can use that
> > arrow, choose custom and even choose from a bunch.
> >
> > Justin Abel wrote:
> > >
> > > I have got a column list of employees on the left...is there a way...that if
> > > they are not vertically sceduled to work for the day....then there name
> > > dissappears....or any other suggestions besides the color lines that shows
> > > they are scheduled...like if they are scheduled...then they're name is
> > > highlighted....maybe a validation or something....any help would be
> > > appreciated
> > >
> > > "Justin Abel" wrote:
> > >
> > > > how would i go about knowing what each color is?
> > > > and can i assign like...different keys for different colors?
> > > >
> > > > like...make a certain range of cells so that you can only insert the right
> > > > click x's in them and then use different keys to assign different colors in
> > > > use with the right click function? would it be easier for me to post the file
> > > > on my website for u? lol
> > > >
> > > > thank you
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > You mean like change the fill color?
> > > > >
> > > > >
> > > > > Option Explicit
> > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > With Selection
> > > > > .Value = "X"
> > > > > .Interior.ColorIndex = 3
> > > > > End With
> > > > > Cancel = True
> > > > > End Sub
> > > > >
> > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > see the number you should use.
> > > > >
> > > > > (.colorindex = 3 gave me a red fill color)
> > > > >
> > > > > Justin Abel wrote:
> > > > > >
> > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > cells?
> > > > > > thank you
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

Dave Peterson · 04-24-2006, 01:20 PM

Maybe...

It would depend on how you define next week's beginning date.

This will try to use Monday (either today -- if today is a Monday, or the
upcoming Monday):

Option Explicit
Sub testme()
Dim NextWeeksMonday As Date
Dim myDate As Date
Dim wks As Worksheet

myDate = Date
NextWeeksMonday = myDate - Weekday(myDate - 2) + 7

With ActiveWorkbook
Set wks = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
On Error Resume Next
wks.Name = Format(NextWeeksMonday, "yyyy-mm-dd")
If Err.Number <> 0 Then
MsgBox "Please rename " & wks.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End With

End Sub

If today is a Monday and you want to get a week from today, then change this:
NextWeeksMonday = myDate - Weekday(myDate - 2) + 7
to
NextWeeksMonday = myDate + 1 - Weekday(myDate - 2 + 1) + 7

Justin Abel wrote:
>
> One more question please...before I spread this corporately...at the bottom
> of my workbook is my work sheets...tuesday through monday...for the pay
> period...is there a way if i create another worksheet...with a button in it
> to create a blank workbook...to have it name it with the next week period
> beginning date and also have it insert the previously weeks schedule but all
> clear?? i know sounds complicated...any help is...as always...greatly
> appreciated!
>
> "Justin Abel" wrote:
>
> > Also...each time i open the workbook...i keep seeing the stupid template help
> > thing on the right hand side...how do i go about stopping that from coming up
> > and could you please explain the auto filter option a little clearer...thank
> > you!
> >
> > "Dave Peterson" wrote:
> >
> > > I would drop the coloring of the cells.
> > >
> > > And replace it with an autofilter.
> > >
> > > You can select your range, then apply Data|Filter|Autofilter to that range.
> > >
> > > Then you can use the arrow in the header row's cell to select the values you
> > > want to see (and hide the others).
> > >
> > > There's options for blanks and non-blanks, too. In fact, you can use that
> > > arrow, choose custom and even choose from a bunch.
> > >
> > > Justin Abel wrote:
> > > >
> > > > I have got a column list of employees on the left...is there a way...that if
> > > > they are not vertically sceduled to work for the day....then there name
> > > > dissappears....or any other suggestions besides the color lines that shows
> > > > they are scheduled...like if they are scheduled...then they're name is
> > > > highlighted....maybe a validation or something....any help would be
> > > > appreciated
> > > >
> > > > "Justin Abel" wrote:
> > > >
> > > > > how would i go about knowing what each color is?
> > > > > and can i assign like...different keys for different colors?
> > > > >
> > > > > like...make a certain range of cells so that you can only insert the right
> > > > > click x's in them and then use different keys to assign different colors in
> > > > > use with the right click function? would it be easier for me to post the file
> > > > > on my website for u? lol
> > > > >
> > > > > thank you
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > You mean like change the fill color?
> > > > > >
> > > > > >
> > > > > > Option Explicit
> > > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > > With Selection
> > > > > > .Value = "X"
> > > > > > .Interior.ColorIndex = 3
> > > > > > End With
> > > > > > Cancel = True
> > > > > > End Sub
> > > > > >
> > > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > > see the number you should use.
> > > > > >
> > > > > > (.colorindex = 3 gave me a red fill color)
> > > > > >
> > > > > > Justin Abel wrote:
> > > > > > >
> > > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > > cells?
> > > > > > > thank you
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > 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
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson

Dave Peterson · 04-24-2006, 01:25 PM

Have you tried just turning it off via View|Taskpane.

It seems to stay off for me when I do that.

Justin Abel wrote:
>
> Also...each time i open the workbook...i keep seeing the stupid template help
> thing on the right hand side...how do i go about stopping that from coming up
> and could you please explain the auto filter option a little clearer...thank
> you!
>
> "Dave Peterson" wrote:
>
> > I would drop the coloring of the cells.
> >
> > And replace it with an autofilter.
> >
> > You can select your range, then apply Data|Filter|Autofilter to that range.
> >
> > Then you can use the arrow in the header row's cell to select the values you
> > want to see (and hide the others).
> >
> > There's options for blanks and non-blanks, too. In fact, you can use that
> > arrow, choose custom and even choose from a bunch.
> >
> > Justin Abel wrote:
> > >
> > > I have got a column list of employees on the left...is there a way...that if
> > > they are not vertically sceduled to work for the day....then there name
> > > dissappears....or any other suggestions besides the color lines that shows
> > > they are scheduled...like if they are scheduled...then they're name is
> > > highlighted....maybe a validation or something....any help would be
> > > appreciated
> > >
> > > "Justin Abel" wrote:
> > >
> > > > how would i go about knowing what each color is?
> > > > and can i assign like...different keys for different colors?
> > > >
> > > > like...make a certain range of cells so that you can only insert the right
> > > > click x's in them and then use different keys to assign different colors in
> > > > use with the right click function? would it be easier for me to post the file
> > > > on my website for u? lol
> > > >
> > > > thank you
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > You mean like change the fill color?
> > > > >
> > > > >
> > > > > Option Explicit
> > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > With Selection
> > > > > .Value = "X"
> > > > > .Interior.ColorIndex = 3
> > > > > End With
> > > > > Cancel = True
> > > > > End Sub
> > > > >
> > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > see the number you should use.
> > > > >
> > > > > (.colorindex = 3 gave me a red fill color)
> > > > >
> > > > > Justin Abel wrote:
> > > > > >
> > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > cells?
> > > > > > thank you
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Justin Abel · 04-24-2006, 01:35 PM

yeah...i have tried that...but everytime i restart workbook...it comes
back...i think it's because original file is template that i downloaded and
changed a whole bunch

"Dave Peterson" wrote:

> Have you tried just turning it off via View|Taskpane.
>
> It seems to stay off for me when I do that.
>
>
> Justin Abel wrote:
> >
> > Also...each time i open the workbook...i keep seeing the stupid template help
> > thing on the right hand side...how do i go about stopping that from coming up
> > and could you please explain the auto filter option a little clearer...thank
> > you!
> >
> > "Dave Peterson" wrote:
> >
> > > I would drop the coloring of the cells.
> > >
> > > And replace it with an autofilter.
> > >
> > > You can select your range, then apply Data|Filter|Autofilter to that range.
> > >
> > > Then you can use the arrow in the header row's cell to select the values you
> > > want to see (and hide the others).
> > >
> > > There's options for blanks and non-blanks, too. In fact, you can use that
> > > arrow, choose custom and even choose from a bunch.
> > >
> > > Justin Abel wrote:
> > > >
> > > > I have got a column list of employees on the left...is there a way...that if
> > > > they are not vertically sceduled to work for the day....then there name
> > > > dissappears....or any other suggestions besides the color lines that shows
> > > > they are scheduled...like if they are scheduled...then they're name is
> > > > highlighted....maybe a validation or something....any help would be
> > > > appreciated
> > > >
> > > > "Justin Abel" wrote:
> > > >
> > > > > how would i go about knowing what each color is?
> > > > > and can i assign like...different keys for different colors?
> > > > >
> > > > > like...make a certain range of cells so that you can only insert the right
> > > > > click x's in them and then use different keys to assign different colors in
> > > > > use with the right click function? would it be easier for me to post the file
> > > > > on my website for u? lol
> > > > >
> > > > > thank you
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > You mean like change the fill color?
> > > > > >
> > > > > >
> > > > > > Option Explicit
> > > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > > With Selection
> > > > > > .Value = "X"
> > > > > > .Interior.ColorIndex = 3
> > > > > > End With
> > > > > > Cancel = True
> > > > > > End Sub
> > > > > >
> > > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > > see the number you should use.
> > > > > >
> > > > > > (.colorindex = 3 gave me a red fill color)
> > > > > >
> > > > > > Justin Abel wrote:
> > > > > > >
> > > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > > cells?
> > > > > > > thank you
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > 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
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Justin Abel · 04-24-2006, 01:40 PM

where would i place this code at...in the worksheet code of my last worksheet
or on a new worksheet or what?
thank you

"Dave Peterson" wrote:

> Maybe...
>
> It would depend on how you define next week's beginning date.
>
> This will try to use Monday (either today -- if today is a Monday, or the
> upcoming Monday):
>
> Option Explicit
> Sub testme()
> Dim NextWeeksMonday As Date
> Dim myDate As Date
> Dim wks As Worksheet
>
> myDate = Date
> NextWeeksMonday = myDate - Weekday(myDate - 2) + 7
>
> With ActiveWorkbook
> Set wks = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
> On Error Resume Next
> wks.Name = Format(NextWeeksMonday, "yyyy-mm-dd")
> If Err.Number <> 0 Then
> MsgBox "Please rename " & wks.Name & " manually"
> Err.Clear
> End If
> On Error GoTo 0
> End With
>
> End Sub
>
> If today is a Monday and you want to get a week from today, then change this:
> NextWeeksMonday = myDate - Weekday(myDate - 2) + 7
> to
> NextWeeksMonday = myDate + 1 - Weekday(myDate - 2 + 1) + 7
>
>
>
>
>
> Justin Abel wrote:
> >
> > One more question please...before I spread this corporately...at the bottom
> > of my workbook is my work sheets...tuesday through monday...for the pay
> > period...is there a way if i create another worksheet...with a button in it
> > to create a blank workbook...to have it name it with the next week period
> > beginning date and also have it insert the previously weeks schedule but all
> > clear?? i know sounds complicated...any help is...as always...greatly
> > appreciated!
> >
> > "Justin Abel" wrote:
> >
> > > Also...each time i open the workbook...i keep seeing the stupid template help
> > > thing on the right hand side...how do i go about stopping that from coming up
> > > and could you please explain the auto filter option a little clearer...thank
> > > you!
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I would drop the coloring of the cells.
> > > >
> > > > And replace it with an autofilter.
> > > >
> > > > You can select your range, then apply Data|Filter|Autofilter to that range.
> > > >
> > > > Then you can use the arrow in the header row's cell to select the values you
> > > > want to see (and hide the others).
> > > >
> > > > There's options for blanks and non-blanks, too. In fact, you can use that
> > > > arrow, choose custom and even choose from a bunch.
> > > >
> > > > Justin Abel wrote:
> > > > >
> > > > > I have got a column list of employees on the left...is there a way...that if
> > > > > they are not vertically sceduled to work for the day....then there name
> > > > > dissappears....or any other suggestions besides the color lines that shows
> > > > > they are scheduled...like if they are scheduled...then they're name is
> > > > > highlighted....maybe a validation or something....any help would be
> > > > > appreciated
> > > > >
> > > > > "Justin Abel" wrote:
> > > > >
> > > > > > how would i go about knowing what each color is?
> > > > > > and can i assign like...different keys for different colors?
> > > > > >
> > > > > > like...make a certain range of cells so that you can only insert the right
> > > > > > click x's in them and then use different keys to assign different colors in
> > > > > > use with the right click function? would it be easier for me to post the file
> > > > > > on my website for u? lol
> > > > > >
> > > > > > thank you
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > You mean like change the fill color?
> > > > > > >
> > > > > > >
> > > > > > > Option Explicit
> > > > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > > > With Selection
> > > > > > > .Value = "X"
> > > > > > > .Interior.ColorIndex = 3
> > > > > > > End With
> > > > > > > Cancel = True
> > > > > > > End Sub
> > > > > > >
> > > > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > > > see the number you should use.
> > > > > > >
> > > > > > > (.colorindex = 3 gave me a red fill color)
> > > > > > >
> > > > > > > Justin Abel wrote:
> > > > > > > >
> > > > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > > > cells?
> > > > > > > > thank you
> > > > > > > >
> > > > > > > > "Dave Peterson" wrote:
> > > > > > > >
> > > > > > > > > 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
> > > > > > > > > > > > >

Dave Peterson · 04-24-2006, 01:45 PM

This code would go into a general module.

Then put a button from the forms toolbar (not the control toolbox toolbar) on
any worksheet and assign the code to that button.

Justin Abel wrote:
>
> where would i place this code at...in the worksheet code of my last worksheet
> or on a new worksheet or what?
> thank you
>
> "Dave Peterson" wrote:
>
> > Maybe...
> >
> > It would depend on how you define next week's beginning date.
> >
> > This will try to use Monday (either today -- if today is a Monday, or the
> > upcoming Monday):
> >
> > Option Explicit
> > Sub testme()
> > Dim NextWeeksMonday As Date
> > Dim myDate As Date
> > Dim wks As Worksheet
> >
> > myDate = Date
> > NextWeeksMonday = myDate - Weekday(myDate - 2) + 7
> >
> > With ActiveWorkbook
> > Set wks = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
> > On Error Resume Next
> > wks.Name = Format(NextWeeksMonday, "yyyy-mm-dd")
> > If Err.Number <> 0 Then
> > MsgBox "Please rename " & wks.Name & " manually"
> > Err.Clear
> > End If
> > On Error GoTo 0
> > End With
> >
> > End Sub
> >
> > If today is a Monday and you want to get a week from today, then change this:
> > NextWeeksMonday = myDate - Weekday(myDate - 2) + 7
> > to
> > NextWeeksMonday = myDate + 1 - Weekday(myDate - 2 + 1) + 7
> >
> >
> >
> >
> >
> > Justin Abel wrote:
> > >
> > > One more question please...before I spread this corporately...at the bottom
> > > of my workbook is my work sheets...tuesday through monday...for the pay
> > > period...is there a way if i create another worksheet...with a button in it
> > > to create a blank workbook...to have it name it with the next week period
> > > beginning date and also have it insert the previously weeks schedule but all
> > > clear?? i know sounds complicated...any help is...as always...greatly
> > > appreciated!
> > >
> > > "Justin Abel" wrote:
> > >
> > > > Also...each time i open the workbook...i keep seeing the stupid template help
> > > > thing on the right hand side...how do i go about stopping that from coming up
> > > > and could you please explain the auto filter option a little clearer...thank
> > > > you!
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > I would drop the coloring of the cells.
> > > > >
> > > > > And replace it with an autofilter.
> > > > >
> > > > > You can select your range, then apply Data|Filter|Autofilter to that range.
> > > > >
> > > > > Then you can use the arrow in the header row's cell to select the values you
> > > > > want to see (and hide the others).
> > > > >
> > > > > There's options for blanks and non-blanks, too. In fact, you can use that
> > > > > arrow, choose custom and even choose from a bunch.
> > > > >
> > > > > Justin Abel wrote:
> > > > > >
> > > > > > I have got a column list of employees on the left...is there a way...that if
> > > > > > they are not vertically sceduled to work for the day....then there name
> > > > > > dissappears....or any other suggestions besides the color lines that shows
> > > > > > they are scheduled...like if they are scheduled...then they're name is
> > > > > > highlighted....maybe a validation or something....any help would be
> > > > > > appreciated
> > > > > >
> > > > > > "Justin Abel" wrote:
> > > > > >
> > > > > > > how would i go about knowing what each color is?
> > > > > > > and can i assign like...different keys for different colors?
> > > > > > >
> > > > > > > like...make a certain range of cells so that you can only insert the right
> > > > > > > click x's in them and then use different keys to assign different colors in
> > > > > > > use with the right click function? would it be easier for me to post the file
> > > > > > > on my website for u? lol
> > > > > > >
> > > > > > > thank you
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > You mean like change the fill color?
> > > > > > > >
> > > > > > > >
> > > > > > > > Option Explicit
> > > > > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > > > > With Selection
> > > > > > > > .Value = "X"
> > > > > > > > .Interior.ColorIndex = 3
> > > > > > > > End With
> > > > > > > > Cancel = True
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > > > > see the number you should use.
> > > > > > > >
> > > > > > > > (.colorindex = 3 gave me a red fill color)
> > > > > > > >
> > > > > > > > Justin Abel wrote:
> > > > > > > > >
> > > > > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > > > > cells?
> > > > > > > > > thank you
> > > > > > > > >
> > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > >
> > > > > > > > > > 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
> > > > > > > > > > > > > >

--

Dave Peterson

Dave Peterson · 04-24-2006, 01:50 PM

Jim Rech posted this for the opposite question (how to keep it open):

If you know how to use the registry editor (Start->Run "Regedit") add the
Dword item "DoNotDismissFileNewTaskPane" under
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\General and assign
it the value 1.

====
11.0 is xl2003. You'd use 10.0 for xl2002.

========
So maybe using a value of 0 would work for you.

This is a guess. So backup your registry first and put if it doesn't work,
either restore the registry or reverse your changes.

Justin Abel wrote:
>
> yeah...i have tried that...but everytime i restart workbook...it comes
> back...i think it's because original file is template that i downloaded and
> changed a whole bunch
>
> "Dave Peterson" wrote:
>
> > Have you tried just turning it off via View|Taskpane.
> >
> > It seems to stay off for me when I do that.
> >
> >
> > Justin Abel wrote:
> > >
> > > Also...each time i open the workbook...i keep seeing the stupid template help
> > > thing on the right hand side...how do i go about stopping that from coming up
> > > and could you please explain the auto filter option a little clearer...thank
> > > you!
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I would drop the coloring of the cells.
> > > >
> > > > And replace it with an autofilter.
> > > >
> > > > You can select your range, then apply Data|Filter|Autofilter to that range.
> > > >
> > > > Then you can use the arrow in the header row's cell to select the values you
> > > > want to see (and hide the others).
> > > >
> > > > There's options for blanks and non-blanks, too. In fact, you can use that
> > > > arrow, choose custom and even choose from a bunch.
> > > >
> > > > Justin Abel wrote:
> > > > >
> > > > > I have got a column list of employees on the left...is there a way...that if
> > > > > they are not vertically sceduled to work for the day....then there name
> > > > > dissappears....or any other suggestions besides the color lines that shows
> > > > > they are scheduled...like if they are scheduled...then they're name is
> > > > > highlighted....maybe a validation or something....any help would be
> > > > > appreciated
> > > > >
> > > > > "Justin Abel" wrote:
> > > > >
> > > > > > how would i go about knowing what each color is?
> > > > > > and can i assign like...different keys for different colors?
> > > > > >
> > > > > > like...make a certain range of cells so that you can only insert the right
> > > > > > click x's in them and then use different keys to assign different colors in
> > > > > > use with the right click function? would it be easier for me to post the file
> > > > > > on my website for u? lol
> > > > > >
> > > > > > thank you
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > You mean like change the fill color?
> > > > > > >
> > > > > > >
> > > > > > > Option Explicit
> > > > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > > > With Selection
> > > > > > > .Value = "X"
> > > > > > > .Interior.ColorIndex = 3
> > > > > > > End With
> > > > > > > Cancel = True
> > > > > > > End Sub
> > > > > > >
> > > > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > > > see the number you should use.
> > > > > > >
> > > > > > > (.colorindex = 3 gave me a red fill color)
> > > > > > >
> > > > > > > Justin Abel wrote:
> > > > > > > >
> > > > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > > > cells?
> > > > > > > > thank you
> > > > > > > >
> > > > > > > > "Dave Peterson" wrote:
> > > > > > > >
> > > > > > > > > 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
> > > > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Justin Abel · 04-24-2006, 02:55 PM

I'm sorry...hate to be a neussance...i tried that but to no resolve...but
it's okay...i just mainly need to figure out how to create an identical
workbook...via a hyperlink or button...that when i click...it copies my
entire workbook...but with the scheduled hours cleared! here is a screen shot
of my worksheet...i need this to open with all the same work sheets (cleared)
in a new work book that i can save and name as a different period!
thank you!
http://abelscomputerservice.tripod.com/Pages/forum.htm

"Dave Peterson" wrote:

> Jim Rech posted this for the opposite question (how to keep it open):
>
> If you know how to use the registry editor (Start->Run "Regedit") add the
> Dword item "DoNotDismissFileNewTaskPane" under
> HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\General and assign
> it the value 1.
>
> ====
> 11.0 is xl2003. You'd use 10.0 for xl2002.
>
> ========
> So maybe using a value of 0 would work for you.
>
> This is a guess. So backup your registry first and put if it doesn't work,
> either restore the registry or reverse your changes.
>
>
> Justin Abel wrote:
> >
> > yeah...i have tried that...but everytime i restart workbook...it comes
> > back...i think it's because original file is template that i downloaded and
> > changed a whole bunch
> >
> > "Dave Peterson" wrote:
> >
> > > Have you tried just turning it off via View|Taskpane.
> > >
> > > It seems to stay off for me when I do that.
> > >
> > >
> > > Justin Abel wrote:
> > > >
> > > > Also...each time i open the workbook...i keep seeing the stupid template help
> > > > thing on the right hand side...how do i go about stopping that from coming up
> > > > and could you please explain the auto filter option a little clearer...thank
> > > > you!
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > I would drop the coloring of the cells.
> > > > >
> > > > > And replace it with an autofilter.
> > > > >
> > > > > You can select your range, then apply Data|Filter|Autofilter to that range.
> > > > >
> > > > > Then you can use the arrow in the header row's cell to select the values you
> > > > > want to see (and hide the others).
> > > > >
> > > > > There's options for blanks and non-blanks, too. In fact, you can use that
> > > > > arrow, choose custom and even choose from a bunch.
> > > > >
> > > > > Justin Abel wrote:
> > > > > >
> > > > > > I have got a column list of employees on the left...is there a way...that if
> > > > > > they are not vertically sceduled to work for the day....then there name
> > > > > > dissappears....or any other suggestions besides the color lines that shows
> > > > > > they are scheduled...like if they are scheduled...then they're name is
> > > > > > highlighted....maybe a validation or something....any help would be
> > > > > > appreciated
> > > > > >
> > > > > > "Justin Abel" wrote:
> > > > > >
> > > > > > > how would i go about knowing what each color is?
> > > > > > > and can i assign like...different keys for different colors?
> > > > > > >
> > > > > > > like...make a certain range of cells so that you can only insert the right
> > > > > > > click x's in them and then use different keys to assign different colors in
> > > > > > > use with the right click function? would it be easier for me to post the file
> > > > > > > on my website for u? lol
> > > > > > >
> > > > > > > thank you
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > You mean like change the fill color?
> > > > > > > >
> > > > > > > >
> > > > > > > > Option Explicit
> > > > > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > > > > With Selection
> > > > > > > > .Value = "X"
> > > > > > > > .Interior.ColorIndex = 3
> > > > > > > > End With
> > > > > > > > Cancel = True
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > > > > see the number you should use.
> > > > > > > >
> > > > > > > > (.colorindex = 3 gave me a red fill color)
> > > > > > > >
> > > > > > > > Justin Abel wrote:
> > > > > > > > >
> > > > > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > > > > cells?
> > > > > > > > > thank you
> > > > > > > > >
> > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > >
> > > > > > > > > > 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 · 04-24-2006, 04:30 PM

Maybe just a macro that does something like this:

Option Explicit
Sub testme01()

With ThisWorkbook
With .Worksheets("sheet1")
.Range("a1:c99").ClearContents
End With
With .Worksheets("sheet99")
.Range("a1,b9,c33").ClearContents
End With
End With

Application.Dialogs(xlDialogSaveAs).Show

End Sub

You'll have to adjust the worksheet names and ranges to clear.

Justin Abel wrote:
>
> I'm sorry...hate to be a neussance...i tried that but to no resolve...but
> it's okay...i just mainly need to figure out how to create an identical
> workbook...via a hyperlink or button...that when i click...it copies my
> entire workbook...but with the scheduled hours cleared! here is a screen shot
> of my worksheet...i need this to open with all the same work sheets (cleared)
> in a new work book that i can save and name as a different period!
> thank you!
> http://abelscomputerservice.tripod.com/Pages/forum.htm
>
> "Dave Peterson" wrote:
>
> > Jim Rech posted this for the opposite question (how to keep it open):
> >
> > If you know how to use the registry editor (Start->Run "Regedit") add the
> > Dword item "DoNotDismissFileNewTaskPane" under
> > HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\General and assign
> > it the value 1.
> >
> > ====
> > 11.0 is xl2003. You'd use 10.0 for xl2002.
> >
> > ========
> > So maybe using a value of 0 would work for you.
> >
> > This is a guess. So backup your registry first and put if it doesn't work,
> > either restore the registry or reverse your changes.
> >
> >
> > Justin Abel wrote:
> > >
> > > yeah...i have tried that...but everytime i restart workbook...it comes
> > > back...i think it's because original file is template that i downloaded and
> > > changed a whole bunch
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Have you tried just turning it off via View|Taskpane.
> > > >
> > > > It seems to stay off for me when I do that.
> > > >
> > > >
> > > > Justin Abel wrote:
> > > > >
> > > > > Also...each time i open the workbook...i keep seeing the stupid template help
> > > > > thing on the right hand side...how do i go about stopping that from coming up
> > > > > and could you please explain the auto filter option a little clearer...thank
> > > > > you!
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > I would drop the coloring of the cells.
> > > > > >
> > > > > > And replace it with an autofilter.
> > > > > >
> > > > > > You can select your range, then apply Data|Filter|Autofilter to that range.
> > > > > >
> > > > > > Then you can use the arrow in the header row's cell to select the values you
> > > > > > want to see (and hide the others).
> > > > > >
> > > > > > There's options for blanks and non-blanks, too. In fact, you can use that
> > > > > > arrow, choose custom and even choose from a bunch.
> > > > > >
> > > > > > Justin Abel wrote:
> > > > > > >
> > > > > > > I have got a column list of employees on the left...is there a way...that if
> > > > > > > they are not vertically sceduled to work for the day....then there name
> > > > > > > dissappears....or any other suggestions besides the color lines that shows
> > > > > > > they are scheduled...like if they are scheduled...then they're name is
> > > > > > > highlighted....maybe a validation or something....any help would be
> > > > > > > appreciated
> > > > > > >
> > > > > > > "Justin Abel" wrote:
> > > > > > >
> > > > > > > > how would i go about knowing what each color is?
> > > > > > > > and can i assign like...different keys for different colors?
> > > > > > > >
> > > > > > > > like...make a certain range of cells so that you can only insert the right
> > > > > > > > click x's in them and then use different keys to assign different colors in
> > > > > > > > use with the right click function? would it be easier for me to post the file
> > > > > > > > on my website for u? lol
> > > > > > > >
> > > > > > > > thank you
> > > > > > > >
> > > > > > > > "Dave Peterson" wrote:
> > > > > > > >
> > > > > > > > > You mean like change the fill color?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Option Explicit
> > > > > > > > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
> > > > > > > > > With Selection
> > > > > > > > > .Value = "X"
> > > > > > > > > .Interior.ColorIndex = 3
> > > > > > > > > End With
> > > > > > > > > Cancel = True
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > If you record a macro when you change the fill color to what you want, you'll
> > > > > > > > > see the number you should use.
> > > > > > > > >
> > > > > > > > > (.colorindex = 3 gave me a red fill color)
> > > > > > > > >
> > > > > > > > > Justin Abel wrote:
> > > > > > > > > >
> > > > > > > > > > Alright...i really appreciate that last post...how about when i release the
> > > > > > > > > > button to have it fill the cells with x, plus have it highlight the same
> > > > > > > > > > cells?
> > > > > > > > > > thank you
> > > > > > > > > >
> > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > >
> > > > > > > > > > > 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

[SOLVED] Creating a tick by clicking a cell

LinkBack

Thread Tools

Rate This Thread

Display

[SOLVED] Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Re: Creating a tick by clicking a cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions