UserForm won't open because ListBox has only one line of text?

J

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
482
Office Version
  1. 365
Platform
  1. Windows
This is odd and I don't know where to begin troubleshooting. My Userform wouldn't open and after doing some debugging I discovered that the problem was that a ListBox was unable to load data from a named range IF the named range was less than one row. Weird, huh?

Here's my Userform showing the relevant ListBox...

listbox1.png


Here's the part of the code under it that loads it:

VBA Code: 
    'Loads Floor Plan Request
    Set wsFloorDesigners = ThisWorkbook.Worksheets("FloorDesigners")
    DesignerNames.List = [FloorDesigners!DesignerNames].Value
    FloorPlanRequestReturn.List = [FloorPlanRequests!FloorPlanRequests].Value
    CCNames.List = [Users!UserNames].Value
    BCCNames.List = [Users!UserNames].Value

The error happens when it tries to load this:

VBA Code: 
FloorPlanRequestReturn.List = [FloorPlanRequests!FloorPlanRequests].Value

Here's the wacky part. When FloorPlanRequests only has one line like this it breaks...

listbox3.png


However, when FloorPlanRequests has two lines (or more) of data it loads fine...

listbox2.png


Here's all the code under the UserForm...

VBA Code: 
Dim i As Long
Dim wsDataCenter As Worksheet
Dim wsModels As Worksheet
Dim wsFieldManagers As Worksheet
Dim wsGarageHandling As Worksheet
Dim wsFloorsSelection As Worksheet
Dim wsFloorDesigners As Worksheet
Dim wsFloorPlanRequests As Worksheet
Dim wsUsers As Worksheet
Dim wsHolidays As Worksheet
Dim wsBoardwalksSelection As Worksheet

Private Sub ADD_EEH_Click()
ANH_NH.SetFocus
End Sub

Private Sub BCC_Requests_Select_Click()
Me.MultiPage1.Value = 11
NewUsersName.SetFocus
End Sub

Private Sub BCCNames_Change()
    SearchUserName2
    CheckUserNames2
    End Sub

Private Sub CC_Requests_Select_Click()
Me.MultiPage1.Value = 11
NewUsersName.SetFocus
End Sub


Private Sub CCNames_Change()
    SearchUserName
    CheckUserNames
    CheckCCNames
End Sub

Private Sub Label111_Click()
Me.MultiPage1.Value = 11
NewUsersName.SetFocus
End Sub

Private Sub Label112_Click()
EditBCC.Visible = True
LabelEditBCC.Visible = True
BCCNames.Visible = False
LabelBCCNames.Visible = False
End Sub

Private Sub cmdResetHoliday_Click()
        EEH_SH.Visible = True
        EEH_SH_label.Visible = True
        With Me
            .EEH_SH.Value = Null
            .EEH_SH_Return.Value = ""
        End With
        UserForm_Initialize
        EEH_SH.SetFocus
        Me.cmdDeleteHoliday.Enabled = False
End Sub

Private Sub CNCL_EEH_Click()
    EEH_SH_label.Visible = True
    EEH_SH.Visible = True
    EEH_EH_label.Visible = False
    EEH_SH_Return.Visible = False
End Sub

Private Sub EDIT_EEH_Click()
     EEH_SH_Return.Visible = True
     EEH_EH_label.Visible = True
     EEH_SH_label.Visible = False
     EEH_SH.Visible = False
     CNCL_EEH.Visible = True
     EDIT_EEH.Visible = False
End Sub

Private Sub FloorsSelection_Change()
If Me.FloorsSelection.Value = "<ADD NEW>" Then
    Me.MultiPage1.Value = 8
    NewFloorsSelection.SetFocus
End If
End Sub

Private Sub FloorsSelectionReturn_Change()
If Me.FloorsSelectionReturn.Value = "<ADD NEW>" Then
    Me.MultiPage1.Value = 8
    NewFloorsSelection.SetFocus
End If
End Sub

Private Sub HolidaySelect_Change()
LabelOptional.Visible = False
End Sub

Private Sub Label117_Click()
Me.MultiPage1.Value = 12
ANH_NH.SetFocus
End Sub

Private Sub NewSubFloorsSelection_Change()
If Me.NewSubFloorsSelection.Value = "<ADD NEW>" Then
    Me.MultiPage1.Value = 8
    NewFloorsSelection.SetFocus
End If
End Sub

Private Sub NewSubInitials_Change()
Call CheckNewSubs2
End Sub

Private Sub ResetPlanRequestUpdate_Click()
Call ResetFloorPlanRequestUpdate
BCCNames.Visible = True
LabelBCC.Visible = True
End Sub
Private Sub EDIT_EEFD_Name_Click()
EditDesignersEmail.Visible = False
LabelEditDesignersEmail.Visible = False
LabelEditDesignersName.Visible = False
EditDesignersName.Visible = False
EDIT_EEFD_Name.Visible = False
CNCL_EEFD_Name.Visible = True
EditEmail.Visible = True
LabelEditEmail.Visible = True
FloorsDesignersReturn.Visible = True
LabelFloorsDesignersReturn.Visible = True
EditEmail.Text = EditDesignersEmail.Value
FloorsDesignersReturn.SetFocus
End Sub

Private Sub EDIT_EEFM_Name_Click()
EDIT_EEFM_Name.Visible = False
CNCL_EEFM_Name.Visible = True
LabelFMName.Visible = False
EditFMName.Visible = False
LabelEditFMName.Visible = True
EditFMNameReturn.Visible = True
EditFMNameReturn.SetFocus
End Sub

Private Sub EDIT_EEFS_SF_Click()
     FloorsSelectionReturn.Visible = True
     LabelFloorsSelectionReturn.Visible = True
     FloorsSelectionEdit.Visible = False
     LabelFloorsSelectionEdit.Visible = False
     CNCL_EEFS_SF.Visible = True
     EDIT_EEFS_SF.Visible = False
End Sub

Private Sub EDIT_EEGH_GH_Click()
     EditGarageHandlingReturn.Visible = True
     LabelEditGH.Visible = True
     LabelEditGarageHandling.Visible = False
     EditGarageHandling.Visible = False
     CNCL_EEGH_GH.Visible = True
     EDIT_EEGH_GH.Visible = False
End Sub

Private Sub EDIT_EEM_Model_Click()
    EditModelName.Visible = True
    LabelEditModelName.Visible = True
    ModelName.Visible = False
    LabelModelName.Visible = False
    CNCL_EEM_Model.Visible = True
    EditModelName.SetFocus
End Sub

Private Sub EDIT_EEUN_Click()
LabelEditUsersEmailReturn.Visible = True
EditUsersEmailReturn.Visible = True
LabelEditName.Visible = True
EditName.Visible = True
EditUsersEmail.Visible = False
LabelEditUsersEmail.Visible = False
EditUsersName.Visible = False
LabelEditUsersName.Visible = False
EDIT_EEUN.Visible = False
CNCL_EEUN.Visible = True
EditUsersEmailReturn.Text = EditUsersEmail.Value
End Sub

Private Sub CNCL_EEFD_Name_Click()
LabelEditDesignersName.Visible = True
EditDesignersName.Visible = True
EDIT_EEFD_Name.Visible = True
CNCL_EEFD_Name.Visible = False
FloorsDesignersReturn.Visible = False
LabelFloorsDesignersReturn.Visible = False
EditDesignersEmail.Visible = False
EditEmail.Visible = False
LabelEditEmail.Visible = False
LabelEditDesignersEmail.Visible = True
EditDesignersEmail.Visible = True
EditDesignersName.SetFocus
End Sub

Private Sub CNCL_EEFM_Name_Click()
CNCL_EEFM_Name.Visible = False
EDIT_EEFM_Name.Visible = True
LabelEditFMName.Visible = False
EditFMNameReturn.Visible = False
LabelFMName.Visible = True
EditFMName.Visible = True
EditFMName.SetFocus
End Sub

Private Sub CNCL_EEFS_SF_Click()
    'FloorsSelectionEdit.Visible = False
    'LabelFloorsSelectionEdit.Visible = False
    FloorsSelectionEdit.Visible = True
    LabelFloorsSelectionEdit.Visible = True
    FloorsSelectionReturn.Visible = False
    LabelFloorsSelectionReturn.Visible = False
End Sub

Private Sub CNCL_EEGH_GH_Click()
     EditGarageHandlingReturn.Visible = False
     LabelEditGH.Visible = False
     LabelEditGarageHandling.Visible = True
     EditGarageHandling.Visible = True
     CNCL_EEGH_GH.Visible = False
     EDIT_EEGH_GH.Visible = True
