VBA in Excel for Statement Generating

Question

I have created an excel file which has two sheets SUMMARY & STATEMENT. Some name wise data is on on the Summary sheet for which I create a statement. I want to get a button called Generate Statement on SUMMARY Sheet clicking on which, it must ask me to select the names, for which it generates the same statement I created asking me whether to generate in excel format or pdf format.

Answer 1

Dear Noel

Theoretically what you said is also known to me but practically I am not able to do so and that is what i am looking for. Please help me practically how to do the same.

Answer 2

Hi,

Thank you for sharing your question. I understand the need to make this fast and reliable with a single “Generate Statement” button that prompts for names and produces either Excel or PDF output.

This workflow is best handled with a macro tied to a button; Excel supports assigning a macro to a button placed on your worksheet, and the macro can prompt you to select cells containing names, ask for the desired output type, filter the SUMMARY data for each name, populate the STATEMENT template, and then save as .xlsx or export as .pdf. The macro uses Excel’s Application.InputBox to collect your selection and format choice, AutoFilter or AdvancedFilter to pull name‑specific rows, SaveAs for .xlsx, and ExportAsFixedFormat for PDF, all of which are documented in Microsoft Learn so your solution stays aligned with current guidance.

To resolve this, add a button to the SUMMARY sheet and assign it to the macro below; the macro will ask you to select the cells that contain the names to process, then ask whether you want Excel or PDF, and for each name it will filter SUMMARY, copy the filtered rows into your STATEMENT template, and save each statement as either an .xlsx workbook or a .pdf file using the built‑in methods referenced above.

 

I hope this helps.

 

Best Regards,

Noel

Answer 3

To create a button in Excel that generates statements based on selected names from the SUMMARY sheet, you can use VBA (Visual Basic for Applications). Below is a general outline of how you can set this up:

1. Create a Button: In the SUMMARY sheet, go to the Developer tab, click on "Insert," and choose a button from the Form Controls. Draw the button on the sheet and assign a macro to it.
2. Write the VBA Code: Open the Visual Basic for Applications editor (ALT + F11) and create a new module. Here is a sample code snippet that you can modify according to your needs:

      Sub GenerateStatement()
          Dim names As Range
          Dim nameCell As Range
          Dim outputFormat As String
          Dim statement As String
      
          ' Prompt user to select names
          On Error Resume Next
          Set names = Application.InputBox("Select names:", Type:=8)
          On Error GoTo 0
      
          If names Is Nothing Then Exit Sub
      
          ' Ask for output format
          outputFormat = MsgBox("Choose output format: Yes for Excel, No for PDF", vbYesNoCancel, "Output Format")
      
          For Each nameCell In names
              ' Here you would generate the statement for each name
              statement = "Statement for " & nameCell.Value
              ' Add your logic to create the statement
      
              ' Output based on selected format
              If outputFormat = vbYes Then
                  ' Code to generate and save in Excel format
              ElseIf outputFormat = vbNo Then
                  ' Code to generate and save in PDF format
              End If
          Next nameCell
      End Sub
   

3. Customize the Code: Modify the code to fit your specific requirements, such as how to generate the statement and how to save it in the desired format.
4. Test the Button: After writing the code, return to Excel, click the button, and test the functionality.

This approach allows you to select names and generate statements dynamically based on user input.