|
Creating a tick by clicking a cell
I have designed a schedule form for my business using excel 2003 and i would
like to know how i can click a cell (a time block) and have it place a pre-defined character into the cell such as an (on event) function...please...any help would be appreciated! |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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 |
Creating a tick by clicking a cell
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\C ommon\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 |
Creating a tick by clicking a cell
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\C ommon\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 |
Creating a tick by clicking a cell
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\C ommon\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 |
| All times are GMT +1. The time now is 12:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com