I've got a scheduling workbook for my employees. It works good, and does exactly what I want, but I know there is a way to make the code more efficient, and I'm not exactly sure how to do it. I'll explain the workbook a little bit before I show the code. I have 12 sheets (1 for each month), that has each employees schedule. These sheets are locked and cannot be changed. I use formulas on these sheets that make these sheets show data that is entered to 2 hidden sheets (1 is "MASTERPTO" in the code) via a userform. So, if an employee requests PTO on Jan. 1, the userform put PTO on the hidden sheet, and the "Jan" worksheet shows that change. The way I have it set up, if that employee is on the "Jan" Worksheet and clicks on the cell associated with Jan. 1, the userform pops up with a textbox automatically populated with the date the employee clicked on.
Well, when the "Submit" button is clicked it puts the data entered in the userform on my hidden sheet. The hidden sheet has the dates Jan 1 - Dec 31 in Column "A", starting in row 2. I wanted it to find whatever date was in TextBox1 on that list and use that row to enter whatever data is entered, but it isn't finding the date. I think it has something to do with I used formulas to make the list rather than actual text. So A2 = 01/01/2017, A3 = A2 + 1, A4 = A3 + 1, etc... Here's my code:
rng1 is supposed to find whatever date is in TextBox1, but it's using that date and going to the row number of the number code of the date in TextBox1. So I had to subtract 42734 from rng1 to get it on the right row. Next year when I do the schedule for 2018, I'll have to subtract 43099 from rng 1. How do I get it to just find the row with the matching date in "A"?
Code:
Private Sub UserForm_Initialize()
TextBox1.Text = ActiveSheet.Cells.Range("A" & (ActiveCell.Row)).Value
End SubWell, when the "Submit" button is clicked it puts the data entered in the userform on my hidden sheet. The hidden sheet has the dates Jan 1 - Dec 31 in Column "A", starting in row 2. I wanted it to find whatever date was in TextBox1 on that list and use that row to enter whatever data is entered, but it isn't finding the date. I think it has something to do with I used formulas to make the list rather than actual text. So A2 = 01/01/2017, A3 = A2 + 1, A4 = A3 + 1, etc... Here's my code:
Code:
Private Sub SUBMIT_Click()
Dim Wb As Workbook
Dim ws As Worksheet
Dim rng1 As Range
Dim lastrow As Long
Set Wb = ThisWorkbook
Set ws = Wb.Sheets("MASTERPTO")
Set rng1 = ws.Range("A:A").Find(UserForm1.TextBox1.Value, ws.[a1], xlValues, xlWhole, xlByRows, xlNext)
If Not rng1 Is Nothing Then
If MsgBox("Once submitted, all data will be locked, and can only be changed by the supervisor." & vbCrLf & "Please make sure all info is correct" & vbCrLf & "Click Yes to confirm entry, or No to Cancel", vbYesNo) = vbNo Then Exit Sub
If ws.Cells(rng1 - 42734, CBREQUSTR.ListIndex + 2).Value = "" Then
ws.Cells(rng1 - 42734, CBREQUSTR.ListIndex + 2).Value = CBPTO.Text
ElseIf ws.Cells(rng1 - 42734, CBREQUSTR.ListIndex + 2).Value = "" = False Then
MsgBox ("That cell already has data entered.")
Exit Sub
End If
End If
ws.Cells(rng1 - 42734, CBREQUSTR.ListIndex + 2).Value = CBPTO.Text
If ws.Cells(rng1 - 42734, 15).Value = "" Then
ws.Cells(rng1 - 42734, 15).Value = CBREQUSTR.Text & " " & TextBox1.Text & " " & CBPTO.Text & " was requested on " & Now
ElseIf ws.Cells(rng1 - 42734, 15).Value = "" = False Then
ws.Cells(rng1 - 42734, 16).Value = CBREQUSTR.Text & " " & TextBox1.Text & " " & CBPTO.Text & " was requested on " & Now
End If
ActiveWorkbook.Save
Unload UserForm1
End Subrng1 is supposed to find whatever date is in TextBox1, but it's using that date and going to the row number of the number code of the date in TextBox1. So I had to subtract 42734 from rng1 to get it on the right row. Next year when I do the schedule for 2018, I'll have to subtract 43099 from rng 1. How do I get it to just find the row with the matching date in "A"?
