Top Userform

Private Sub cb_DualTCs_Click()
DualsUserform.Show
End Sub

Private Sub cmb_Duals_Change()
     ' When a name is selected, get its full version
    Dim fullName As String
    fullName = GetFullName(cmb_Duals.Value)

    ' Using tag property to identify the content control
    Dim cc As ContentControl
    For Each cc In ActiveDocument.ContentControls
        If cc.tag = "text_PerfDual" Then
            cc.Range.text = fullName
            Exit For
        End If
    Next cc
End Sub

Private Sub cmb_Test_Click()
   ' Add bottom entries with user-specific data
    Call AddBottomEntries(text25.Value, text5.Value, txtCustomEntry.Value)

End Sub

Private Sub UserForm_Initialize()
        InitializeForm
      ' Sets object defaults
    cb_Extension = False
    txt_Extension.Visible = False
    label_ExtendTime.Visible = False
    CompassFrame.Visible = False
    FireExtFrame.Visible = False
    FirstAidFrame.Visible = False
    WBFrame.Visible = False
    DualsFrame.Visible = False
    opt_Maint.Value = True

End Sub

Private Sub cb_Extension_Click()
    label_ExtendTime.Visible = cb_Extension.Value
    txt_Extension.Visible = cb_Extension.Value
End Sub

Private Sub cb_FireExt_Click()
    FireExtFrame.Visible = cb_FireExt.Value
End Sub

Private Sub cb_FirstAidKit_Click()
    FirstAidFrame.Visible = cb_FirstAidKit.Value
End Sub

Private Sub cb_WB_Click()
    WBFrame.Visible = cb_WB.Value
End Sub


 Private Sub InitializeForm()
    ' Arrays for combo box values
    Dim inspectionTypes As Variant
    inspectionTypes = Array("75 Hour inspection", "150 Hour inspection", "300 Hour inspection", _
                           "1500 Hour inspection", "6000 Hour inspection", "100 Hour inspection", _
                           "500 Hour inspection", "1000 Hour inspection")

    Dim dualNames As Variant
    dualNames = Array("Dave J", "Dave M", "Steve", "Peter", "Pierre", "Marc")

    Dim bottomAddsItems As Variant
    bottomAddsItems = Array("Engine leak check required after first flight", _
                          "Aircraft released conditional to satisfactory 1500hr inspection / engine break in test flight ", _
                          "Aircraft released conditional to satisfactory test flight due to fuel system adjustments carried out", _
                          "Propeller torque check required after first flight and after first 25hrs to not exceed X hrs TTAF", _
                          "Initial 5hr alternator belt tension check required to not exceed Y hrs ", _
                          "Initial 25hr engine operation inspection required to not exceed X hrs TTAF", _
                          "25 hour propeller retorque due at X hrs TTAF")

    ' Populate BottomAddsList
    Dim item As Variant
    For Each item In bottomAddsItems
        Me.BottomAddsList.AddItem item
    Next item
    Me.BottomAddsList.MultiSelect = fmMultiSelectMulti

    ' Populate inspection type combo box
    For Each item In inspectionTypes
        Me.cmb_InspectionType.AddItem item
    Next item
    Me.cmb_InspectionType.Value = "75 Hour inspection"  ' Default value

    ' Populate duals combo box
    For Each item In dualNames
        Me.cmb_Duals.AddItem item
    Next item

    ' Populate recurring defect combo box
    With Me.cmb_RecurDef
        .AddItem "No"
        .AddItem "Yes"
        .Value = "No"  ' Default value
    End With
End Sub

Public Function BuildWPFull() As String
    Dim regPart As String
    regPart = Right(txt_Reg.text, 3) ' Get the last 3 characters of txt_Reg.text  **Tested**
    BuildWPFull = UCase(regPart & "-" & txt_WPYear.text & "-" & txt_WPNumber.text)
End Function

