A Different Type of "Timesheet" Query

[golf4]

Hi, everyone -

I just had what-should-probably-be-considered an easier VB question. The problem I'm having is employees entering daily hours that exceed 8 hours (located in my last TOTALS column).

What I'm wondering is how to develop some code, in the TOTAL column cells, for each day, to alert the user that the hours entered for the day exceed 8 hours and provides them an opportunity to review/revise the hours if needed. For example, if cell AU7 exceeds 8 hours, the msgbox will warn the user, ask if they used any OT and to review hours. I can figure the code out for one cell (probably), but I'm having a problem extending the code to the entire range of cells, i.e. AU7..AU200. Any help in this one would be greatly appreciate.

Thanks,

Golf

[Jacob Hilderbrand]

You could use Conditional Formatting to highlight the cell or Validation with an error alert warning message.

[Zack Barresse]

Hi Frank,

If you're wanting VBA for this, you could use the Intersect function...

If Not Intersect(Target, Range("AU7:AU200")) Is Nothing Then
    'Do something here
End If

[golf4]

Thanks, guys -

Really appreciate the help. I'll try em both........

Take care,
Frank

[golf4]

Hi, Zack & Jacob -

I'm attempting to develop some code to try and get accomplished what we've talked about, but it doesn't seem to work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("y5:y200")) > 8 And [N2] = "N" Then
MsgBox ("TEST")
End If
End Sub

The error msg I'm getting is " Run-time error "91": Object variable or With block variable not set".

Any ideas to what I'm doing wrong?

Thanks for the help.

Frank

[Jacob Hilderbrand]

Nothing cannot be greater than 8. If the Intersect is Nothing then you have a problem.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("y5:y200")) Is Nothing Then Exit Sub
    If Intersect(Target, Range("y5:y200")) > 8 And [N2] = "N" Then
        MsgBox ("TEST")
    End If
End Sub

[golf4]

Hi, Jake -

Thanks for the code. I just tried, and the thing still doesn't work. I'm wondering whether I can shoot you a copy to take a look at? Too much to ask? I would post a zip version here, but I'm thinking the file is too big. What do you think?

Thanks,

Frank

[Jacob Hilderbrand]

Go ahead and send it to me. Please zip it for me.

[Jacob Hilderbrand]

Got the file. I see what the problem was. Try this macro.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target.EntireRow, Range("y5:y200")) Is Nothing Then Exit Sub
    If Intersect(Target.EntireRow, Range("y5:y200")) > 8 And [N2] = "N" Then
        MsgBox ("Noooo OT...")
    End If
End Sub[

[golf4]

Hi, Jacob -

Your suggestion, AGAIN, works like a charm!!! Thanks so much for the help.

Take care,
Frank

[Jacob Hilderbrand]

You're Welcome

Take Care

[golf4]

Hi, everyone -

I'm not sure whether this will reopen this thread or not, but I'mm going to give it a shot.

We have several "types" of employees, i.e. maintenance, remote site personnel, main office personnel, etc. The way I have our Timesheet data entry page set up is columns for each program and site where the employee enters their hours each day and the tool breaks the hours down between the sites and programs by specific percentages. The thing is that, for example, our maintenance people only deal with certain programs (column cells) and not others. To make the data entry sheet more usable for these specific types of employees, I would like to hide the columns that are not applicable to their positions.

The first thing I have the employee(s) do is to choose their name from a drop-down list from a VLookup table. In a cell directly adjacent to the employee name is another code ("M"aintenance, "WS" for remote sites, "E"xempt for managers and "N"on-exempt for all others). These codes also come from the same Vlookup table. I've developed a code that I thought would do the job, but I think it's going to require a Worksheet_Change macro rather than the one I've developed. I've included my code below:

[VBA]Private Sub hidecolumns()
If [M2] = "M" Then
Columns("P:S").Select
Selection.EntireColumn.Hidden = True
Columns("Z").Select
Selection.EntireColumn.Hidden = True
Else
If [M2] = "WS" Then
Columns("P:W").Select
Selection.EntireColumn.Hidden = True
Else
If [M2] = "E" Then
Columns("B:AX").Select
Selection.EntireColumn.Hidden = False
Else
If [M2] = "N" Then
Columns("B:AX").Select
Selection.EntireColumn.Hidden = False
Columns("Z").Select
Selection.EntireColumn.Hidden = True
End If
End If
End If
End If
Range("C7").Select
End Sub[/VBA]

When I run the macro within the VB editor, using F8, it works fine. But when I go to the data entry sheet and select a specific employee, nothing happens. That's why I'm thinking it should be developed through Worksheet_Change.

I'm hoping one of the experts here can help me modify my code so that it works via selecting an employee.

Thanks again for all the help.

Frank

[Jacob Hilderbrand]

If you want the code to run when you select an employee then you need to use an event. If you are just selecting a cell then use the Selection Change event. If you are changing a cell's value use the Change event.

[golf4]

Thanks, Jacob -

The way I have everyone selecting their name is from a drop-down list from a VLookup table. I gave the Worksheet_Change option a try, but still isn't working:

[VBA] Private Sub Worksheet_Change(ByVal Target As Range)
If (Range("M2")) Is Nothing Then Exit Sub
If (Range("M2")) = "M" Then
Columns("P:S").Select
Selection.EntireColumn.Hidden = True
Columns("Z").Select
Selection.EntireColumn.Hidden = True
Else
If (Range("M2")) = "WS" Then
Columns("P:W").Select
Selection.EntireColumn.Hidden = True
Else
If (Range("M2")) = "E" Then
Columns("B:AX").Select
Selection.EntireColumn.Hidden = False
Else
If (Range("M2")) = "N" Then
Columns("B:AX").Select
Selection.EntireColumn.Hidden = False
Columns("Z").Select
Selection.EntireColumn.Hidden = True
End If
End If
End If
End If
Range("C7").Select
End Sub[/VBA]

Not sure what I'm doing wrong.......

[Jacob Hilderbrand]

Try this:
[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("M2").Text = vbNullString Then
Exit Sub
End If

Select Case Range("M2").Text
Case Is = "M"
Columns("P:S").Hidden = True
Columns("Z").Hidden = True
Case Is = "WS"
Columns("P:W").Hidden = True
Case Is = "E"
Columns("B:AX").Hidden = False
Case Is = "N"
Columns("B:AX").Hidden = False
Columns("Z").Hidden = True
End Select

End Sub
[/vba]

[golf4]

Hi, Jacob -

Thanks again for the code suggestion. This one doesn't seem to work in the data entry sheet either, but does in VB Editor. UUUGGGG!!!!! Just a tad frustrating, this one!!!

Thanks for the help.

Frank

[Jacob Hilderbrand]

Go ahead and send me the workbook if you want and I will take a closer look.

[golf4]

Hi, Jacob -

Thanks for all the help on this one. I'm still at work (dedicated public servant that I am), but I'll shoot you a copy as soon as I get home. Hope that's ok.

Thanks again,

Frank

[Jacob Hilderbrand]

I just sent the file back. Seems to be working now. Let me know if there are any other problems.

[golf4]

Just got it, Jacob -

Thanks so much for the help. I feel so bad about yanking on so much of your time on this thing!!!

I do have one more question if you have a sec. The combobox drop-down name list, prior to the new one, was tied to the Vlookup table on sheet DATA. From the combobox choice of names would relate to the Vlooup cell C3 on the Payroll Sheet which would then produce the specific program breakdown % of hours at the bottom of the Payroll Sheet.

Is there a way that I can now tie the new drop-down list of employee names to the Vlookup table so that the %'s will be accurate?

Thanks again for the help,

Frank