Excel userform code

Option Explicit

Private Sub cmdCancel_Click()

Unload Me

End Sub

Private Sub createFldr(fldr As String)

fldr = Left(Application.UserLibraryPath, InStr(10, Application.UserLibraryPath, "\")) & _
        filepath & _
        "\" & fldr

If Dir(fldr, vbDirectory) = "" Then
    Debug.Print (fldr)
    MkDir (fldr)
Else
    MsgBox ("Folder already created")
End If

End Sub

Private Function OKerrCheck(obj As Object, nam As String) As String

Dim msg As String

msg = "Select a " & nam & vbNewLine

With obj
    If .Value = "" Then
        OKerrCheck = msg
    ElseIf .ListIndex = -1 Then
        If MsgBox(nam & " not found. Do you want to add it to the list?", vbYesNo) = 7 Then OKerrCheck = msg
    Else
        OKerrCheck = ""
    End If
End With

End Function

Private Sub cmdOK_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim assNum As Range
Dim response As Integer
Dim errmsg As String
Set assNum = Sheet3.Cells(2, 6)

'Error checking (adds text to the error message. If the message isn't blank then the sub is exited)
With Me

    If .txtDesc = "" Then errmsg = errmsg & "Description missing" & vbNewLine
    If .txtSN = "" Then errmsg = errmsg & "Serial Number missing" & vbNewLine
    If .txtModel = "" Then errmsg = errmsg & "Model Number missing" & vbNewLine

    errmsg = errmsg & OKerrCheck(.cmbCat, "Category")
    errmsg = errmsg & OKerrCheck(.cmbManuf, "Manufacturer")
    errmsg = errmsg & OKerrCheck(.cmbSupplier, "Supplier")
    errmsg = errmsg & OKerrCheck(.cmbLocat, "Location")

    If .cmbSys.Value = "" Then
        errmsg = errmsg & "Select a System " & vbNewLine
    ElseIf .cmbSys.ListIndex = -1 Then
        response = MsgBox("System not found. Do you want to create a new System?", vbYesNoCancel)
        If response = 2 Then
            Exit Sub
        ElseIf response = 7 Then
            errmsg = errmsg & "Select a System " & vbNewLine
        ElseIf response = 6 Then
            createFldr (.cmbSys.Value)
        End If
    End If

    If .chkPPM And .cmbPPMFreq.ListIndex = -1 Then errmsg = errmsg & "Select a Physical Maintenance frequency " & vbNewLine
    If .chkICal And .cmbICalFreq.ListIndex = -1 Then errmsg = errmsg & "Select an Internal Calibration frequency " & vbNewLine
    If .chkECal And .cmbECalFreq.ListIndex = -1 Then errmsg = errmsg & "Select an External Calibration frequency " & vbNewLine
End With

If Not errmsg = "" Then
    errmsg = Left(errmsg, Len(errmsg) - 1)
    MsgBox (errmsg)
    Exit Sub
End If


With Sheet2.ListObjects("Table2")
'   Add new row
    .ListRows.Add (1)
    .ListRows(2).Range.Copy
    .ListRows(1).Range.PasteSpecial xlPasteFormats

    assNum = assNum + 1

'   Populate asset list
    With .ListRows(1)
        .Range(1) = "PAC-" & assNum
        .Range(2) = Me.txtDesc.Value
        .Range(3) = Me.txtSN.Value
        .Range(4) = Me.txtModel.Value
        .Range(5) = Me.cmbCat.Value
        .Range(6) = Me.cmbManuf.Value
        .Range(7) = Me.cmbSupplier.Value
        .Range(8) = Me.cmbLocat.Value
        .Range(9) = Me.cmbSys.Value
        .Range(10) = Me.txtTag.Value
        .Range(11) = Date

        If Me.chkATEX Then .Range(12) = "5 Yearly" Else: .Range(12) = "-"
        If Me.chkStat Then .Range(15) = Date Else: .Range(15) = "-"
        If Me.chkICal Then .Range(17) = Me.cmbICalFreq.List(Me.cmbICalFreq.ListIndex, 1) Else: .Range(17) = "-"
        If Me.chkECal Then
            .Range(20) = Me.cmbECalFreq.List(Me.cmbECalFreq.ListIndex, 1)
            .Range(21) = Date
        Else
            .Range(20) = "-": .Range(21) = "-"
        End If
        If Not Me.chkElec Then .Range(23) = "-"
        If Me.chkPPM Then
            .Range(25) = Me.cmbPPMFreq.List(Me.cmbPPMFreq.ListIndex, 1)
            .Range(26) = Date
        Else
            .Range(25) = "-": .Range(26) = "-"
        End If

        'Create Asset folder
        createFldr (Me.cmbSys.Value & "\" & .Range(1).Value)

    End With
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Public Sub UserForm_Initialize()

Dim arr As Variant, transArr(0 To 6, 0 To 1) As Variant
Dim i As Integer, j As Integer

arr = Array(Array(Null, "1m", "3m", "6m", "1y", "3y", "5y"), Array(Null, "Monthly", "Quarterly", "6 Monthly", "Yearly", "3 Yearly", "5 Yearly"))

For i = 0 To 1
    For j = 0 To 6
        transArr(j, i) = arr(i)(j)
    Next j
Next i

With Application.WorksheetFunction
    Me.cmbCat.List = .Sort(.Unique(Range("Table2[Equipment Category]").Value))
    Me.cmbManuf.List = .Sort(.Unique(Range("Table2[Manufacturer]").Value))
    Me.cmbSupplier.List = .Sort(.Unique(Range("Table2[Supplier]").Value))
    Me.cmbLocat.List = .Sort(.Unique(Range("Table2[Location]").Value))
    Me.cmbSys.List = .Sort(.Unique(Range("Table2[System Related To]").Value))
End With

Me.cmbPPMFreq.List = transArr
Me.cmbICalFreq.List = transArr
Me.cmbECalFreq.List = transArr

Me.StartUpPosition = 0
Me.Top = frmCtrl.Top + frmCtrl.Height + 0
Me.Left = Application.Left + Application.Width - Me.Width - 25

End Sub