Private Function GetFullName(shortName As String) As String
    ' Map short names to full names
    Select Case shortName
        Case "Dave J"
            GetFullName = "David Jones 450901"
        Case "Dave M"
            GetFullName = "Bob Smith"
        Case "Steve"
            GetFullName = "Sue Ellen"
         Case "Peter"
            GetFullName = "Sue Ellen"
        Case "Pierre"
            GetFullName = "Sue Ellen"
        Case "Marc"
            GetFullName = "xxx"
        Case Else
            GetFullName = shortName  ' Return original if no mapping exists
    End Select
End Function




Private Sub cb_Compass_Click()
    Dim doc As Document
    Dim cc As ContentControl
    Dim tbl As Table
    Dim ccInTable As ContentControl
    Dim tblRange As Range

    ' Check the state of the checkbox and hide/show CompassFrame accordingly
    CompassFrame.Visible = cb_Compass.Value

    ' Reference the active document
    Set doc = ActiveDocument

    ' Find the table content control by its title or tag
    For Each cc In doc.ContentControls
        If cc.Title = "box_Compass" Or cc.tag = "box_Compass" Then
            If cc.Range.Tables.Count > 0 Then
                Set tbl = cc.Range.Tables(1) ' Get the table within the content control
                Set tblRange = tbl.Range
                Exit For
            End If
        End If
    Next cc

    ' If the table is found, toggle its visibility based on the checkbox value
    If Not tbl Is Nothing Then
        If cb_Compass.Value Then
            tblRange.Font.Hidden = False  ' Show the table
            ' Show all content controls within the table
            For Each ccInTable In doc.ContentControls
                If ccInTable.Range.Start >= tblRange.Start And ccInTable.Range.End <= tblRange.End Then
                    ccInTable.Range.Font.Hidden = False
                End If
            Next ccInTable
        Else
            tblRange.Font.Hidden = True   ' Hide the table
            ' Hide all content controls within the table
            For Each ccInTable In doc.ContentControls
                If ccInTable.Range.Start >= tblRange.Start And ccInTable.Range.End <= tblRange.End Then
                    ccInTable.Range.Font.Hidden = True
                End If
            Next ccInTable
        End If
    End If
End Sub

Private Sub SetVisibility(ByVal inspectionTypeVisible As Boolean, _
                          ByVal snagEntryVisible As Boolean, _
                          ByVal extensionVisible As Boolean)
    cmb_InspectionType.Visible = inspectionTypeVisible
    label_InspectionType.Visible = inspectionTypeVisible
    cb_Extension.Visible = extensionVisible
    txt_SnagEntry.Visible = snagEntryVisible
    label_SnagEntry.Visible = snagEntryVisible
    label_SnagRectification.Visible = snagEntryVisible
    txt_SnagRect.Visible = snagEntryVisible
End Sub

Private Sub opt_Snag_Click()

    SetVisibility False, True, False
 UpdateContentBoxesVisibility
End Sub

Private Sub opt_Maint_Click()
    SetVisibility True, False, True
    UpdateContentBoxesVisibility
    End Sub

Private Sub opt_Both_Click()
    SetVisibility True, True, True
   UpdateContentBoxesVisibility
End Sub
Private Sub UpdateContentBoxesVisibility()
    Dim ccSnag As ContentControl
    Dim ccMaint As ContentControl

    ' Get reference to content controls
    Set ccSnag = ActiveDocument.SelectContentControlsByTag("box_Snag")(1)
    Set ccMaint = ActiveDocument.SelectContentControlsByTag("box_Maint")(1)

    ' Set visibility based on the selected option button
    If opt_Snag.Value Then
        ccSnag.Range.Font.Hidden = False
        ccMaint.Range.Font.Hidden = True
    ElseIf opt_Maint.Value Then
        ccSnag.Range.Font.Hidden = True
        ccMaint.Range.Font.Hidden = False
    ElseIf opt_Both.Value Then
        ccSnag.Range.Font.Hidden = False
        ccMaint.Range.Font.Hidden = False
    End If
