PDA

View Full Version : Solved: Timesheet calculation using forms accounting for sick days



ndendrinos
01-06-2007, 09:47 PM
Hello to all.
Need help in finishing this project.
If you double click on "Nick" or "Max" and choose a reason for the employee not showing up for work the reason itself will fill the small gold squares and the times In/Out & Lunch will clear around them.
The Total Reg Hrs will be reduced from 40 Hrs to Nil.

If you doubleclick one small square again you will have the choice of choosing the reason why the employee has not shawn up for work on that particular day only and again the same thing will happen as above. ( I thank Pete (XLGibbs for his assistance with this and more in this chalenge )

All reasons are paid for by the company except "SUA" (stands for scheduled unpaid absence)

Using UserForm2 I need to calculate the value of the absences for each employee in the large gold rectangle named Total ABS.

Some employees work a 40 Hrs week while others work 37 and a half and some part time employees work 3 days a week.

I fear that even the genius from Le Hague would not be able to come up with a formula ... so how can one use code here if at all possible that is and solve this one ?

Thank you.

XLGibbs
01-06-2007, 10:08 PM
Well Nick. Speaking of overtime...I will take a peek around in there. See what I have been working on all this time.

XLGibbs
01-06-2007, 10:17 PM
Nick...I double click, and can choose a reason, then it pops up with that Pay or DOn't pay buttons.

Are those the buttons in question here? or do you need a formula?

How based on the information in this workbook do we identify how many hours they are to be paid.

My suggestion is this.
-Allow the reason for sick day to be dropped as per the double click.
-if the reason is one of the pay reasons (that is NOT SUA), then drop the "Work" hours in the cells instead of the sick day reason (or don't clear them)

Only if the SUA button is clicked should the day come completely off the books--correct?

Assuming you pay sick days for full shifted folks, it would seem the 8:00 to 16:00 workday would be consistent. (or 8:00 and 15:30 to account for the lunch thing)..

It would be much easier to just place the hours there anyway (and have the sick day code in the little orange cell)

Make sense?

ndendrinos
01-07-2007, 08:58 AM
Goodmorning Pete,

Thank you for your suggestions and based on them I've introduced a new column titled "Total Worked Hrs" .If possible I would like to keep this extra column for I have a good use for it.
The example in this attachment shows the desired end result.

Discontinued is the clearing of the cells adjacent to the ABS (except for SUA) ....
( I'll fix the code later on)

Form2 stays in for now but most likely I will have no use for it at the end.

The problem now is to give a value to the green cells for every instance of an ABS
based on the hours the employee works. (OUT less IN less Lunch)

If you find it easier NOT to clear cells adjacent to "SUA" that is OK by me (In that case I guess it would have to be a conditional solution.)

Thanks again.

XLGibbs
01-07-2007, 09:04 AM
Let's go one step at a time...what do you need my help with first?

XLGibbs
01-07-2007, 09:13 AM
I am not sure what you mean by giving a value to the green cells. I would think that you would use a formula of some kind, leaving the absent codes in place, but using the <> "SUA" as an exlcusion from the total since that is the only unpaid absence item.

ndendrinos
01-07-2007, 09:43 AM
Pete, the genial part of my brain (very small) is working at full capacity this morning and I think I've got the right concept and with your help I'll be done today.
This sheet needs an extra cell in the row of each employee (example for Nick in AA4) with it's value being "Out less In less lunch." (for Nick = 8:00) for (Max=7:30)

I need to introduce the code of this attachment to my forms 1 and 2 and using it I will populate the green cell for each employee accordingly and if correct be done with this chalenge.

"SUA" will not patcipate and will just clear the adjacent cells.

XLGibbs
01-07-2007, 09:58 AM
Okay, so the code in that attachment (haven't looked yet) is to be incorporated into populating the green cells?

And a formula in one cell that resolves Out less in less lunch...is there a location for this result yet?

ndendrinos
01-07-2007, 09:59 AM
Here is the code now in Form3 for "VAC" and it works.
Private Sub CommandButton1_Click()
Dim r As Long

r = ActiveCell.Row

With ActiveSheet
ActiveCell.FormulaR1C1 = "VAC"
Range("AA4").Copy
Range("O4").Select
ActiveSheet.Paste
Range("A1").Select
End With

Unload Me


End Sub


What's now left then is to make the same concept work for Form1 ... problem here is that I use an array like you suggested and paste all five days at once ... maybe if I change the code to enter VAC cell by cell thus incrementing the green cell 8:00 hrs at a time ?

ndendrinos
01-07-2007, 10:08 AM
Sorry was typing at the same time you were.
The location is AA4 for Nick

Here is the latest revision and it seems to work with FORM3 for "VAC" I will edit the rest of the code to work the same way for all except for SUA that will just clear the adjacent cells.

IMPORTANT REVISION : to retain the old value of AA the Sheet code provided in the attachment Retain Old and Add New needs to be edited to work just in column "AA"

If you approve then the last hurdle is to make Form1 work the same way.

ndendrinos
01-07-2007, 10:26 AM
Revised twice my last post

ndendrinos
01-07-2007, 10:42 AM
Thought that somehow my posting got lost.... could not find it under the original title ,,, to the one that revised it ... thank you.

Here is the sheet code and it works.


Public oldVal As Variant
___________________________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 15 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value + oldVal
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 15 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
oldVal = Target.Value
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Union(Range("a1:a20"), Range("B1:Z100"))) Is Nothing Then Exit Sub

Select Case Target.Column
Case 1
UserForm1.Show

Case Else
UserForm3.Show

End Select



End Sub

ndendrinos
01-07-2007, 10:53 AM
Temporary setback ... somehow I cannot clear the value in the green cell while accepting macros ... I have to reopen the book choose Not to accpet macros and then clear the green cell ... can this be addressed ? Not crucial but annoying.
Thanks

ndendrinos
01-07-2007, 11:03 AM
I'm Working on Form1 now and for VAC only and of course the way it is it adds just one
8:00 in the green cell instead of 5 times 8:00 = 40:00

How can I change the code to make it work?
_________________________________________________________________________

Private Sub CommandButton1_Click()
Dim r As Long

r = ActiveCell.Row

With ActiveSheet
Union(.Cells(r, 4), .Cells(r, 6), .Cells(r, 8), .Cells(r, 10), .Cells(r, 12)).Value = "VA"
Range("AA4").Copy
Range("O4").Select
ActiveSheet.Paste

End With
[A1].Select

Unload Me
UserForm2.Show
End Sub

ndendrinos
01-07-2007, 11:06 AM
Pete and All
Sorry but I have to leave for a couple of hours.
Thanks

XLGibbs
01-07-2007, 02:55 PM
Thought that somehow my posting got lost.... could not find it under the original title ,,, to the one that revised it ... thank you.

Here is the sheet code and it works.




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 15 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
oldVal = Target.Value
End Sub



Refresh my memory why this is needed?



I'm Working on Form1 now and for VAC only and of course the way it is it adds just one
8:00 in the green cell instead of 5 times 8:00 = 40:00

How can I change the code to make it work?
_________________________________________________________________________

Private Sub CommandButton1_Click()
Dim r As Long

r = ActiveCell.Row

With ActiveSheet
Union(.Cells(r, 4), .Cells(r, 6), .Cells(r, 8), .Cells(r, 10), .Cells(r, 12)).Value = "VA"
Range("AA4").Copy
Range("O4").Select
ActiveSheet.Paste

End With
[A1].Select
Unload Me
UserForm2.Show
End Sub

With ActiveSheet
Union(.Cells(r, 4), .Cells(r, 6), .Cells(r, 8), .Cells(r, 10), .Cells(r, 12)).Value = "VA"

[O4].Value = [AA4].Value
'you don't need to copy/paste..just do the above..

End With
[A1].Select
Unload Me 'you might want to leave the other form in memory and just use Me.Hide
UserForm2.Show

End Sub



But what why it is not showing the correct hours total? Are you supposed to be adding them with this code above? the code isn't doing any math....is Cell AA4 calculatiing anything? is that why it gets copied and pasted to O4?

I don't want to mess with the last version as it seems you have made changes..

ndendrinos
01-07-2007, 03:40 PM
The code in UserForm3 is OK for Nick Only and works fine with any choice of ABS
But I should have done my homework before declaring victory with this one and the reason is that if I choose to run this code for (example) with Mary it will copy the value in AA4 and paste it in O4 INSTEAD of copying the value in AA8 and paste it in O8.

Something has to be added to the code that will make it wotk on the ROW chosen with the selection of any given green cell.... in other words instead of saying:
AA4 I would say (active row column 37)
and instead of O8 I would say: (active row column15)

The formula in AA4 is just Nick's value for his "OUT less his IN less his 30min lunch"
Does not have to be a formula... could also be entered manually.

Your first quote : Refresh my memory why this is needed?
The sheet code retains the value in O4 for Nick and each time using form3 I add an ABS for Nick it pastes the value in AA4 to OA

So again using UserForm3 with the first ABS the value in O4 will be 8:00 but the second ABS will change it to 16:00 since it adds to the preceding value (that's remembered thanks to the sheet code.

Pete on top of helping out you show tremendous patience with me here


Private Sub CommandButton1_Click()
Dim r As Long

r = ActiveCell.Row

With ActiveSheet
ActiveCell.FormulaR1C1 = "VAC"
Range("AA4").Copy
Range("O4").Select
ActiveSheet.Paste
Range("A1").Select
End With

Unload Me


End Sub

XLGibbs
01-07-2007, 03:44 PM
Private Sub CommandButton1_Click()
Dim r As Range
With activeSheet

Set r = ActiveCell

r.text = "VAC"
Range("O" & r.Row) = Range("AA" & r.Row)

Range("A1").Select 'sure you want this? .Activate is probably better

End With

Unload Me


End Sub

ndendrinos
01-07-2007, 04:14 PM
Tried this last suggestion and got : Runtime Error 424 / Object required
and the yellow line is : r text = "VAC"

XLGibbs
01-07-2007, 04:21 PM
You are missing the period?

r.text = "VAC"

ndendrinos
01-07-2007, 04:27 PM
My bad ... in the code the period IS there.

XLGibbs
01-07-2007, 04:33 PM
Are you sure you used all of the code?

You have to make sure:


Dim r as Range



If you left it as:
Dim r as Long

you would get the error..

ndendrinos
01-07-2007, 04:36 PM
I think I have it right... here is a copy
Private Sub CommandButton1_Click()
Dim r As Range
With ActiveSheet

Set r = ActiveCell

r.Text = "VAC"
Range("O" & r.Row) = Range("AA" & r.Row)

Range("A1").Select 'sure you want this? .Activate is probably better

End With

Unload Me


End Sub

XLGibbs
01-07-2007, 04:40 PM
:cuckoo:


Entirely my bad, I think.

Replace r.text with r.value

ndendrinos
01-07-2007, 04:55 PM
Edited to value and it works BUT (another one I'm afraid)

If Iunderstand your code correctly then I think I know why it does not address the purpose here.
Assuming that "Range("O" & r.Row) = Range("AA" & r.Row)" means O=AA then
it needs to be replace by: Copy Range AA on the row that's the selected cell is in ... and paste it in the same row Range O. That should maybe do the trick.

ndendrinos
01-07-2007, 05:07 PM
Got your message also ... not clear how to answer it (never used it)I keep clicking on the button "quick reply to this message" and nothing happens.
I have not included i.e. Yahoo Messenger in my profile on this board ... not sure how to do it ...

ndendrinos
01-07-2007, 05:18 PM
I'll eat my hat if I pronounced victory too soon again but:
Private Sub CommandButton1_Click()
Dim r As Range
With ActiveSheet

Set r = ActiveCell

r.Value = "VAC"
Range("AA" & r.Row).Copy
Range("O" & r.Row).Select
ActiveSheet.Paste


Range("A1").Select 'sure you want this? .Activate is probably better

End With

Unload Me


End Sub
Seems to be working.

XLGibbs
01-07-2007, 05:21 PM
[quote=ndendrinos]I'll eat my hat if I pronounced victory too soon again but:
Range("AA" & r.Row).Copy
Range("O" & r.Row).Select
ActiveSheet.Paste



End Sub

is identical to:


Range("O" & r.Row).Value = Range("AA" & r.Row).Value

Trust me. You do not have to Copy -- Select -- Paste like that.

I think I left of the .Value needed before that would prevent the actual transfer of the value if it was omitted.

ndendrinos
01-07-2007, 05:51 PM
Then I did something wrong.
Here is revision three.
Please Double click any ABS cells (Green) in Nick Max or Mary and choose VAC
Do the same and choose PSIC ... you should get different results.

Not good ... I've reached the max upload for this posting.
Let's try this then.
In userform3 use my copy/paste for VAC and your way for PSIC you should get different results.
Before you test pls ensure that for Nick, Max and mary you have for example 2:00 in range AA . PSIC might work once BUT not the second time, whereas VAC will work all times.
Please let me know and thanks

EDITING: Attachment here after all.

XLGibbs
01-07-2007, 06:08 PM
Couple questions..

The value in AA, what does it represent, and is it supposed to be different for each person?

And to that end...is it the same for any missed reason other than SUA?


I have an alternative means of resolving this that will be far simpler but I want to be sure what is supposed to happen (time wise, as far as the running total in the ABS cell and how much time people get credit for on a PAID day..SUA gets 0..)

ndendrinos
01-07-2007, 06:23 PM
AA represents the number of Hrs paid for an employee.
The full time employees work 8:30 and get paid 8:00
The part time employees work 8:00 ans get paid 7:30
So you are right AA will differ for each employee and that is why I've used a formula
in that cell (OUT less INN less Lunch)
Nick comes in at 8:30 leaves at 5PM has half Hr lunch / Formula = =SUM(C5-C4)-D5

Edit : I have replaced the attachment thanks for pointing this out.

This applies to all ABS reasons except SUA that you are correct do not get paid.

XLGibbs
01-07-2007, 06:39 PM
Hookin a brotha up.

Here is the form code, in it's entirety. The SheetChange and SheetSelectionChange events on the worksheet or not necessary any more.

I renamed your buttons from CommandButton1 etc to more intuitive names so the code is easier to "see". For exampe the Vacation button is cmdVAC

I also moved all of the "action" that is represented in your Sheet change codes (and selection change) codes into one subroutine that is called by each button click (other than Exit)

I consolidated your 6 modules into Module1. No sense having too many..you might want to remove the unecessary code.

The only remaining issue is what to do with the unpaid day..and I think I have an idea for that..but I wanted you to check this out in the meantime.


'Macros by Nick Dendrinos 12-31-2007

Private Sub cmdVAC_Click()
PlaceTimesAndType "VAC", ActiveCell

Unload Me
End Sub

Private Sub cmdPSIC_Click()
PlaceTimesAndType "PSIC", ActiveCell

Unload Me
End Sub

Private Sub cmPD_Click()
PlaceTimesAndType "PD", ActiveCell

Unload Me
End Sub

Private Sub cmdBERV_Click()
PlaceTimesAndType "BE", ActiveCell

Unload Me
End Sub
Private Sub cmdJURY_Click()
PlaceTimesAndType "Jury", ActiveCell

Unload Me
End Sub

Private Sub cmdFLOAT_Click()
PlaceTimesAndType "FL", ActiveCell

Unload Me
End Sub

Private Sub cmdExit_Click()
Unload Me
End Sub


Private Sub cmdSUA_Click()
PlaceTimesAndType "SUA", ActiveCell
Unload Me
End Sub

Private Sub PlaceTimesAndType(pType As String, myCell As Range)
Dim ws As Worksheet
Set ws = ActiveSheet
With ws

myCell.Value = pType
Select Case pType
Case Is = "SUA"
'Do Nothing for now, pending what needs to be done...we can do that on
'the forum. I think you need to subtract AA from hours worked somehow
'we can discuss this.
Case Else
'the below replaces the Sheet_SelectionChange event (Now commented out)
.Range("O" & myCell.Row).Value = Range("O" & myCell.Row).Value + .Range("AA" & myCell.Row).Value

.Cells(1, 1).Activate 'activate range A1
End Select

End With

End Sub

ndendrinos
01-07-2007, 07:19 PM
Pete, this is just awesome , thanks a lot.
Tested and it works great ... will have to look at it in details to learn more in the next days.
For the unpaid day, why not just clear the adjacent cells ?Doing so will reduce the Reg Hrs column N by that much ... that's good don't you think?

You are the boss, you decide then.

The last hurdle left here is this: (from the original code)
When using UserForm1, choosing the reason and executing the macro for the whole week it fills the five cells but adds only one day's hrs to Column O (value AA)
instead of AA times five.

Not sure if you feel like tinkering with the code to your liking or just add to it so that it pastes in O (simply put) AA*5

Best regards
Nick

XLGibbs
01-07-2007, 07:51 PM
Okay, to clear the adjacent cell just put change the
'Do Nothing for now, pending what needs to be done...we can do that on
'the forum. I think you need to subtract AA from hours worked somehow
'we can discuss this.


to


MyCell.Offset(,-1).ClearContents 'will clear 1 column to the left of clicked cell.
As far as the other part..well I was gonna talk to you about that...

Both forms are identical, except 1 does stuff for the week, the other does stuff for just that day.

How about I tweek the code (just a little more) and shoot you another sample. It will make sense when I am done and you see it but:

1 form with a check box for Weekly or Today Only.

Depending on which box is checked, that can be another variable passed to the one subroutine and then essentially, one module does all the work. The rest just send it information.

I will tweak and return later...

ndendrinos
01-07-2007, 07:59 PM
YES that IS the right approach.
Will do the revision to clear the adjacent cells for SUA as it reduces BOTH Reg Hrs as
well as Worked Hrs.
I've been watching your contributions on both boards ... talk of multitasking.

XLGibbs
01-07-2007, 08:31 PM
Okay, made some minor changes.

Now UserForm3 is the real deal. Does it all. Two lables for CLICK to Do what. Public boolean variables for week and day choices. Play with it..works pretty good. I am pretty sure the weekly actions work right,but just let me know.

UserForm1 removed. Move your changes to this file....(Attached)

I meant to add 40 hours for myself to your schedule but opted for paid vacation instead :)


Multitasking? Yeah. I also wrote a 800 line stored procedure (SQL Server) for work and got my ass handed to me in Candyland.

A label at the bottom will change as it is clicked.

The Form code now looks like thisPublic WeekTrue As Boolean
Public DayTrue As Boolean

'Macros by Nick Dendrinos 12-31-2007

Private Sub cmdVAC_Click()
PlaceTimesAndType "VAC", ActiveCell

Unload Me
End Sub

Private Sub cmdPSIC_Click()
PlaceTimesAndType "PSIC", ActiveCell

Unload Me
End Sub

Private Sub cmPD_Click()
PlaceTimesAndType "PD", ActiveCell

Unload Me
End Sub

Private Sub cmdBERV_Click()
PlaceTimesAndType "BE", ActiveCell

Unload Me
End Sub
Private Sub cmdJURY_Click()
PlaceTimesAndType "Jury", ActiveCell

Unload Me
End Sub

Private Sub cmdFLOAT_Click()
PlaceTimesAndType "FL", ActiveCell

Unload Me
End Sub

Private Sub cmdExit_Click()
Unload Me
End Sub


Private Sub cmdSUA_Click()
PlaceTimesAndType "SUA", ActiveCell
Unload Me
End Sub

Private Sub PlaceTimesAndType(pType As String, myCell As Range)
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
If DayTrue = True Then
myCell.Value = pType
Select Case pType
Case Is = "SUA"
.Range(Cells(myCell.Row, myCell.Column - 1), Cells(myCell.Row + 1, myCell.Column - 1)).ClearContents
Case Else
'the below replaces the Sheet_SelectionChange event (Now commented out)
.Range("O" & myCell.Row).Value = Range("O" & myCell.Row).Value + .Range("AA" & myCell.Row).Value

.Cells(1, 1).Activate 'activate range A1
End Select

End If

If WeekTrue = True Then
myCell.Value = pType
Select Case pType
Case Is = "SUA"

'set the pType
Union(.Cells(myCell.Row, 4), .Cells(myCell.Row, 6), .Cells(myCell.Row, 8), _
.Cells(myCell.Row, 10), .Cells(myCell.Row, 12)).Value = pType

'clear the time slots
Union(.Cells(myCell.Row, 3), .Cells(myCell.Row, 5), .Cells(myCell.Row, 7), _
.Cells(myCell.Row, 9), .Cells(myCell.Row, 11)).ClearContents

Union(.Cells(myCell.Row + 1, 3), .Cells(myCell.Row + 1, 5), .Cells(myCell.Row + 1, 7), _
.Cells(myCell.Row + 1, 9), .Cells(myCell.Row + 1, 11)).ClearContents

'Set range "O" and row to zero?
.Range("O" & myCell.Row).Value = 0

Case Else
Union(.Cells(myCell.Row, 4), .Cells(myCell.Row, 6), .Cells(myCell.Row, 8), _
.Cells(myCell.Row, 10), .Cells(myCell.Row, 12)).Value = pType
.Range("O" & myCell.Row).Value = Range("O" & myCell.Row).Value + .Range("AA" & myCell.Row).Value * 5
End Select
End If
[A1].Activate
End With
End Sub



Private Sub lblDay_Click()
a = MsgBox("Please confirm you wish to add ABS for DAY", vbYesNo, "CONFIRMATION!")
If a = vbNo Then Exit Sub
lblDayOrWeek.Caption = "ABS for DAY ONLY"
WeekTrue = False
DayTrue = True
End Sub



Private Sub lblWeek_Click()
a = MsgBox("Please confirm you wish to add ABS for WEEK", vbYesNo, "CONFIRMATION!")
If a = vbNo Then Exit Sub
lblDayOrWeek.Caption = "ABS for WEEK"
DayTrue = False
WeekTrue = True
End Sub



Private Sub UserForm_Initialize()
DayTrue = True
WeekTrue = False

End Sub

XLGibbs
01-07-2007, 08:52 PM
Case Else
Union(.Cells(myCell.Row, 4), .Cells(myCell.Row, 6), .Cells(myCell.Row, 8), _
.Cells(myCell.Row, 10), .Cells(myCell.Row, 12)).Value = pType
.Range("O" & myCell.Row).Value = .Range("AA" & myCell.Row).Value * 5
End Select

Nick, minor change above for the SUA weekly thing...I was ADDING Range("O" & mycell.Row) to the AA * 5...should just be AA * 5

CodeMakr
01-08-2007, 02:07 PM
Just a quick time saver, instead of seperate lines for copy/select/paste.
Range("AA4").Copy Range("O4")
Copy/paste in one line.

XLGibbs
01-08-2007, 02:14 PM
Just a quick time saver, instead of seperate lines for copy/select/paste.
Range("AA4").Copy Range("O4") Copy/paste in one line.
A good tip, and one many forget about as the recorder that some beginners use records a lot of unnessary code.

But that issue doesn't appear in the code I provided in my version of the most recent update, and I had gone through that with Nick off-forum as well.

Also:


Range("O4").value = Range("AA4").Value

No copy/paste needed.

In this case, the OP needed it to be variable as to the row..so the code I use in the procedure I posted above,which includes the needed Addition of what already exists in Range("O" and row) to the value in Range("AA"). A copy paste would overwrite the data...

above is:
.Range("O" & myCell.Row).Value = Range("O" & myCell.Row).Value + .Range("AA" & myCell.Row).Value

CodeMakr
01-08-2007, 03:11 PM
My apologies Pete. I didn't realize the second page of posts, and only referenced Nick's code on the first page, not your latest/greatest. Didn't mean to stear in the wrong direction or step on toes.

XLGibbs
01-08-2007, 03:15 PM
My apologies Pete. I didn't realize the second page of posts, and only referenced Nick's code on the first page, not your latest/greatest. Didn't mean to stear in the wrong direction or step on toes.

No worries! and you didn't step on my toes....just thought I would give you a little nod and some tips for you as well. Like I said in the post, Nick and I discussed this in a few PM's (the not needing multiple lines to copy/paste ), and it is a good tip that most people aren't arare of.

It helps to read the full thread before jumping in, but certainly the more the merrier...Nick has been stuck with me long enough on this one! LOL.

:beerchug:

CodeMakr
01-08-2007, 03:21 PM
Thanks for the tips....I can certainly use all I can get :doh: Some great stuff in this thread :clap:

XLGibbs
01-08-2007, 04:49 PM
Minor adjustments needed..

type for one command button corrected.

Correction made to the SUA clearcontents coding for this procedure:
Private Sub PlaceTimesAndType(pType As String, myCell As Range)
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
If DayTrue = True Then
myCell.Value = pType
Select Case pType
Case Is = "SUA"
With myCell
.Offset(1, 0).ClearContens
.Offset(0, -1).Resize(2, 1).ClearContents
Case Else
'the below replaces the Sheet_SelectionChange event (Now commented out)
.Range("O" & myCell.Row).Value = Range("O" & myCell.Row).Value + .Range("AA" & myCell.Row).Value

.Cells(1, 1).Activate 'activate range A1
End Select

End If

If WeekTrue = True Then
myCell.Value = pType
Select Case pType
Case Is = "SUA"

'set the pType
Union(.Cells(myCell.Row, 4), .Cells(myCell.Row, 6), .Cells(myCell.Row, 8), _
.Cells(myCell.Row, 10), .Cells(myCell.Row, 12)).Value = pType

'clear the time slots
Union(.Cells(myCell.Row, 3), .Cells(myCell.Row, 5), .Cells(myCell.Row, 7), _
.Cells(myCell.Row, 9), .Cells(myCell.Row, 11)).ClearContents

Range(Cells(myCell.Row + 1, 3), Cells(myCell.Row + 1, 12)).ClearContents

'Set range "O" and row to zero?
.Range("O" & myCell.Row).Value = 0

Case Else
Union(.Cells(myCell.Row, 4), .Cells(myCell.Row, 6), .Cells(myCell.Row, 8), _
.Cells(myCell.Row, 10), .Cells(myCell.Row, 12)).Value = pType
.Range("O" & myCell.Row).Value = Range("O" & myCell.Row).Value + .Range("AA" & myCell.Row).Value * 5
End Select
End If
[A1].Activate
End With
End Sub

Should put this one to bed...right Nick?

ndendrinos
01-08-2007, 06:21 PM
Yes we do Pete but not before I thank you one last time.
Someone took the liberty to give this posting "stars" not sure who but he beat me to it
and you deserve each and every one of them.
Until next time then.
Best regards,
Nick