End Sub

Private Sub CNCL_EEM_Model_Click()
    EditModelName.Visible = False
    LabelEditModelName.Visible = False
    ModelName.Visible = True
    LabelModelName.Visible = True
    EDIT_EEM_Model.Visible = True
    CNCL_EEM_Model.Visible = False
    CheckFloorsSelection
End Sub

Private Sub CNCL_EEUN_Click()
EditUsersEmailReturn.Visible = False
LabelEditUsersEmailReturn.Visible = False
LabelEditUsersEmail.Visible = True
EditUsersEmail.Visible = True
LabelEditName.Visible = False
EditName.Visible = False
EditUsersName.Visible = True
LabelEditUsersName.Visible = True
EDIT_EEUN.Visible = True
CNCL_EEUN.Visible = False
EditUsersName.SetFocus
End Sub

Private Sub UserForm_Initialize()

    CheckNewJobs
    CheckNewLots
    CheckSpacerDate
    CheckWeekendDate
    CheckNewSubs
    CheckNewSubs2
    CheckNewModels
    CheckFM
    CheckGH
    CheckFloorsSelection
    CheckEditDesigner
    CheckNewDesigner
    CheckPlanRequests
    CheckCCNames
    CheckUserNames
    CheckUserNames2
    CheckEditUser
    CheckEEH
    'FloorPlanRequests

    'Loads Add Job
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    JobSubName.List = [DataCenter!DataCenterSubNames].Value
    JobModelNumber.List = [Models!Models].Value
    JobGarageHandling.List = [GarageHandling!GarageHandling].Value
    
    'Loads Add Lot
    LotSubName.List = [DataCenter!DataCenterSubNames].Value
    
    'Loads Spacer
    Set wsHolidays = ThisWorkbook.Worksheets("Holidays")
    HolidaySelect.List = [Holidays!HolidaysAll].Value
    LabelOptional.Visible = True
    
    'Loads Edit Sub
    Set wsDataCenter = ThisWorkbook.Worksheets("DataCenter")
    SubName.List = [DataCenter!DataCenterSubNames].Value
    FieldManager.List = [FieldManagers!FMNames].Value
    FloorsSelection.List = [FloorsSelection!FloorsSelection].Value
    EES_BW_Select.List = [BoardwalksSelection!BoardwalksSelection].Value
    ANS_BW_Select.List = [BoardwalksSelection!BoardwalksSelection].Value
    EditSubName.Visible = False
    LabelEditSubName.Visible = False
    SubName.Visible = True
    LabelSubName.Visible = True
    CNCL_EES_Sub.Visible = False
    EDIT_EES_Sub.Visible = False
    'EDIT_EES_Floors.Visible = False
    EDIT_EEFS_SF.Visible = False
    CNCL_EEFS_SF.Visible = False
       
    'Loads Add Sub
    FMSelect.List = [FieldManagers!FMNames].Value
    NewSubFloorsSelection.List = [FloorsSelection!FloorsSelection].Value
    
    'Loads Edit Model
    Set wsModels = ThisWorkbook.Worksheets("Models")
    ModelName.List = [Models!ModelwUnits].Value
    EditModelName.Visible = False
    LabelEditModelName.Visible = False
    CNCL_EEM_Model.Visible = False
    
    'Loads FM Edit
    Set wsFieldManagers = ThisWorkbook.Worksheets("FieldManagers")
    EditFMName.List = [FieldManagers!FMNames].Value
    'EditFMName.Visible = False
    LabelEditFMName.Visible = False
    EditFMNameReturn.Visible = False
    EDIT_EEFM_Name.Visible = False
    CNCL_EEFM_Name.Visible = False
    'LabelFMName.Visible = True
    'EditFMName.Visible = True
    
    'Loads Garage Handling Edit
    Set wsGarageHandling = ThisWorkbook.Worksheets("GarageHandling")
    EditGarageHandling.List = [GarageHandling!GarageHandling].Value
    EditGarageHandlingReturn.Visible = False
    LabelEditGH.Visible = False
    EDIT_EEGH_GH.Visible = False
    CNCL_EEGH_GH.Visible = False
    LabelEditGH.Visible = False
    EditGarageHandlingReturn.Visible = False
    
    'Loads Floors Selection Edit
    Set wsFloorsSelection = ThisWorkbook.Worksheets("FloorsSelection")
    FloorsSelectionEdit.List = [FloorsSelection!FloorsSelection].Value
    FloorsSelectionReturn.Visible = False
    LabelFloorsSelectionReturn.Visible = False
    EDIT_EEM_Model.Visible = False
    
    'Loads Floor Designers Edit
    Set wsFloorDesigners = ThisWorkbook.Worksheets("FloorDesigners")
    EditDesignersName.List = [FloorDesigners!DesignerNames].Value
    FloorsDesignersReturn.Visible = False
    LabelFloorsDesignersReturn.Visible = False
    LabelEditEmail.Visible = False
    EditEmail.Visible = False
    EDIT_EEFD_Name.Visible = False
    CNCL_EEFD_Name.Visible = False
    
    'Loads Floor Plan Request
    Set wsFloorDesigners = ThisWorkbook.Worksheets("FloorDesigners")
    DesignerNames.List = [FloorDesigners!DesignerNames].Value
    FloorPlanRequestReturn.List = [FloorPlanRequests!FloorPlanRequests].Value
    CCNames.List = [Users!UserNames].Value
    BCCNames.List = [Users!UserNames].Value
        
    'Loads Add User
    Set wsUsers = ThisWorkbook.Worksheets("Users")
    EditUsersName.List = [Users!UserNames].Value
    LabelEditName.Visible = False
    EditName.Visible = False
    EDIT_EEUN.Visible = False
    CNCL_EEUN.Visible = False
    EditUsersEmailReturn.Visible = False
    LabelEditUsersEmailReturn.Visible = False
    
    'Loads Calendar
    Set wsHolidays = ThisWorkbook.Worksheets("Holidays")
    EEH_SH.List = [Holidays!HolidaysAll].Value
    EEH_SH_Return.Visible = False
    EEH_EH_label.Visible = False
    EDIT_EEH.Visible = False
    CNCL_EEH.Visible = False
       
End Sub
Private Sub ADD_ANJ_GH_Click()
Me.MultiPage1.Value = 7
NewGarageHandlingName.SetFocus
End Sub
Private Sub ADD_ANJ_Model_Click()
Me.MultiPage1.Value = 5
NewModelCode.SetFocus
End Sub
Private Sub ADD_ANJ_Sub_Click()
Me.MultiPage1.Value = 4
NewSubCode.SetFocus
End Sub
Private Sub ADD_ANL_Sub_Click()
Me.MultiPage1.Value = 4
NewSubCode.SetFocus
End Sub
Private Sub ADD_ANM_Model_Click()
Me.MultiPage1.Value = 4
NewSubCode.SetFocus
End Sub
Private Sub ADD_ANS_Floors_Click()
Me.MultiPage1.Value = 8
NewFloorsSelection.SetFocus
End Sub
Private Sub ADD_ANS_FM_Click()
Me.MultiPage1.Value = 6
NewFMName.SetFocus
End Sub
Private Sub ADD_EEFD_Name_Click()
NewFloorDesignersName.SetFocus
End Sub
Private Sub ADD_EEFM_Name_Click()
NewFMName.SetFocus
End Sub
Private Sub ADD_EEFS_SF_Click()
NewFloorsSelection.SetFocus
End Sub
Private Sub ADD_EEGH_GH_Click()
NewGarageHandlingName.SetFocus
End Sub
Private Sub ADD_EEM_Model_Click()
NewModelCode.SetFocus
End Sub
Private Sub ADD_EES_Floors_Click()
Me.MultiPage1.Value = 8
NewFloorsSelection.SetFocus
End Sub
Private Sub ADD_EES_FM_Click()
Me.MultiPage1.Value = 6
NewFMName.SetFocus
End Sub
Private Sub ADD_EES_Sub_Click()
NewSubCode.SetFocus
End Sub
Private Sub ADD_Requests_Select_Click()
Me.MultiPage1.Value = 9
NewFloorDesignersName.SetFocus
End Sub
Private Sub ADD_EEUN_Click()
NewUsersName.SetFocus
End Sub
Private Sub ADD_UserNames_Click()
Me.MultiPage1.Value = 11
NewUsersName.SetFocus
End Sub
Private Sub CNCL_EES_Sub_Click()
    EditSubName.Visible = False
    LabelEditSubName.Visible = False
    SubName.Visible = True
    LabelSubName.Visible = True
    EDIT_EES_Sub.Visible = True
    CNCL_EES_Sub.Visible = False