End Sub


Private Sub btnSubmit_Click()

    'Make WPFull string available
    Dim doc As Document
    Dim WPFull As String
    WPFull = BuildWPFull()


 ' Set the active document
    Set doc = Application.ActiveDocument

    ' After handling the button click, update the content controls **Tested
    Call UpdateContentControls

      ' If Snag or Both is checked, enter Snag Entry and Rectification **Tested
    If opt_Snag.Value = True Or opt_Both.Value = True Then
    ' Prepend "Snag: " to the text and call the module procedure
        UpdateSnagContent "Snag: " & txt_SnagEntry.Value, "text_SnagTitle"
        UpdateSnagContent txt_SnagRect.Value, "text_SnagRectify"

    End If


    ' Updates inspection type with or without extension in Maintenance header **Tested
    UpdateMaintHeader doc, cmb_InspectionType.Value, cb_Extension.Value

    ' Adds extension entry and hours
    Dim ExtensionChecked As Boolean
    Dim AddWorkChecked As Boolean
    Dim inspectionType As String

    ' Capture values from the userform for extension
    ExtensionChecked = cb_Extension.Value
    AddWorkChecked = cb_Addwork.Value
    inspectionType = cmb_InspectionType.Value

    ' Call the UpdateMaintenanceBody subroutine
    UpdateMaintenanceBody ExtensionChecked, inspectionType, AddWorkChecked



        ' Updates compass headings, only if compass checkmark is ticked
    If cb_Compass.Value = True Then
        ' Call the UpdateCompassHeadings subroutine to update the compass headings in the document
        UpdateCompassHeadings
        End If


      ' Add bottom entries with user-specific data
    Call AddBottomEntries(text25.Value, text5.Value, txtCustomEntry.Value)

End Sub


Private Sub UpdateContentControls()
    Dim cc As ContentControl
    Dim WPFull As String
    ' Build the WPFull string
    WPFull = BuildWPFull()

    ' Loop through all content controls in the document
    For Each cc In ActiveDocument.ContentControls
        Select Case cc.tag
            Case "Text_Reg"
                cc.Range.text = UCase(txt_Reg.text) ' Convert to uppercase before setting the value
            Case "Text_Date"
                cc.Range.text = txt_Date.text
            Case "Doc_RecDef"
                cc.Range.text = cmb_RecurDef.Value
            Case "Text_TTAF"
                cc.Range.text = txt_TTAF.text & " Hrs"
            Case "Text_WPFull"
                cc.Range.text = WPFull ' Update the WPFull content control
                cc.Range.Font.Bold = True ' Apply bold formatting
            Case "Doc_InspectionType"
                cc.Range.text = cmb_InspectionType.Value
        End Select
    Next cc
End Sub

Private Sub cb_Duals_Click()
    ' Get reference to the document
    Dim doc As Document
    Set doc = ActiveDocument

    DualsFrame.Visible = cb_Duals.Value

    ' Get reference to the content control box
    Dim boxDuals As ContentControl
    On Error Resume Next
    Set boxDuals = doc.SelectContentControlsByTitle("box_Duals").item(1)
    On Error GoTo 0

    ' Check if the content control exists
    If boxDuals Is Nothing Then
        MsgBox "Content control 'box_Duals' not found!", vbExclamation
        Exit Sub
    End If

    ' Show/hide the box based on checkbox state
    If cb_Duals.Value Then
        boxDuals.Range.Font.Hidden = False
    Else
        boxDuals.Range.Font.Hidden = True
    End If
End Sub




Private Sub AddBottomEntries(userNumberX As String, userNumberY As String, customEntry As String)
    Dim i As Integer
    Dim selectedItem As String

    For i = 0 To BottomAddsList.ListCount - 1
        If BottomAddsList.Selected(i) Then
            selectedItem = Replace(Replace(BottomAddsList.List(i), "X", userNumberX), "Y", userNumberY)
            AddTextToDocument "*** " & Trim(selectedItem) & " ***", True
        End If
    Next i

    ' If there is a custom entry, add it to the document
    If customEntry <> "" Then
        AddTextToDocument "*** " & Trim(customEntry) & " ***", True
    End If
