Using USERFORM to collect variables and choose appropriate macro

**ExcelVBAHelp** · 03-13-2010, 04:11 AM

Hi Everyone,

I have researched the site, and I am still trying to figure out how to use VBA to accomplish the 'vision' that I have. The knowledge that I've gained from the board has enabled me to code some of my program, but there are certainly a couple of major holes. Nevertheless, thanks to everyone who has contributed! I'm fairly new to VBA programming, so please excuse the 'basic' questions.

Here is a flowchart of what I have in mind:

Start a UserForm:
After the user loads raw data into a worksheet (named "RawData"), a 'Generate' button is clicked to begin the following process. (Completed)

UserForm collects 2 variables:
A userform pops up and asks for two variables based on the following:
Type of Report: 3 types - based on radio buttons (Not Completed)
Method of Output: Print or Create PDF (Not Completed)

Based on the above two variables, run two macros:
Create 1 of the 3 reports specified by the radio buttons (Macro created, but do not know how to link)
Prepare either the 'print preview' screen or the PDF based on the drop-down menu for the report generated (Macro created, but do not know how to link)

Your input on my coding would be much appreciated. Any other suggestions or helpful hints are certainly welcome. I just realized that VBA and Excel automation are certainly useful in the accounting/finance field. Your help would make all the difference!

Thank you in advance!

**mikerickson** · 03-13-2010, 06:34 AM

The attached has a userform with 3 option buttons, with captions "ReportType1", "ReportType2" and "ReportType3".
There are also two checkboxes named, cbxPrint and cbxPDF
as well as buttons, butOK and butCancel.

in the userform's code module

Code:

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Hide
End Sub

Function optionChosen() As String
    Dim oneButton As Variant
    For Each oneButton In Array(OptionButton1, OptionButton2, OptionButton3)
        If oneButton.Value Then
            optionChosen = oneButton.Caption
        End If
    Next oneButton
End Function

in a normal module

Code:

Sub ReportAndOut()
    Dim reportType As String
    Dim outputToPrint As Boolean, outputToPDF As Boolean
    
    UserForm1.Show
    
    With UserForm1
        reportType = .optionChosen
        outputToPrint = .cbxPrint.Value
        outputToPDF = .cbxPDF.Value
    End With
    
    Unload UserForm1

    Select Case reportType
        Case "ReportType1"
            Rem make report type 1
        Case "ReportType2"
            Rem make report type 2
        Case "ReportType3"
            Rem make report type 3
        Case vbNullString
            Rem canceled
        Case Else
            MsgBox reportType & "-bad option button caption"
            Exit Sub
    End Select

    If outputToPrint Then
        Rem call print routine
    End If

    If outputToPDF Then
        Rem call PDF routine
    End If
    
End Sub

**mikerickson** · 03-13-2010, 05:04 PM

Please proved a link to your cross-post on a different forum.

Waiting before cross posting is appreciated, but going back and linking to the new cross posting prevents duplicate effort.

**ExcelVBAHelp** · 03-15-2010, 01:20 AM

Thanks so much for the help mikerickson. I replied to you on the other forum, but I will say the same here. Your feedback made me run out and get MrExcel's 'vba and macro for excel 2007' the very same day. I'm trying hard to learn this as fast as humanly possible.

I will be more mindful with cross-posting.

Kinda new to the whole forum scene.

Thanks again!

**ExcelVBAHelp** · 03-15-2010, 01:22 AM

Also, what can you (or other members) recommend as the 'best' resource for learning VBA for excel in the business/accounting realm? I'm half way through my book already. I just finished university, so going through large books and taking notes is my specialty