I am creating a calendar for scheduling, I am trying to have a warning message pop up if you try and schedule a day the employee has asked for off. The issue I was having was the data validation wasn't recognizing the macro so I decided to try and write it into the macro. I got it to work when looking at an absolute reference, now I'm having the worst time trying to figure out an index and match solution to match the date with the employee name. I have googled and watched videos and my brain hurts! The macro is in Module 4 attached to the first command button in the attached worksheet.
A couple of other mentions:
- The schedule will be entered into the Calendar tab.
- The days off will be entered into the Employees tab.
I'm still very new to macros, any help is greatly appreciated!
I am really struggling to see what the Macro is trying to do?
If i click the top button it checks E5 and then populates times in the activecell
Are you saying that instead of checking E5 you want to check the person and date of the active cell?
Somthing like this?
Dim DateX As Long
Dim EmName As String
EmName = Cells(ActiveCell.Row, 3)
DateX = Cells(10, ActiveCell.Column).Text
Worksheets("Employees").Cells(Worksheets("Employees").Range("a:a").Find(what:=EmName, LookIn:=xlValues, lookat:=xlWhole).Row, _
Worksheets("Employees").Range("4:4").Find(what:=DateX, LookIn:=xlValues, lookat:=xlPart).Column) = "X"
That's it! Thank you so much! You're my superstar!!
I am having more issues! I'm trying to google but I'm not finding the answer that will suffice. I am also trying to have the worksheet check the number of hours worked and alert the supervisor if an employee is being scheduled for more than 40 hours. The current code I have is this:
Dim Week1 As Long
Dim Week2 As Long
Dim Week3 As Long
Dim Week4 As Long
Dim Week5 As Long
Dim Week6 As Long
On Error Resume Next
Week1 = Cells(ActiveCell.Row, 38)
Week2 = Cells(ActiveCell.Row, 39)
Week3 = Cells(ActiveCell.Row, 42)
Week4 = Cells(ActiveCell.Row, 43)
Week5 = Cells(ActiveCell.Row, 44)
Week6 = Cells(ActiveCell.Row, 45)
If Week1 > 1.5 Then
MsgBox "Employee is scheduled for overtime, would you like to continue?", vbYesNo + vbExclamation + vbDefaultButton2
End If
End Sub
I'm having issues with how the code is looking at the information in the cell, if I use the decimal for the time everything comes up as >, if I use the actual ie ">40" nothing does. Attached is the current workbook. Module 4 is my "Test Module" to get my code working before I touch any of the actual working pieces of code in my workbook.
A Long is a Long Integer, so always a whole number. I suspect you want Double.
Unfortunately that doesn't seem to be the problem, or maybe at least not the only problem...?
I finally got it to work! Thank you so much! I had tweak some more, but I think the big issue was using the wrong declaration, thank you!!