End Sub

Private Sub UpdateCompassHeadings()
    Dim cc As ContentControl
    Dim userFormControl As MSForms.Control
    Dim contentControlName As String
    Dim userFormTextboxName As String

    ' Loop through all content controls in the document
    For Each cc In ActiveDocument.ContentControls
        ' Check if the content control's tag matches one of the predefined values
        Select Case cc.tag
            Case "text_North", "text_30", "text_60", "text_East", "text_120", _
                 "text_150", "text_South", "text_210", "text_240", _
                 "text_West", "text_300", "text_330"

                ' Determine the corresponding textbox on the userform based on the tag
                userFormTextboxName = Replace(cc.tag, "text_", "txt")

                ' Check if the textbox exists on the userform
                On Error Resume Next
                Set userFormControl = Me.Controls(userFormTextboxName)
                On Error GoTo 0

                ' If the textbox exists, update the content control with its value
                If Not userFormControl Is Nothing Then
                    cc.Range.text = UCase(userFormControl.Value) ' Convert to uppercase
                End If
        End Select
    Next cc
End Sub

Private Sub AddTextToDocument(text As String, alignCenter As Boolean)
    Selection.EndKey Unit:=wdStory
    Selection.TypeParagraph
    If alignCenter Then
        Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    End If
    Selection.TypeText text:=text & vbCrLf
End Sub


Dual Userform

Option Explicit
Private counter As Integer

Private Sub UserForm_Initialize()
    counter = 1
End Sub

Private Sub cb_AddRow_Click()
    ' Increment counter for new textbox naming
    counter = counter + 1

    ' Get reference to last textboxes for positioning
    Dim lastControlBox As MSForms.TextBox
    Dim lastTCBox As MSForms.TextBox
    Set lastControlBox = Me.Controls("txt_DualControl" & (counter - 1))
    Set lastTCBox = Me.Controls("txt_DualTC" & (counter - 1))

    ' Create new textboxes
    Dim newControlBox As MSForms.TextBox
    Dim newTCBox As MSForms.TextBox

    ' Create and set properties for Control description textbox
    Set newControlBox = Me.Controls.Add("Forms.TextBox.1", "txt_DualControl" & counter)
    With newControlBox
        .Left = lastControlBox.Left
        .Top = lastControlBox.Top + lastControlBox.Height + 5
        .Width = lastControlBox.Width
        .Height = lastControlBox.Height
    End With

    ' Create and set properties for Task Card textbox
    Set newTCBox = Me.Controls.Add("Forms.TextBox.1", "txt_DualTC" & counter)
    With newTCBox
        .Left = lastTCBox.Left
        .Top = lastTCBox.Top + lastTCBox.Height + 5
        .Width = lastTCBox.Width
        .Height = lastTCBox.Height
    End With

    ' Add new row to the table in the document
    Dim cc As ContentControl
    Dim tbl As Table

    ' Find the content control by its tag
    For Each cc In ActiveDocument.ContentControls
        If cc.tag = "box_DualTCs" Then
            Set tbl = cc.Range.Tables(1)
            ' Add new row to the table
            tbl.Rows.Add
            Exit For
        End If
    Next cc
End Sub

Public Sub TransferDataToTable()
    Dim cc As ContentControl
    Dim tbl As Table
    Dim i As Integer

    ' Find the content control and its table
    For Each cc In ActiveDocument.ContentControls
        If cc.tag = "box_DualTCs" Then
            Set tbl = cc.Range.Tables(1)

            ' Loop through all textbox pairs and add their data to the table
            For i = 1 To counter
                Dim controlBox As MSForms.TextBox
                Dim tcBox As MSForms.TextBox

                Set controlBox = Me.Controls("txt_DualControl" & i)
                Set tcBox = Me.Controls("txt_DualTC" & i)

                ' Add data to table
                tbl.Rows(i).Cells(1).Range.text = controlBox.text
                tbl.Rows(i).Cells(2).Range.text = tcBox.text
            Next i

            Exit For
        End If
    Next cc
