Log In
Register
Home
Trusted Members
February 13, 2021
Offline
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!
See Answer
Trusted Members
December 20, 2019
Offline
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"
Answers Post
Trusted Members
February 13, 2021
Offline
That's it! Thank you so much! You're my superstar!!
Trusted Members
February 13, 2021
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
A Long is a Long Integer, so always a whole number. I suspect you want Double.
1 Guest(s)
