I am trying to use the Pop-up calendar by Trevor Eyre to choose a date. I have a Sub PM_SCHEDULE_ENTER that I use to find the last row and then add the date to column 5 and two different combobox values to columns 1 & 2. Everything works except for having to choose and click the date on the calendar 3 times and then click "OK" on the message box twice. Not sure if a Function should be used to pass the last row to the sub for the calendar or if I am going about this all wrong. Any help would be appreciated. Thank you.
Code I am using:
Private Sub PM_SCHEDULE_ENTER()
'******************************************************************
'This routine adds the Asset ID and Task ID to the PM schedule.
'******************************************************************
Dim ws As Worksheet
Set ws = Sheet1
Dim LastRow As Long, i As Long
LastRow = Sheets("PM Schedule").Cells(Rows.Count, 1).End(xlUp).row
For i = LastRow To 1 Step -1
If Sheets("PM Schedule").Cells(i, 1) <> " " Then
LastRow = i + 1
Exit For
End If
Next
'****************************************************************
'This routine calls the Calendar Form to enter Assigned Date.
'****************************************************************
Dim dateVariable As Date
dateVariable = CalendarForm.GetDate
CalendarForm.GetDate
MyDate = CalendarForm.GetDate
If MyDate > 0 Then
Sheet1.Cells(LastRow, 5).value = MyDate
'Makes values have capital letters when added to the sheet.
ASSETS_ID_NUMBER_INPUT_BOX_2.value = UCase(ASSETS_ID_NUMBER_INPUT_BOX_2.value)
ASSETS_ALL_MAINT_TASKS_LIST.value = UCase(ASSETS_ALL_MAINT_TASKS_LIST.value)
'Puts values in first empty row.
Sheet1.Cells(LastRow, 1).value = ASSETS_ID_NUMBER_INPUT_BOX_2.value
Sheet1.Cells(LastRow, 2).value = ASSETS_ALL_MAINT_TASKS_LIST.value
End If
MsgBox "Task Added"
Call MOVE_FREQUENCY_TO_SCHEDULE
End Sub
Code I am using:
Private Sub PM_SCHEDULE_ENTER()
'******************************************************************
'This routine adds the Asset ID and Task ID to the PM schedule.
'******************************************************************
Dim ws As Worksheet
Set ws = Sheet1
Dim LastRow As Long, i As Long
LastRow = Sheets("PM Schedule").Cells(Rows.Count, 1).End(xlUp).row
For i = LastRow To 1 Step -1
If Sheets("PM Schedule").Cells(i, 1) <> " " Then
LastRow = i + 1
Exit For
End If
Next
'****************************************************************
'This routine calls the Calendar Form to enter Assigned Date.
'****************************************************************
Dim dateVariable As Date
dateVariable = CalendarForm.GetDate
CalendarForm.GetDate
MyDate = CalendarForm.GetDate
If MyDate > 0 Then
Sheet1.Cells(LastRow, 5).value = MyDate
'Makes values have capital letters when added to the sheet.
ASSETS_ID_NUMBER_INPUT_BOX_2.value = UCase(ASSETS_ID_NUMBER_INPUT_BOX_2.value)
ASSETS_ALL_MAINT_TASKS_LIST.value = UCase(ASSETS_ALL_MAINT_TASKS_LIST.value)
'Puts values in first empty row.
Sheet1.Cells(LastRow, 1).value = ASSETS_ID_NUMBER_INPUT_BOX_2.value
Sheet1.Cells(LastRow, 2).value = ASSETS_ALL_MAINT_TASKS_LIST.value
End If
MsgBox "Task Added"
Call MOVE_FREQUENCY_TO_SCHEDULE
End Sub