Forum Discussion

Mamajobyu's avatar
Mamajobyu
Copper Contributor
Mar 14, 2024

VBA coding Word help

I am just venturing into the world of Visual Basic/userforms and I need some help. 

 

General purpose:

I am creating a form where based on the selections in the form, it interacts with the word document template which includes linking to bookmarks and un-collapsing applicable styled paragraphs. I have 3 buttons. When each of them are selected, I need certain paragraphs to be un-collapsed. 

 

Here is my userform:

 

The three buttons I need help with are the GrantPWDbut (Grant PWD), LoanPWDbut (Loan PWD), and Reqsbut (Outstanding Requirements).

  • When GrantPWDbut is True, the HeadingGrant Style collapse will need to false (uncollapse)  
  • When LoanPWDbut is True, both HeadingLoan1 and HeadingLoan2 collapse will need to false.  
  • When Reqsbut is True, both HeadingReqs1 and HeadingReqs2 collapse will need to false. 

Need help: 

1.  When I only have one associated paragraph, I can get it to uncollapse but only sometimes. I can't figure out how to get it to work all the time. 

  • Do I need to collapse the applicable styles whenever I open the document? If so, how can I collapse multiple styles?

2. When I have multiple paragraphs associated with a button, I don't know how to collapse multiple paragraphs. 

I have found a few applicable forums but since I'm new to VBA coding, I am struggling to know what pieces I need and what I don't need and how to tweak it to apply to my scenario. These are some of the resources I've found. 

3. I also have a section in my form for requirements.

  • I have programmed it to select multiple, but I don't know how to insert the selections into my document. I do have a style and bookmark set up and started setting up the language to put it in, but it's not working. I think I am missing a variable assignment maybe in the ReqsListBox initialization of the form?  
  • Also, is there a way to add a the Aid Year and Tax Year into certain fields before inserting it into my document? For example, the tax form would need to have the tax year listed ("Student - [2022] Federal IRS Tax...." where the tax year ("2022)" is selected from the userform. 
  • Really, I'd be ok with having a shortened version on the userform like "student tax form" and then if that item is selected, it would show the full name in the bookmarked area. Is this a better option? I am thinking this might be better so that perhaps I can populate the hyperlink text on the few items with a website link. 

 

Here is the coding associated with the questions I have: 

'-------------------------------------------------------------------

Private Sub OKbut_Click()

 

‘---assign bookmark -- this section is giving me errors…..   

    Dim ReqsListBox As Range

    Set ReqsListBox = ActiveDocument.Bookmarks("ReqList").Range

    ReqsListBox.Text = Me.ReqList.Value

 

 

'---Collapse Paragraphs' this section is giving me errors…..

        'Bookmark navigation:

        'HeadingGrant = section with amounts

        'HeadingLoan1 = section with amounts

        'HeadingLoan2 = section with signatures

        'HeadingReqs1 = main section directing to page 2

        'HeadingReqs2 = page2 paragraph(s)

    If GrantPWDBut.Value = True Then

        Selection.Find.Style = ActiveDocument.Styles("HeadingGrant")

        Selection.Paragraphs(1).CollapsedState = False

    End If

   

    If LoanPWDBut.Value = True Then

        Selection.Find.Style = ActiveDocument.Styles("HeadingLoan1")

‘ need to add “HeadingLoan2”

        Selection.Paragraphs(1).CollapsedState = False

    End If

 

 

    Me.Repaint

 

    PWDForm.Hide

End Sub

 

'-------------------------------------------------------------------

 

Private Sub UserForm_Initialize()

    Term.List = Array("Summer I", "Summer II", "Fall", "Spring")

    Year.List = Array("2024", "2025", "2026", "2027", "2028", "2029", "2030")

    AidYear.List = Array("2024-25", "2025-26", "2026-27", "2027-28", "2028-29", "2029-30", "2030-31")

    TaxYear.List = Array("2022", "2023", "2024", "2025", "2026", "2027", "2028", "2029", "2030")

    GrantType1.List = Array("Federal Pell Grant", "Federal SEOG")

    GrantType2.List = Array("Federal Pell Grant", "Federal SEOG")

    LoanType1.List = Array("Federal Direct Unsubsidized Loan", "Federal Direct Subsidized Loan", "Federal Direct Grad PLUS Loan", "Federal Direct Parent PLUS Loan")

    LoanType2.List = Array("Federal Direct Unsubsidized Loan", "Federal Direct Subsidized Loan", "Federal Direct Grad PLUS Loan", "Federal Direct Parent PLUS Loan")

    LoanType3.List = Array("Federal Direct Unsubsidized Loan", "Federal Direct Subsidized Loan", "Federal Direct Grad PLUS Loan", "Federal Direct Parent PLUS Loan")

    LoanType4.List = Array("Federal Direct Unsubsidized Loan", "Federal Direct Subsidized Loan", "Federal Direct Grad PLUS Loan", "Federal Direct Parent PLUS Loan")

    ReqsListBox.List = Array("Verification Form - Independent", "Verification Form - Dependent", "Student - Federal IRS Tax Return Transcript or Signed Federal IRS Form 1040", "Parent - Federal IRS Tax Return Transcript or Signed Federal IRS Form 1040", "For Federal Direct Subsidized/Unsubsidized loans: Completed Loan Entrance Counseling. Available online at: https://studentaid.gov/entrance-counseling)", "For Federal Direct PLUS loans: Completed Loan Entrance Counseling. Available online at: https://studentaid.gov/entrance-counseling", "For Federal Direct Subsidized/Unsubsidized loans: Completed a Loan Agreement (Master Promissory Note/MPN). Available online at: https://studentaid.gov/mpn", "For Federal Direct PLUS loans: Completed a Loan Agreement (Master Promissory Note/MPN). Available online at: https://studentaid.gov/mpn", "Other - Contact the Financial Aid Office")

    With ReqsListBox

    .MultiSelect = fmMultiSelectExtended

    End With

End Sub

 

 

Resources