Creating a tick by clicking a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have designed a schedule form for my business using excel 2003 and i would
like to know how i can click a cell (a time block) and have it place a
pre-defined character into the cell such as an (on event)
function...please...any help would be appreciated!
 
There isn't a single click event that you can tie into. But you can tie into a
doubleclick or rightclick event.

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

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

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

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

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

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

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

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

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

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

End Sub

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

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


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

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

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

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
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
 
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
 
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
 
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 said:
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
Click to expand...
 
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
 
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 said:
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
Click to expand...
 
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
 
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 said:
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
Click to expand...
 
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.
 
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!
 
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 said:
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!
Click to expand...
 
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
 
Have you tried just turning it off via View|Taskpane.

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


Justin said:
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!
Click to expand...
 
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
 
where would i place this code at...in the worksheet code of my last worksheet
or on a new worksheet or what?
thank you
 
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.
 
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 said:
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
Click to expand...
 
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
 
Back
Top