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