End Sub

'Job Tab
Private Sub JobShippingDate_Change()
    CheckNewJobs
End Sub
Private Sub JobSubCode_Change()
    CheckNewJobs
    End Sub
Private Sub JobSubCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.JobSubCode.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchJobSubCode_Click
             JobLotNumber.SetFocus
        End If
End Sub
Private Sub JobSubName_Change()
     Call SearchJobSubName_Click
End Sub
Private Sub JobLotNumber_Change()
    CheckNewJobs
End Sub
Private Sub CheckNewJobs()
    If Len(JobShippingDate.Value) > 0 And Len(JobSubCode.Value) > 0 And Len(JobLotNumber.Value) > 0 Then
        cmdAddNewJob.Enabled = True
        cmdAddNewJobSlab.Enabled = True
        cmdAddNewJobOpenWeb.Enabled = True
    Else
        cmdAddNewJob.Enabled = False
        cmdAddNewJobSlab.Enabled = False
        cmdAddNewJobOpenWeb.Enabled = False
    End If
End Sub
Private Sub SearchJobSubCode_Click()
    Dim LastRow     As Long
 
    JobSubCode = Trim(JobSubCode.Text)
    If Len(JobSubCode.Text) = 0 Then Exit Sub
 
    With wsDataCenter
        LastRow = .Cells(Rows.Count, 2).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 3).Value = JobSubCode Then
                JobSubCode.Text = .Cells(i, 2).Value
                JobSubName.Text = .Cells(i, 5).Value
            Exit For
        End If
    Next
 End With
End Sub
Private Sub JobSubName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.JobSubName.Value) = "" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchJobSubCode_Click
             SearchJobSubCode_Click
             JobSubName.SetFocus
        End If
End Sub
Private Sub JobSubName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub SearchJobSubName_Click()
Dim JobSubName_id As String
JobSubName = Trim(JobSubName.Text)
LastRow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("DataCenter").Cells(i, 5).Value = JobSubName Then
JobSubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
End If
Next
End Sub
Private Sub JobModelNumber_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub JobGarageHandling_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub cmdResetNewJob_Click()
        With Me
            .JobShippingDate.Value = ""
            .JobSubCode.Value = ""
            .JobSubName.Value = Null
            .JobLotNumber.Value = ""
            .JobModelNumber.Value = Null
            .JobElevation.Value = ""
            .JobGarageHandling.Value = Null
        End With
        JobShippingDate.SetFocus
        UserForm_Initialize
End Sub
Private Sub cmdAddNewJob_Click()
    Call SubmitJob
    Call cmdResetNewJob_Click
    Call ReplyNewJob
End Sub
Private Sub cmdAddNewJobSlab_Click()
    Call SubmitJobSlab
    Call cmdResetNewJob_Click
    Call ReplyNewJob
End Sub
Private Sub cmdAddNewJobOpenWeb_Click()
    Call SubmitJobOpenWeb
    Call cmdResetNewJob_Click
    Call ReplyNewJob
End Sub
Private Sub cmdCloseAddNewJob_Click()
  Unload Me
End Sub
Private Sub ReplyNewJob()
    UserForm_Initialize
    MsgBox ("New Job Successfully Added!")
End Sub
Private Sub JobGarageHandling_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.JobShippingDate.Value) = "*" Then
        ShippingDate.SetFocus
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SubmitJob
        End If
        If KeyCode = 13 Then
             ResetJob
        End If
End Sub

'Lot Tab
Private Sub LotSubCode_Change()
        CheckNewLots
End Sub
Private Sub LotLotNumber_Change()
    CheckNewLots
End Sub
Private Sub CheckNewLots()
    If Len(LotSubCode.Value) > 0 And Len(LotLotNumber.Value) > 0 Then
        cmdAddNewLot.Enabled = True
        cmdAddNewLotSlab.Enabled = True
        cmdAddNewLotOpenWeb.Enabled = True
        cmdAddNewFileSet.Enabled = True
        cmdAddNewFileSetSlab.Enabled = True
        cmdAddNewFileSetOpenWeb.Enabled = True
    Else
        cmdAddNewLot.Enabled = False
        cmdAddNewLotSlab.Enabled = False
        cmdAddNewLotOpenWeb.Enabled = False
        cmdAddNewFileSet.Enabled = False
        cmdAddNewFileSetSlab.Enabled = False
        cmdAddNewFileSetOpenWeb.Enabled = False
    End If
End Sub
Private Sub cmdAddNewLot_Click()
    Call AddNewLot
    Call cmdResetAddNewLot_Click
    Call ReplyNewLot
End Sub
Private Sub cmdAddNewLotSlab_Click()
    Call AddNewLotSlab
    Call cmdResetAddNewLot_Click
    Call ReplyNewLot
End Sub
Private Sub cmdAddNewLotOpenWeb_Click()
    Call AddNewLotOpenWeb
    Call cmdResetAddNewLot_Click
    Call ReplyNewLot
End Sub
Private Sub cmdCloseNewLot_Click()
  Unload Me
End Sub
Private Sub cmdResetNewLot_Click()
        With Me
            .LotSubCode.Value = ""
            .LotSubName.Value = Null
            .EditFMEmail.Value = ""
            .LotLotNumber.Value = ""
        End With
        LotSubCode.SetFocus
        Me.cmdDeleteModel.Enabled = False
        UserForm_Initialize
End Sub
Private Sub cmdResetAddNewLot_Click()
        With Me
            .LotSubCode.Value = ""
            .LotSubName.Value = Null
            .EditFMEmail.Value = ""
            .LotLotNumber.Value = ""
        End With
        LotSubCode.SetFocus
        Me.cmdDeleteModel.Enabled = False
        UserForm_Initialize
End Sub
Private Sub ReplyNewLot()
    UserForm_Initialize
    MsgBox ("New Lot Successfully Added!")
End Sub
Private Sub SearchLotSubName_Click()
Dim LotSubName_id As String
LotSubName = Trim(LotSubName.Text)
LastRow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("DataCenter").Cells(i, 5).Value = LotSubName Then
LotSubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
End If
Next
End Sub
Private Sub SearchLotSubCode_Click()
    Dim LastRow     As Long
 
    LotSubCode = Trim(LotSubCode.Text)
    If Len(LotSubCode.Text) = 0 Then Exit Sub
 
    With wsDataCenter
        LastRow = .Cells(Rows.Count, 2).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 3).Value = LotSubCode Then
                LotSubCode.Text = .Cells(i, 2).Value
                LotSubName.Text = .Cells(i, 5).Value
            Exit For
        End If
    Next
 End With
End Sub
Private Sub LotSubName_Change()
     Call SearchLotSubName_Click
End Sub
Private Sub LotSubName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub LotSubName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.LotSubName.Value) = "" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchLotSubCode_Click
        End If
End Sub
Private Sub LotSubCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.LotSubCode.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchLotSubCode_Click
             LotLotNumber.SetFocus
        End If
End Sub
Private Sub cmdAddNewFileSet_Click()
 Call AddNewFileSet
 Call cmdResetAddNewLot_Click
 LotSubCode.SetFocus
End Sub
Private Sub cmdAddNewFileSetOpenWeb_Click()
Call AddNewFileSetOpenWeb
Call cmdResetAddNewLot_Click
LotSubCode.SetFocus
End Sub
Private Sub cmdAddNewFileSetSlab_Click()
Call AddNewFileSetSlab
Call cmdResetAddNewLot_Click
LotSubCode.SetFocus
End Sub
Private Sub TargetDate_Change()
    CheckSpacerDate
End Sub
Private Sub CheckSpacerDate()
    If Len(TargetDate.Value) > 0 Then
        cmdAddSpacer.Enabled = True
    Else
        cmdAddSpacer.Enabled = False
    End If
End Sub
Private Sub TargetDateWeekend_Change()
    CheckWeekendDate
End Sub
Private Sub CheckWeekendDate()
    If Len(TargetDateWeekend.Value) > 0 Then
        cmdAddWeekend.Enabled = True
    Else
        cmdAddWeekend.Enabled = False
    End If
End Sub

'Spacer Tab
Private Sub TargetDate_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.TargetDate.Value) = "*" Then
        TargetDate.SetFocus
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             AddSpacer
        End If
        If KeyCode = 13 Then
             ResetSpacer
        End If
End Sub
Private Sub cmdAddSpacer_Click()
    Call AddSpacer
    Call ResetSpacer
    TargetDate.SetFocus
End Sub
Private Sub CloseSpacer_Click()
  Unload Me