End Sub


Private Sub cb_AddData_Click()

'Submits the data into the document
TransferDataToTable

'HIDES the Duals form
Me.Hide

End Sub


Formatting Module

Public Sub ApplyTextFormatToText(ByRef targetRange As Range, _
                                ByVal textToFormat As String, _
                                Optional ByVal FontName As String = "Times New Roman", _
                                Optional ByVal FontSize As Single = 8, _
                                Optional ByVal Bold As Boolean = True, _
                                Optional ByVal Italic As Boolean = False, _
                                Optional ByVal Underline As Boolean = False, _
                                Optional ByVal FontColor As Long = wdColorAutomatic, _
                                Optional ByVal Alignment As WdParagraphAlignment = wdAlignParagraphLeft)
    ' Check if targetRange is valid
    If targetRange Is Nothing Or targetRange.text = "" Then Exit Sub

    ' Find and format the text
    With targetRange.Find
        .text = textToFormat
        .MatchCase = False
        .MatchWholeWord = False
        If .Execute Then
            With .Parent
                ' Apply font properties
                With .Font
                    .Name = FontName
                    .Size = FontSize
                    .Bold = Bold
                    .Italic = Italic
                    .Underline = IIf(Underline, wdUnderlineSingle, wdUnderlineNone)
                    .Color = FontColor
                End With
                ' Apply paragraph alignment
                .ParagraphFormat.Alignment = Alignment
            End With
        End If
    End With
End Sub


' Helper function to add section content with proper formatting
Public Sub AddFormattedSection(ByRef content As String, ByVal mainText As String, ByRef subItems() As String, ByVal WPFull As String)
    content = content & vbCrLf & mainText & " REF WP " & WPFull
    Dim subItem As Variant
    For Each subItem In subItems
        If subItem <> "" Then
            content = content & vbCrLf & subItem
        End If
    Next subItem
End Sub

Updating content module

Public Sub ApplyTextFormatToText(ByRef targetRange As Range, _
                                ByVal textToFormat As String, _
                                Optional ByVal FontName As String = "Times New Roman", _
                                Optional ByVal FontSize As Single = 8, _
                                Optional ByVal Bold As Boolean = True, _
                                Optional ByVal Italic As Boolean = False, _
                                Optional ByVal Underline As Boolean = False, _
                                Optional ByVal FontColor As Long = wdColorAutomatic, _
                                Optional ByVal Alignment As WdParagraphAlignment = wdAlignParagraphLeft)
    ' Check if targetRange is valid
    If targetRange Is Nothing Or targetRange.text = "" Then Exit Sub

    ' Find and format the text
    With targetRange.Find
        .text = textToFormat
        .MatchCase = False
        .MatchWholeWord = False
        If .Execute Then
            With .Parent
                ' Apply font properties
                With .Font
                    .Name = FontName
                    .Size = FontSize
                    .Bold = Bold
                    .Italic = Italic
                    .Underline = IIf(Underline, wdUnderlineSingle, wdUnderlineNone)
                    .Color = FontColor
                End With
                ' Apply paragraph alignment
                .ParagraphFormat.Alignment = Alignment
            End With
        End If
    End With
End Sub


' Helper function to add section content with proper formatting
Public Sub AddFormattedSection(ByRef content As String, ByVal mainText As String, ByRef subItems() As String, ByVal WPFull As String)
    content = content & vbCrLf & mainText & " REF WP " & WPFull
    Dim subItem As Variant
    For Each subItem In subItems
        If subItem <> "" Then
            content = content & vbCrLf & subItem
        End If
    Next subItem
End Sub