Printable View
That's okay OBP, my supervisor said he wanted to "play" with what I had at this stage and in one on my most discerning looks and said " one does not play with the cake while its still in the oven" and walked off.
That'll put him off for a couple of weeks.... but after that I'm a shot duck.
Is this a " correct" (permissible) section of code?
[vba]
Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("H3:M368")) Is Nothing Then Exit Sub
With target
vVal = Format(.value,"0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End With
End IF
If Intersect(Target, Range("C3:F368")) Is Nothing Then Exit Sub
With target
vVal = Format(.Vale, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm AM/PM"
End With
End If
Application.EnableEvents = True
End Sub
[/vba]
I have one range to be formatted as [h]:mm and another to be formatted as [h]:mm AM/PM. XLD suggested testing in the same Worksheet_Change event, so am I on the right track here?
Ted
No, you would never get to the second action
Code:Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H3:M368")) Is Nothing Then
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
ElseIf Not Intersect(Target, Range("C3:F368")) Is Nothing Then
With Target
vVal = Format(.Vale, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm AM/PM"
End If
End With
End If
Application.EnableEvents = True
End Sub
Thanks Bob. Its a jungle out there for us beginners
Getting a compile error "End With without with" at the first End With. It seems matched, so what causes this?
Quote:
Originally Posted by xld
Should the End With and End IF be reversed after the line .numberformat = "[h]:mm"
Sorry, I just coipied your code and that error was already in there.
[vba]
Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H3:M368")) Is Nothing Then
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
ElseIf Not Intersect(Target, Range("C3:F368")) Is Nothing Then
With Target
vVal = Format(.Vale, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm AM/PM"
End If
End With
End If
Application.EnableEvents = True
End Sub
[/vba]
Fixed the code, now it doesn't seem to matter what I do as it gives date and time values.
Anyone with a guess as to what is now going down the plug hole?
Ted
Code:Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H3:M368,T3:X368,AF3:AJ368")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = (.Value \ 100 + (.Value - (.Value \ 100) * 100) / 60) / 24
.NumberFormat = "[h]:mm"
End If
End With
ElseIf Not Intersect(Target, Range("C3:F368,O3:R368,AA3:AD368")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = (.Value \ 100 + (.Value - (.Value \ 100) * 100) / 60) / 24
.NumberFormat = "h:mm AM/PM"
End If
End With
End If
Application.EnableEvents = True
End Sub
So, no formatting of the cells is required as this code now completes this task for the data entered?
And.... am I still meant to be able to enter the values as "0000" from the numerical keyboard?
At the moment 0700 for 7am results in 16800:00
Hi Ted,
I had a little time to play with this, so here's a userform interface. I've changed time format to 24 hour as this makes life simpler for data entry, but with a bit more work it can be changed if the approach is of any use.
To use, either select a date and click the button. or enter a date on the form, then select the Worker from the dropdown. Additional names can be added with minimal changes to the code.
Regards
Malcolm
Thank you Malcolm. I shall have a play around tomorrow night after work. Right now its 20 to Midnight and I need to be up again at 4.
Ted
Just enter it as 700, 714, 729, etc.Quote:
Originally Posted by Aussiebear
I can't get 16800 as you do.
Yes I imagine its because I've had formatting in place on this sheet before your contribution of the code. Its possible that the code format and the sheet format are clashing.
Ted
MD, If an employee should leave during the year, and be replaced or a additional employee be added, do I make the necessary changes to the sheet, then go to Private Sub refresh() and add an addition Case "New Name" and WPos = 36?
Ted
MD, I imagine that the Supervisor will be sitting there with a time card in hand, entering data. Once he enters a days "values" and it writes to sheet, how does the form clear to allow the next day's values to be entered for the same employee?
On some days, they may actually do the same times ( and work functions) as the day before, is it possible just to change the date, tab through to Write to the sheet and press enter?
If I try to select the employee name by typing their name rather than selecting from the drop down list, it boots the form to a new position which requires the form to be dragged up the sheet for it to be filled in. Can this be fixed? Perhaps to show the form just below the last row of data for the employee selected, although this may not be good if there's a significant break between working days.
Something that has just occured to me is, we have a policy of allocating a min 8 hours for a sick day, or for a day which is part of approved holidays. I will need to show the hours in the employee time as 0700, 1230,1300,1530 and zero hours in the work functions. Will this be allowed in the form given that it will show as 8 hours unallocated?
If I create an error it boots me out of the workbook, can this be changed so that it simply clears the data from the form and gives an error message?
Otherwise its very impressive. Thank you
Ted
Hi Ted,
As usual, we can do most things with the form. It's a question of identifying your needs. It sounds like you need additional columns for your time allocation, or some other way to record sick time/holidays.
Regarding the form position. If you can set Top and Left values for the form which suit your screen, I can code these in. The form can move to the row relevant to the date/worker being processed, as soon as this data is entered.
Error handling can be added as required.
Regards
Malcolm
Malcolm, I notice that when you input the values in the work times section it is already written into the sheet, and by the time you get to the "Write to Sheet"button all values are already showing in their respective cells.
So what does the button actually do?
Can you change this so that the employee name remains the same but the date is advanced one day and the rest of the data is cleared to the default values.
Lets leave the issue of holiday/sickdays for now. Its still a cost incurred but since it cannot be allocated to a work function, then its an "extra"
Ted
Hi Ted,
The userform should be regarded as a trial version, as I didn't know if this was the way you wanted to go.
Not all entry changes will update the sheet and the button forces an update.
We can certainly add a next day button for the same worker.
Why not add an "Other/Extra" column(s) to your Crops which would allow full allocation of time. We could even attach a comment to this field (if used) I think you did this elsewhere.
To save modifying code, the ListtIndex of the combo could be used to set the necessary offsets, but it depends upon correct arrangement of Names and Spreadsheet. Would this be desireable or a problem?
Desireable... most certainly.