End Sub
Private Sub cmdResetSpacer_Click()
    Call ResetSpacer
    LabelOptional.Visible = True
    TargetDate.SetFocus
End Sub

'Weekend Tab
Private Sub TargetDateWeekend_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.TargetDateWeekend.Value) = "*" Then
        TargetDateWeekend.SetFocus
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             AddWeekend
        End If
        If KeyCode = 13 Then
             ResetWeekend
        End If
End Sub
Private Sub cmdAddWeekend_Click()
    Call AddWeekend
    Call ResetWeekend
    TargetDateWeekend.SetFocus
End Sub
Private Sub CloseWeekend_Click()
  Unload Me
End Sub
Private Sub cmdResetWeekend_Click()
    Call ResetWeekend
    TargetDateWeekend.SetFocus
End Sub

'Subdivision Tab
Private Sub CheckNewSubs()
    If Len(SubCode.Value) > 0 And Len(SubName.Value) > 0 And Len(SubInitials.Value) > 0 Then
        cmdUpdateSub.Enabled = True
        cmdDeleteSub.Enabled = True
    Else
        cmdUpdateSub.Enabled = False
        cmdDeleteSub.Enabled = False
    End If
End Sub
Private Sub CheckNewSubs2()
    If Len(NewSubCode.Value) > 0 And Len(NewSubName.Value) > 0 And Len(NewSubInitials.Value) > 0 Then
        cmdAddNewSub.Enabled = True
        cmdResetNewSub.Enabled = True
    Else
        cmdAddNewSub.Enabled = False
        cmdResetNewSub.Enabled = False
    End If
End Sub
Private Sub cmdSearchSubCode_Click()
Dim SubCode_id As String
SubCode = Trim(SubCode.Text)
LastRow = Worksheets("DataCenter").Cells(Rows.Count, 3).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("DataCenter").Cells(i, 3).Value = SubCode Then
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
EditSubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FloorsSelection.Text = Worksheets("DataCenter").Cells(i, 13).Value
EES_BW_Select.Text = Worksheets("DataCenter").Cells(i, 14).Value
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
End If
Next
End Sub
Private Sub cmdSearchSubName_Click()
Dim SubName_id As String
SubName = Trim(SubName.Text)
LastRow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("DataCenter").Cells(i, 5).Value = SubName Then
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
EditSubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
SubInitials.Text = Worksheets("DataCenter").Cells(i, 4).Value
FloorsSelection.Text = Worksheets("DataCenter").Cells(i, 13).Value
EES_BW_Select.Text = Worksheets("DataCenter").Cells(i, 14).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
Me.cmdDeleteSub.Enabled = True
End If
Next
End Sub
Private Sub cmdSearchSubInitials_Click()
Dim SubCode_id As String
SubInitials = Trim(SubInitials.Text)
LastRow = Worksheets("DataCenter").Cells(Rows.Count, 4).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("DataCenter").Cells(i, 4).Value = SubInitials Then
SubName.Text = Worksheets("DataCenter").Cells(i, 5).Value
SubCode.Text = Worksheets("DataCenter").Cells(i, 2).Value
FieldManager.Text = Worksheets("DataCenter").Cells(i, 7).Value
FloorsSelection.Text = Worksheets("DataCenter").Cells(i, 13).Value
EES_BW_Select.Text = Worksheets("DataCenter").Cells(i, 14).Value
End If
Next
End Sub
Private Sub cmdUpdateSub_Click()
Dim SubName_id As String
SubName = Trim(SubName.Text)
LastRow = Worksheets("DataCenter").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("DataCenter").Cells(i, 5).Value = SubName Then
   Worksheets("DataCenter").Cells(i, 5).Value = EditSubName.Text
   Worksheets("DataCenter").Cells(i, 2).Value = SubCode.Text
   Worksheets("DataCenter").Cells(i, 4).Value = SubInitials.Text
   Worksheets("DataCenter").Cells(i, 13).Value = FloorsSelection.Text
   Worksheets("DataCenter").Cells(i, 14).Value = EES_BW_Select.Text
   Worksheets("DataCenter").Cells(i, 7).Value = FieldManager.Text
End If
Next

        With Me
            .SubName.Value = Null
            .EditSubName = ""
            .SubCode.Value = ""
            .SubInitials.Value = ""
            .FloorsSelection.Value = Null
            .FieldManager.Value = Null
        End With
        
        UserForm_Initialize
        
        Call cmdResetSub_Click
        
        MsgBox ("Subdivision Successfully Updated!")

End Sub
Private Sub EDIT_EES_Sub_Click()
    EditSubName.Visible = True
    LabelEditSubName.Visible = True
    SubName.Visible = False
    LabelSubName.Visible = False
    EDIT_EES_Sub.Visible = False
    CNCL_EES_Sub.Visible = True
    EditSubName.SetFocus
End Sub
Private Sub cmdAddNewSub_Click()
    Call AddNewSub
    Call ResetNewSub
    UserForm_Initialize
    NewSubCode.SetFocus
End Sub
Private Sub cmdResetSub_Click()
 
        With Me
            .SubCode.Value = ""
            .SubName.Value = Null
            .SubInitials.Value = ""
            .EditSubName.Value = ""
            .FloorsSelection.Value = Null
            .EES_BW_Select.Value = Null
            .FieldManager.Value = Null
            
        End With
        UserForm_Initialize
        SubCode.SetFocus

End Sub
Private Sub cmdResetNewSub_Click()
        With Me
            .NewSubCode.Value = ""
            .NewSubName.Value = ""
            .NewSubInitials.Value = ""
            .NewSubFloorsSelection.Value = Null
            .EES_BW_Select.Value = Null
            .FMSelect.Value = Null
        End With
        UserForm_Initialize
        NewSubCode.SetFocus
End Sub
Private Sub cmdDeleteSub_Click()
    Call DeleteSub
    Call ResetSub
    Call ReplyDeleteSub
    UserForm_Initialize
End Sub
Private Sub ResetSub()
        With Me
            .SubCode.Value = ""
            .SubName.Value = Null
            .SubInitials.Value = ""
            .FloorsSelection.Value = Null
            .EES_BW_Select.Value = Null
            .FieldManager.Value = Null
        End With
        UserForm_Initialize
        SubCode.SetFocus
        Me.cmdDeleteModel.Enabled = False
End Sub
Private Sub ReplyDeleteSub()
    Dim r As Range, X, a
    X = Me.SubName.Value
    UserForm_Initialize
    MsgBox (X & " Successfully Deleted!")
End Sub
Private Sub DeleteSub()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.SubName.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & "?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log SubCode
    Sheets("MasterLog").Range("B10") = Me.SubCode.Value
        
    'Log Subdivision
    Sheets("MasterLog").Range("C10") = Me.SubName.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "Subdivision Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
    
    With Sheets("DataCenter")
    .Columns(5).Find(What:=X, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.SubName.List = a
    End If
           
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
Private Sub SubInitials_Change()
    SubInitials.Text = UCase(SubInitials.Text)
End Sub
Private Sub SubInitials_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubInitials.Value) = "*" Then
                Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubInitials_Click
             cmdSearchSubInitials_Click
        End If
        SubInitials.SetFocus
End Sub
Private Sub SubName_Change()
     Call cmdSearchSubName_Click
     Call cmdSearchSubName_Click
     EDIT_EES_Sub.Visible = True
     CheckNewSubs
End Sub
Private Sub SubName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub SubName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubName.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubCode_Click
        End If
End Sub
Private Sub SubCode_Change()
    SubCode.Text = UCase(SubCode.Text)
    CheckNewSubs
End Sub
Private Sub SubCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SubCode.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             cmdSearchSubCode_Click
        End If
End Sub
Private Sub CloseNewSub_Click()
  Unload Me
End Sub
Private Sub cmdCloseSubL_Click()
Unload Me
End Sub
Private Sub cmdCloseSubR_Click()
Unload Me
End Sub
Private Sub cmdCloseSubMaintenance_Click()
Unload Me
End Sub
Private Sub RST_EES_Floors_Click()
    FloorsSelection.Visible = True
    LabelFloorsSelection.Visible = True
    EditFloorsSelection.Visible = False
    LabelEditFloorsSelection.Visible = False
    EDIT_EES_Floors.Visible = True
    RST_EES_Floors.Visible = False
End Sub

'Models Tab
Private Sub CloseNewModel_Click()
  Unload Me
End Sub
Private Sub cmdAddNewModel_Click()
    Call AddNewModel
    Call ResetNewModel
    NewModelName.SetFocus
End Sub
Private Sub cmdResetNewModel_Click()
    Call ResetNewModel
    NewModelCode.SetFocus
End Sub
Private Sub ModelCode_Change()
     'Call SearchModelCode_Click
End Sub
Private Sub ModelCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.ModelCode.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchModelCode_Click
        End If
End Sub
Private Sub SearchModelCode_Click()
If Len(ModelCode.Text) = 0 Then Exit Sub
Dim theModelCode As Variant
theModelCode = Trim(ModelCode.Text)
If IsNumeric(theModelCode) Then theModelCode = CLng(theModelCode)
    With wsModels
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = theModelCode Then
                ModelCode.Text = .Cells(i, 1).Value
                ModelUnits.Text = .Cells(i, 3).Value
                ModelName.Text = .Cells(i, 5).Value
            Exit For
        End If
    Next
 End With
End Sub
Private Sub ModelName_Change()
     Call SearchModelName_Click
     Call SearchModelName_Click
     EditModelName.Value = ModelName.Value
     EDIT_EEM_Model.Visible = True
End Sub
Private Sub SearchModelName_Click()
Dim ModelName_id As String
ModelName = Trim(ModelName.Text)
LastRow = Worksheets("Models").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("Models").Cells(i, 5).Value = ModelName Then
ModelName.Text = Worksheets("Models").Cells(i, 5).Value
ModelCode.Text = Worksheets("Models").Cells(i, 1).Value
ModelUnits.Text = Worksheets("Models").Cells(i, 3).Value
Me.cmdDeleteModel.Enabled = True
Me.cmdUpdateModel.Enabled = True
End If
Next
End Sub
Private Sub ModelName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.ModelName.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchModelName_Click
        End If
End Sub
Private Sub cmdUpdateModel_Click()
Dim ModelName_id As String
LastRow = Worksheets("Models").Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("Models").Cells(i, 5).Value = ModelName Then
   Worksheets("Models").Cells(i, 5).Value = EditModelName.Text
   Worksheets("Models").Cells(i, 1).Value = ModelCode.Text
   Worksheets("Models").Cells(i, 3).Value = ModelUnits.Text
End If
Next
        
        Call cmdResetUpdateModel_Click
        
        MsgBox ("Model Successfully Updated!")

End Sub

Private Sub cmdResetUpdateModel_Click()
        With Me
        ModelName.Visible = True
        LabelModelName.Visible = True
            .ModelName.Value = Null
            .EditModelName.Value = ""
            .ModelUnits.Value = ""
            .ModelCode.Value = ""
            UserForm_Initialize
        End With
        
                Me.cmdDeleteModel.Enabled = False
End Sub
Private Sub cmdResetModel_Click()
        With Me
            .ModelCode.Value = ""
            .ModelName.Value = Null
            .ModelUnits.Value = ""
        End With
        Me.cmdDeleteModel.Enabled = False
        LabelModelName.Visible = True
        ModelName.Visible = True
        EDIT_EEM_Model.Visible = False
        UserForm_Initialize
        ModelCode.SetFocus
End Sub
Private Sub cmdResetNewModel2_Click()
        With Me
            .ModelCode.Value = ""
            .ModelName.Value = Null
            .ModelUnits.Value = ""
        End With
        Me.cmdDeleteModel2.Enabled = False
        UserForm_Initialize
End Sub
Private Sub cmdDeleteModel_Click()
    Call DeleteModel
    Call ResetDeleteModel
    Call ReplyDeleteModel
    UserForm_Initialize
End Sub
Private Sub DeleteModel()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.ModelName.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & "?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log ModelCode
    Sheets("MasterLog").Range("B10") = Me.ModelCode.Value
        
    'Log Model Name
    Sheets("MasterLog").Range("C10") = Me.ModelName.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "Model Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
           
    With Sheets("Models")
    .Columns(5).Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.ModelName.List = a
    End If
    
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
Private Sub ResetDeleteModel()
        With Me
            .ModelCode.Value = ""
            .ModelName.Value = Null
            .ModelUnits.Value = ""
        End With
        UserForm_Initialize
        ModelCode.SetFocus
        Me.cmdDeleteModel.Enabled = False
End Sub
Private Sub ReplyDeleteModel()
    Dim r As Range, X, a
    X = Me.ModelName.Value
    UserForm_Initialize
    MsgBox (X & " Successfully Deleted!")
End Sub
Private Sub ModelName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub CheckNewModels()
    If Len(ModelCode.Value) > 0 Then
        cmdUpdateModel.Enabled = True
        cmdDeleteModel.Enabled = True
    Else
        cmdUpdateModel.Enabled = False
        cmdDeleteModel.Enabled = False
    End If
End Sub
Private Sub cmdCloseModelL_Click()
Unload Me
End Sub
Private Sub cmdCloseModelR_Click()
Unload Me
End Sub

'Field Managers Tab
Private Sub CheckFM()
    If Len(EditFMName.Value) > 0 Then
        cmdUpdateFM.Enabled = True
        cmdDeleteFM.Enabled = True
    Else
        cmdUpdateFM.Enabled = False
        cmdDeleteFM.Enabled = False
    End If
End Sub
Private Sub CloseNewFMName_Click()
  Unload Me
End Sub
Private Sub cmdAddNewFM_Click()
    Call AddNewFM
    Call cmdResetNewFM_Click
    Call ReplyNewFM
    UserForm_Initialize
End Sub
Private Sub cmdDeleteFM_Click()
    Call DeleteFM
    Call ResetEditNewFM
    Call ReplyDeleteFM
    UserForm_Initialize
End Sub
Private Sub cmdResetEditNewFM_Click()
        With Me
        EditFMName.Visible = True
        LabelFMName.Visible = True
            .EditFMName.Value = Null
            .EditFMEmail.Value = ""
            .EditFMPhone.Value = ""
            UserForm_Initialize
        End With
        
                Me.cmdDeleteModel.Enabled = False
End Sub
Private Sub ReplyDeleteNewFM()
    UserForm_Initialize
    MsgBox ("Field Manager Successfully Deleted!")
End Sub
Private Sub SearchFMName_Click()
Dim EditFMName_id As String
EditFMName = Trim(EditFMName.Text)
LastRow = Worksheets("FieldManagers").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("FieldManagers").Cells(i, 1).Value = EditFMName Then
EditFMName.Text = Worksheets("FieldManagers").Cells(i, 1).Value
EditFMEmail.Text = Worksheets("FieldManagers").Cells(i, 2).Value
EditFMPhone.Text = Worksheets("FieldManagers").Cells(i, 3).Value
End If
Next
End Sub
Private Sub EditFMName_Change()
     Call SearchFMName_Click
     Call SearchFMName_Click
     EditFMNameReturn.Value = EditFMName.Value
     EDIT_EEFM_Name.Visible = True
     CheckFM
End Sub
Private Sub EditFMName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.EditFMName.Value) = "*" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchFMName_Click
             SearchFMName_Click
        End If
End Sub
Private Sub ResetEditNewFM()
        With Me
            .EditFMName.Value = Null
            .EditFMEmail.Value = ""
            .EditFMPhone.Value = ""
        End With
        UserForm_Initialize
        EditFMName.SetFocus
        Me.cmdDeleteModel.Enabled = False
End Sub
Private Sub cmdResetNewFM_Click()
        With Me
        EditFMName.Visible = True
        LabelEditFMName.Visible = True
            .NewFMName.Value = ""
            .NewFMEmail.Value = ""
            .NewFMPhone.Value = ""
        End With
        UserForm_Initialize
        NewFMName.SetFocus
        Me.cmdDeleteModel.Enabled = False
End Sub
Private Sub ReplyNewFM()
    UserForm_Initialize
    MsgBox ("New Field Manager Successfully Added!")
End Sub
Private Sub ReplyDeleteFM()
    UserForm_Initialize
    MsgBox ("Field Manager Successfully Deleted!")
End Sub
Private Sub DeleteFM()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.EditFMName.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & "?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log Field Manager Name
    Sheets("MasterLog").Range("C10") = Me.EditFMName.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "Field Manager Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
    
    With Sheets("FieldManagers")
    .Columns(1).Find(What:=X, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.EditFMName.List = a
    End If
    
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
Private Sub FieldManager_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub FMSelect_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub EditFMName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub cmdCloseFML_Click()
Unload Me
End Sub
Private Sub cmdCloseFMR_Click()
Unload Me
End Sub
Private Sub cmdUpdateFM_Click()
Dim EditFMName_id As String
LastRow = Worksheets("FieldManagers").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("FieldManagers").Cells(i, 1).Value = EditFMName Then
   Worksheets("FieldManagers").Cells(i, 1).Value = EditFMNameReturn.Text
   Worksheets("FieldManagers").Cells(i, 2).Value = EditFMEmail.Text
   Worksheets("FieldManagers").Cells(i, 3).Value = EditFMPhone.Text
End If
Next
        
        Call cmdResetEditNewFM_Click
        
        MsgBox ("Field Manager Successfully Updated!")

End Sub

'Garage Handling Tab
Private Sub CheckGH()
    If Len(EditGarageHandling.Value) > 0 Then
        cmdUpdateGarageHandling.Enabled = True
        cmdDeleteGarageHandling.Enabled = True
    Else
        cmdUpdateGarageHandling.Enabled = False
        cmdDeleteGarageHandling.Enabled = False
    End If
End Sub
Private Sub EditGarageHandling_Change()
     EditGarageHandlingReturn.Value = EditGarageHandling.Value
     EDIT_EEGH_GH.Visible = True
     CheckGH
End Sub
Private Sub cmdDeleteGarageHandling_Click()
    Call DeleteGarageHandling
    Call cmdResetDeleteGarageHandling_Click
    Call ReplyDeleteGarageHandling
    UserForm_Initialize
End Sub
Private Sub cmdResetDeleteGarageHandling_Click()
        With Me
            .EditGarageHandling.Value = Null
            .EditGarageHandlingReturn.Value = ""
            EditGarageHandling.Visible = True
            LabelEditGarageHandling.Visible = True
        End With
        UserForm_Initialize
        EditGarageHandling.SetFocus
        Me.cmdDeleteModel.Enabled = False
End Sub
Private Sub ReplyDeleteGarageHandling()
    Dim r As Range, X, a
    X = Me.EditGarageHandling.Value
    UserForm_Initialize
    MsgBox (X & " Successfully Deleted!")
End Sub
Private Sub cmdAddNewGarageHandling_Click()
    Call AddNewGarageHandling
    Call ResetNewGarageHandling
    UserForm_Initialize
    NewGarageHandlingName.SetFocus
End Sub
Private Sub cmdResetNewGarageHandling_Click()
    Call ResetNewGarageHandling
    NewGarageHandlingName.SetFocus
End Sub
Private Sub EditGarageHandling_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub cmdCloseGHL_Click()
Unload Me
End Sub
Private Sub cmdCloseGHR_Click()
Unload Me
End Sub
Private Sub cmdUpdateGarageHandling_Click()
Dim EditGarageHandling_id As String
'EditGarageHandling = Trim(EditGarageHandling.Text)
LastRow = Worksheets("GarageHandling").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("GarageHandling").Cells(i, 1).Value = EditGarageHandling Then
   Worksheets("GarageHandling").Cells(i, 1).Value = EditGarageHandlingReturn.Text
End If
Next
        
        Call cmdResetDeleteGarageHandling_Click
        
        MsgBox ("Garage Handling Successfully Updated!")

End Sub
Private Sub DeleteGarageHandling()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.EditGarageHandling.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & "?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log Garage Handling Name
    Sheets("MasterLog").Range("C10") = Me.EditGarageHandling.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "Garage Handling Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
           
    With Sheets("GarageHandling")
    .Columns(1).Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.EditGarageHandling.List = a
    End If
    
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

'Floors Selection Tab
Private Sub CheckFloorsSelection()
    If Len(FloorsSelectionEdit.Value) > 0 Then
        UpdateFloorsSelection.Enabled = True
        cmdDeleteFloorsSelection.Enabled = True
    Else
        UpdateFloorsSelection.Enabled = False
        cmdDeleteFloorsSelection.Enabled = False
    End If
End Sub
Private Sub FloorsSelectionEdit_Change()
     EDIT_EEFS_SF.Visible = True
     FloorsSelection.Visible = False
     LabelFloorsSelection.Visible = False
     FloorsSelectionReturn.Value = FloorsSelectionEdit.Value
     CheckFloorsSelection
End Sub
Private Sub cmdDeleteFloorsSelection_Click()
    Call DeleteFloorsSelection
    Call ResetFloorsSelection_Click
    UserForm_Initialize
    Call ReplyDeleteFloorsSelection
    
End Sub
Private Sub ResetFloorsSelection_Click()
        FloorsSelectionEdit.Visible = True
        LabelFloorsSelectionEdit.Visible = True
        With Me
            .FloorsSelectionEdit.Value = Null
            .FloorsSelectionReturn.Value = ""
        End With
        UserForm_Initialize
        FloorsSelectionEdit.SetFocus
        Me.cmdDeleteFloorsSelection.Enabled = False
End Sub
Private Sub ReplyDeleteFloorsSelection()
    Dim r As Range, X, a
    X = Me.FloorsSelectionEdit.Value
    UserForm_Initialize
    MsgBox (X & " Successfully Deleted!")
End Sub
Private Sub SaveNewFloorsSelection_Click()
    Call AddNewFloorsSelection
    Call ResetNewFloorsSelection
    UserForm_Initialize
    NewFloorsSelection.SetFocus
End Sub
Private Sub cmdResetNewFloorsSelection_Click()
    Call ResetNewFloorsSelection
    NewFloorsSelection.SetFocus
End Sub
Private Sub ResetNewFloorsSelection()
        With Me
            .NewFloorsSelection.Value = ""
        End With
        UserForm_Initialize
        NewFloorsSelection.SetFocus
End Sub
Private Sub FloorsSelectionEdit_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub CloseFloorsSelectionL_Click()
Unload Me
End Sub
Private Sub CloseFloorsSelectionR_Click()
Unload Me
End Sub
Private Sub UpdateFloorsSelection_Click()
Dim UpdateFloorsSelection_id As String
LastRow = Worksheets("FloorsSelection").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("FloorsSelection").Cells(i, 1).Value = FloorsSelectionEdit Then
   Worksheets("FloorsSelection").Cells(i, 1).Value = FloorsSelectionReturn.Text
End If
Next
        
        Call ResetFloorsSelection_Click
        
        MsgBox ("Floors Selection Successfully Updated!")

End Sub
Private Sub DeleteFloorsSelection()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.FloorsSelectionEdit.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & " as a Floors Selection?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log Floor Selection
    Sheets("MasterLog").Range("C10") = Me.FloorsSelectionEdit.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "Floors Selection Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
           
    With Sheets("FloorsSelection")
    .Columns(1).Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.FloorsSelectionEdit.List = a
    End If
    
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

'Plan Requests Tab
Private Sub PlanRequestsClose_Click()
Unload Me
End Sub
Private Sub SearchUsersNameRequest_Click()
Dim UserNames_id As String
UserNames = Trim(UserNames.Text)
LastRow = Worksheets("Users").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("Users").Cells(i, 1).Value = UserNames Then
UserNames.Text = Worksheets("Users").Cells(i, 1).Value
UserNameEmailReturn.Text = Worksheets("Users").Cells(i, 2).Value
End If
Next
End Sub
Private Sub CheckPlanRequests()
    If Len(DesignerNames.Value) > 0 Then
        SendPlanRequestUpdate.Enabled = True
        ResetPlanRequestUpdate.Enabled = True
    Else
        SendPlanRequestUpdate.Enabled = False
        ResetPlanRequestUpdate.Enabled = False
    End If
End Sub
Private Sub CheckBCCNames()
    If Len(BCCNames.Value) > 0 Then
        ResetPlanRequestUpdate.Enabled = True
    Else
        ResetPlanRequestUpdate.Enabled = False
    End If
End Sub
Private Sub CheckCCNames()
    If Len(CCNames.Value) > 0 Then
        ResetPlanRequestUpdate.Enabled = True
    Else
        ResetPlanRequestUpdate.Enabled = False
    End If
End Sub

'Floor Designer Tab
Private Sub SaveNewFloorDesigner_Click()
    Call AddNewFloorDesigner
    Call ResetNewFloorDesigner_Click
    Call ReplyNewFloorDesigner
    UserForm_Initialize
End Sub
Private Sub CloseEditDesigner_Click()
Unload Me
End Sub
Private Sub CloseNewDesigner_Click()
Unload Me
End Sub
Private Sub CheckEditDesigner()
    If Len(EditDesignersName.Value) > 0 Then
        UpdateEditDesigner.Enabled = True
        DeleteEditDesigner.Enabled = True
    Else
        UpdateEditDesigner.Enabled = False
        DeleteEditDesigner.Enabled = False
    End If
End Sub
Private Sub EditDesignersName_Change()
        SearchEditDesignersName_Click
        FloorsDesignersReturn.Value = EditDesignersName.Value
        EDIT_EEFD_Name.Visible = True
        CheckEditDesigner
End Sub
Private Sub SearchEditDesignersName_Click()
    Dim LastRow     As Long
 
    EditDesignersName = Trim(EditDesignersName.Text)
    If Len(EditDesignersName.Text) = 0 Then Exit Sub
 
    With wsFloorDesigners
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = EditDesignersName Then
                EditDesignersName.Text = .Cells(i, 1).Value
                EditDesignersEmail.Text = .Cells(i, 2).Value
            Exit For
        End If
    Next
 End With
End Sub
Private Sub SearchEditDesignersName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If (Me.SearchEditDesignersName.Value) = "" Then
        Exit Sub
        Else
        End If
        If KeyCode = 13 Then
             SearchEditDesignersName_Click
             SearchEditDesignersName_Click
             EditDesignersName.SetFocus
        End If
End Sub
Private Sub ResetEditDesigner_Click()
        With Me
            .EditDesignersName.Value = Null
            .FloorsDesignersReturn.Value = ""
            .EditDesignersEmail.Value = ""
            EditDesignersName.Visible = True
            LabelEditDesignersName.Visible = True
            LabelEditDesignersEmail.Visible = True
            EditDesignersEmail.Visible = True
            FloorsDesignersReturn.Visible = False
            LabelFloorsDesignersReturn.Visible = False
        End With
        UserForm_Initialize
        EditDesignersName.SetFocus
End Sub
Private Sub ResetNewFloorDesigner_Click()
        With Me
            .NewFloorDesignersName.Value = ""
            .NewFloorDesignersEmail.Value = ""
        End With
        UserForm_Initialize
        NewFloorDesignersName.SetFocus
End Sub
Private Sub CheckNewDesigner()
    If Len(NewFloorDesignersName.Value) > 0 And Len(NewFloorDesignersEmail.Value) > 0 Then
        SaveNewFloorDesigner.Enabled = True
    Else
        SaveNewFloorDesigner.Enabled = False
    End If
End Sub
Private Sub NewFloorDesignersName_Change()
    CheckNewDesigner
End Sub
Private Sub NewFloorDesignersEmail_Change()
    CheckNewDesigner
End Sub
Private Sub ReplyNewFloorDesigner()
    UserForm_Initialize
    MsgBox ("New Floor Designer Successfully Added!")
End Sub
Private Sub DesignerNames_Change()
    SearchDesignerNames
    CheckPlanRequests
End Sub
Private Sub DeleteEditDesigner_Click()
    Call DeleteDesigner
    Call ResetDeleteDesigner_Click
    Call ReplyDeleteDesigner
    UserForm_Initialize
End Sub
Private Sub ResetDeleteDesigner_Click()
        With Me
            .EditDesignersName.Value = Null
            .EditDesignersEmail.Value = ""
        End With
        UserForm_Initialize
        EditDesignersName.SetFocus
End Sub
Private Sub ReplyDeleteDesigner()
    UserForm_Initialize
    MsgBox ("Floor Designer Successfully Deleted!")
End Sub
Private Sub DeleteDesigner()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.EditDesignersName.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & "?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log Field Manager Name
    Sheets("MasterLog").Range("C10") = Me.EditDesignersName.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "Floor Designer Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
    
    With Sheets("FloorDesigners")
    .Columns(1).Find(What:=X, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.EditDesignersName.List = a
    End If
    
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
Private Sub SendPlanRequestUpdate_Click()
    SendPlanRequestUpdateEmail
    Call ResetFloorPlanRequestUpdate
    Call ReplyFloorPlanRequestUpdate
    UserForm_Initialize
End Sub
Private Sub ResetFloorPlanRequestUpdate()
        With Me
            .DesignerNames.Value = Null
            .CCNames.Value = Null
            .BCCNames.Value = Null
            FloorPlanRequestMessage.Value = Null
            CCEmailReturn.Value = Null
            BCCEmailReturn.Value = Null
            DesignerEmailReturn.Value = Null
            DesignerNames.Visible = True
            LabelDesignerNames.Visible = True
            CCNames.Visible = True
            LabelCCNames.Visible = True
        End With
        UserForm_Initialize
        DesignerNames.SetFocus
End Sub
Private Sub ReplyFloorPlanRequestUpdate()
    UserForm_Initialize
    MsgBox ("Floor Plan Request Update Email Successfully Sent!")
End Sub
Private Sub SearchDesignerNames()
    Dim LastRow     As Long
 
    DesignerNames = Trim(DesignerNames.Text)
    If Len(DesignerNames.Text) = 0 Then Exit Sub
 
    With wsFloorDesigners
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = DesignerNames Then
                DesignerNames.Text = .Cells(i, 1).Value
                DesignerEmailReturn.Text = .Cells(i, 2).Value
            Exit For
        End If
    Next
 End With
End Sub
Private Sub UpdateEditDesigner_Click()
Dim EditDesignersName_id As String
LastRow = Worksheets("FloorDesigners").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("FloorDesigners").Cells(i, 1).Value = EditDesignersName Then
   Worksheets("FloorDesigners").Cells(i, 1).Value = FloorsDesignersReturn.Text
   Worksheets("FloorDesigners").Cells(i, 2).Value = EditEmail.Text
End If
Next
        
        Call ResetEditDesigner_Click
        
        MsgBox ("Floor Designer Successfully Updated!")

End Sub

'Users Tab
Private Sub cmdResetNewUser_Click()
        With Me
            .NewUsersName.Value = ""
            .NewUsersEmail.Value = ""
        End With
        Sheets("UsersGrid").Visible = False
        Sheets("Calendar").Select
        UserForm_Initialize
        NewUsersName.SetFocus
End Sub
Private Sub ReplyNewUser()
    UserForm_Initialize
    MsgBox ("New User Successfully Added!")
End Sub
Private Sub ReplyDeleteUser()
    UserForm_Initialize
    MsgBox ("User Successfully Deleted!")
End Sub
Private Sub SearchUsersName_Click()
Dim EditUsersName_id As String
EditUsersName = Trim(EditUsersName.Text)
LastRow = Worksheets("Users").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("Users").Cells(i, 1).Value = EditUsersName Then
EditUsersName.Text = Worksheets("Users").Cells(i, 1).Value
EditUsersEmail.Text = Worksheets("Users").Cells(i, 2).Value
End If
Next
End Sub
Private Sub EditUsersName_Change()
SearchUsersName_Click
EditName.Value = EditUsersName.Value
EDIT_EEUN.Visible = True
CheckEditUser
End Sub
Private Sub DeleteUser()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.EditUsersName.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & "?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log User Name
    Sheets("MasterLog").Range("C10") = Me.EditUsersName.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "User Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
    
    With Sheets("Users")
    .Columns(1).Find(What:=X, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.EditUsersName.List = a
    End If
    
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
Private Sub cmdCloseUNL_Click()
Unload Me
End Sub
Private Sub cmdCloseUNR_Click()
Unload Me
End Sub
Private Sub cmdResetNewUN_Click()
        With Me
            .NewUsersName.Value = ""
            .NewUsersEmail.Value = ""
        End With
        UserForm_Initialize
        NewUsersName.SetFocus
End Sub
Private Sub cmdResetUN_Click()
        With Me
            EditUsersName.Visible = True
            LabelEditUsersName.Visible = True
            .EditUsersName.Value = Null
            .EditUsersEmail.Value = ""
            EditUsersEmail.Visible = True
            LabelEditUsersEmail.Visible = True
        End With
        UserForm_Initialize
        EditUsersName.SetFocus
End Sub
Private Sub cmsSaveNewUN_Click()
    Call AddNewUser
    Call cmdResetNewUser_Click
    Call ReplyNewUser
    UserForm_Initialize
End Sub
Private Sub CheckEditUser()
    If Len(EditUsersName.Value) > 0 Then
        cmdUpdateUN.Enabled = True
        cmdDeleteUN.Enabled = True
    Else
        cmdUpdateUN.Enabled = False
        cmdDeleteUN.Enabled = Fale
    End If
End Sub
Private Sub ResetEditUser_Click()
        With Me
        LabelEditUsersName.Visible = True
        EditUsersName.Visible = True
        LabelEditName.Visible = False
        EditName.Visible = False
            .EditUsersName.Value = Null
            .EditUsersEmail.Value = ""
        End With
        UserForm_Initialize
        EditUsersName.SetFocus
End Sub
Private Sub cmdDeleteUN_Click()
    Call DeleteUser
    Call ResetDeleteUser_Click
    Call ReplyDeleteUser
    UserForm_Initialize
End Sub
Private Sub ResetDeleteUser_Click()
        With Me
            .EditUsersName.Value = Null
            .EditName.Value = ""
            .EditUsersEmail.Value = ""
        End With
        UserForm_Initialize
        EditUsersName.SetFocus
End Sub
Private Sub cmdUpdateUN_Click()
Dim EditUsersName_id As String
LastRow = Worksheets("Users").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("Users").Cells(i, 1).Value = EditUsersName Then
   Worksheets("Users").Cells(i, 1).Value = EditName.Text
   Worksheets("Users").Cells(i, 2).Value = EditUsersEmailReturn.Text
End If
Next
        
        Call ResetEditUser_Click
        
        MsgBox ("User Successfully Updated!")

End Sub
Private Sub UserNames_Change()
    SearchUsersNameRequest_Click
    'CheckPlanRequests
End Sub
Private Sub SearchUserName()
    Dim LastRow     As Long
 
    CCNames = Trim(CCNames.Text)
    If Len(CCNames.Text) = 0 Then Exit Sub
 
    With wsUsers
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = CCNames Then
                CCNames.Text = .Cells(i, 1).Value
                CCEmailReturn.Text = .Cells(i, 2).Value
            Exit For
        End If
    Next
 End With
End Sub
Private Sub SearchUserName2()
    Dim LastRow     As Long
 
    BCCNames = Trim(BCCNames.Text)
    If Len(BCCNames.Text) = 0 Then Exit Sub
 
    With wsUsers
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To LastRow
            If .Cells(i, 1).Value = BCCNames Then
                BCCNames.Text = .Cells(i, 1).Value
                BCCEmailReturn.Text = .Cells(i, 2).Value
            Exit For
        End If
    Next
 End With
End Sub
Private Sub CheckUserNames()
    If Len(CCNames.Value) > 0 Then
        SendPlanRequestUpdate.Enabled = True
        ResetPlanRequestUpdate.Enabled = True
    Else
        SendPlanRequestUpdate.Enabled = False
        ResetPlanRequestUpdate.Enabled = False
    End If
End Sub
Private Sub CheckUserNames2()
    If Len(BCCNames.Value) > 0 Then
        SendPlanRequestUpdate.Enabled = True
        ResetPlanRequestUpdate.Enabled = True
    Else
        SendPlanRequestUpdate.Enabled = False
        ResetPlanRequestUpdate.Enabled = False
    End If
End Sub

'Calendar Tab
Private Sub CheckEEH()
    If Len(EEH_SH.Value) > 0 Then
        cmdUpdateHoliday.Enabled = True
        cmdDeleteHoliday.Enabled = True
    Else
        cmdUpdateHoliday.Enabled = False
        cmdDeleteHoliday.Enabled = False
    End If
End Sub
Private Sub EEH_SH_Change()
     EEH_SH_Return.Value = EEH_SH.Value
     EDIT_EEH.Visible = True
     CheckEEH
End Sub
Private Sub cmdDeleteHoliday_Click()
    Call DeleteHoliday
    Call cmdResetDeleteHoliday_Click
    Call ReplyDeleteHoliday
    UserForm_Initialize
End Sub
Private Sub cmdResetDeleteHoliday_Click()
        With Me
            .EEH_SH.Value = Null
            .EEH_SH_Return.Value = ""
            EEH_SH.Visible = True
            EEH_SH_label.Visible = True
        End With
        UserForm_Initialize
        EEH_SH.SetFocus
        Me.cmdDeleteHoliday.Enabled = False
End Sub
Private Sub ReplyDeleteHoliday()
    Dim r As Range, X, a
    X = Me.EEH_SH.Value
    UserForm_Initialize
    MsgBox (X & " Successfully Deleted!")
End Sub
Private Sub cmdSaveHoliday_Click()
    Call AddNewHoliday
    Call ResetNewHoliday
    UserForm_Initialize
    ANH_NH.SetFocus
End Sub
Private Sub cmdResetAddHoliday_Click()
    Call ResetNewHoliday
    ANH_NH.SetFocus
End Sub
Private Sub EEH_SH_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub
Private Sub CloseCalendarL_Click()
Unload Me
End Sub
Private Sub CloseCalendarR_Click()
Unload Me
End Sub
Private Sub cmdUpdateHoliday_Click()
Dim EEH_SH_id As String
'EEH_SH = Trim(EEH_SH.Text)
LastRow = Worksheets("Holidays").Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("Holidays").Cells(i, 2).Value = EEH_SH Then
   Worksheets("Holidays").Cells(i, 2).Value = EEH_SH_Return.Text
End If
Next
        
        Call cmdResetHoliday_Click
        
        MsgBox ("Holiday Successfully Updated!")

End Sub
Private Sub DeleteHoliday()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim r As Range, X, a
    X = Me.EEH_SH.Value
    If X = Empty Then Exit Sub
    If MsgBox("Are you sure you want to delete " & vbLf & X & "?  There is no undo.", vbYesNo) = vbYes Then
               
    'Insert Blank Row In MasterLog
    Sheets("MasterLog").Visible = True
    Sheets("MasterLog").Select
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown
    Worksheets("MasterLogTemplate").Range("1:1").Copy Worksheets("MasterLog").Range("10:10")
    
    'Log Holiday Name
    Sheets("MasterLog").Range("C10") = Me.EEH_SH.Value
        
    'Log Action
    Range("E10").Select
    ActiveCell.Value = "Holiday Deleted"
    
    'Log Name
    Sheets("MasterLog").Range("I10") = Application.UserName
    
    'Log Timestamp
    Sheets("MasterLog").Range("A10") = [Text(Now(), "MM/DD/YYYY HH:MM:SS AM/PM")]
    
    'Sort MasterLog
    Application.Goto Reference:="MasterLogTimeStampColumn"
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MasterLog").Sort.SortFields.Add2 Key:=Range( _
        "MasterLogTimeStampColumn"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MasterLog").Sort
        .SetRange Range("MasterLogAllColumns")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
          
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    ActiveWindow.SmallScroll Down:=-18
    Range("A" & (ActiveCell.Row)).Select
        
    'Goto Last Row of MasterLog
    With Range("MasterLogTimestampColumn")
    .Range("A" & .Rows.Count).EntireRow.Select
    End With
    Range("A" & (ActiveCell.Row)).Select
    
    Sheets("MasterLog").Visible = False
           
    With Sheets("Holidays")
    .Columns(2).Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.EEH_SH.List = a
    End If
    
    Sheets("Calendar").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
@jmpatrick
You need to load an array to a listbox. A single value isn’t an array but you can convert it into an array. Try something like this:
VBA Code: 
    'Loads Floor Plan Request
    Set wsFloorDesigners = ThisWorkbook.Worksheets("FloorDesigners")
   
    If Not IsArray([FloorDesigners!DesignerNames].Value) Then
        DesignerNames.List = Array([FloorDesigners!DesignerNames].Value)
    Else
        DesignerNames.List = [FloorDesigners!DesignerNames].Value
    End If
   
    'do the same thing to other listboxes
    FloorPlanRequestReturn.List = [FloorPlanRequests!FloorPlanRequests].Value
    CCNames.List = [Users!UserNames].Value
    BCCNames.List = [Users!UserNames].Value

and since you have multiple listboxes then you might want to convert the code into a function.
 
Upvote 0
Solution
Akuini said:
@jmpatrick
You need to load an array to a listbox. A single value isn’t an array but you can convert it into an array. Try something like this:
VBA Code: 
    'Loads Floor Plan Request
    Set wsFloorDesigners = ThisWorkbook.Worksheets("FloorDesigners")
  
    If Not IsArray([FloorDesigners!DesignerNames].Value) Then
        DesignerNames.List = Array([FloorDesigners!DesignerNames].Value)
    Else
        DesignerNames.List = [FloorDesigners!DesignerNames].Value
    End If
  
    'do the same thing to other listboxes
    FloorPlanRequestReturn.List = [FloorPlanRequests!FloorPlanRequests].Value
    CCNames.List = [Users!UserNames].Value
    BCCNames.List = [Users!UserNames].Value

and since you have multiple listboxes then you might want to convert the code into a function.
Click to expand...

Nice. That works great. I don't know why it works but that's not important. I'll study it until I do.

Thanks!
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Error 1004
2
Replies
18
Views
316
dmt32
D
D
  • Solved
Class Module to include one decimal place in userform textboxes
Replies
3
Views
72
dlee83
D
F
  • Question Question
Update userform
2
Replies
13
Views
226
Formula11
F
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
189
RoryA
RoryA
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
67
Denny57
D

Forum statistics

Threads
1,223,628
Messages
6,173,426
Members
452,515
Latest member
Alicedonald9

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top