Using Option Button to Set Variable

[samerickson89]

I've got a command button that prints my workbook to a PDF with a specified filename and location, but I've been asked to add the option to save to one of two folders (without being able to browse to other folders). I'm trying to use a user form to choose the folder name, save it as a string variable, and append it to the file path. Two things I'm having trouble with are:

1) How do I make the user form assign a value to a string variable based on which option button is selected?

2) How do I set up an "OK" button on the user form (closes the user form and continues the module it was opened from)?

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=ActiveWorkbook.Path & "\Completed RI Reports\" & OutName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    
End Sub

I would want the user to select either the "FCAD" or "PDL" option button on the user form, which would then be added to the file path after "\Completed RI Reports"

I'm also open to other approaches, if you have something simpler or more efficient. Thanks in advance for any help!

 

[Yongle]

Try something like this

Include this line in UserForm_Initialize :

OptionButton1.Value = True

Command button :

Private Sub CommandButton1_Click()
    Dim fpath As String    

    [COLOR=#ff0000][I]rest of your code goes here[/I][/COLOR]
    
[I][COLOR=#006400]'your original path[/COLOR][/I]
    fpath = ActiveWorkbook.path & "\Completed RI Reports\"
[COLOR=#006400][I]'append correct subfolder[/I][/COLOR]
    If OptionButton1.Value = True Then
        fpath = fpath & "FCAD\"
    ElseIf OptionButton2.Value = True Then
        fpath = fpath & "PDL\"
    End If
[COLOR=#006400][I]'export to PDF [/I][/COLOR]
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=[COLOR=#ff0000]fpath & outname[/COLOR], _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
'unload userform    
    Unload Me
End Sub

 

[samerickson89]

Try something like this

Include this line in UserForm_Initialize :

OptionButton1.Value = True

Command button :

Private Sub CommandButton1_Click()
    Dim fpath As String    

    [COLOR=#ff0000][I]rest of your code goes here[/I][/COLOR]
    
[I][COLOR=#006400]'your original path[/COLOR][/I]
    fpath = ActiveWorkbook.path & "\Completed RI Reports\"
[COLOR=#006400][I]'append correct subfolder[/I][/COLOR]
    If OptionButton1.Value = True Then
        fpath = fpath & "FCAD\"
    ElseIf OptionButton2.Value = True Then
        fpath = fpath & "PDL\"
    End If
[COLOR=#006400][I]'export to PDF [/I][/COLOR]
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=[COLOR=#ff0000]fpath & outname[/COLOR], _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
'unload userform    
    Unload Me
End Sub

Ok I think I mostly understand, but how do I actually close the UserForm and continue running the module it was opened from?

 

[Yongle]

Have you tested the code ?
- it closes the userform

Why are you opening the userform from a module and what is code in the module doing after the userform is closed ?
- just wondering whether some of that code should be included in the userform code

 

[samerickson89]

Have you tested the code ?
- it closes the userform

Why are you opening the userform from a module and what is code in the module doing after the userform is closed ?
- just wondering whether some of that code should be included in the userform code

I'll just go ahead and post the entire code for both the command button and the userform. Before adding the line "UserForm1.Hide" nothing would happen after making a selection, the userform would just stay open and the command button code wouldn't continue to run.

Sub Print_Report()
Worksheets(1).Activate

'define variables for output file name[INDENT]Dim PartNo As String
Dim PurchNo As String
Dim Today As String
Dim OutName As String
[/INDENT]
'define output file name[INDENT]PartNo = ActiveSheet.Range("D6").Value
PurchNo = ActiveSheet.Range("D4").Value
InspDate = Format(Range("D2").Value, "yyyymmdd") 'replaced by DateRecd
DateRecd = Format(Range("D3").Value, "yyyymmdd")
OutName = PartNo & "_" & PurchNo & "_" & DateRecd
[/INDENT]
'choose folder and define file path[INDENT]Dim FPath As String
FPath = ActiveWorkbook.Path & "\Completed RI Reports\"
UserForm1.Show
If FCADOptionButton.Value = True Then
    FPath = FPath & "FCAD Completed RI Reports\"
ElseIf PDLOptionButton.Value = True Then
    FPath = FPath & "PDL Completed RI Reports\"
End If
[/INDENT]
'select all visible sheets, don't replace selection
    If ws.Visible Then ws.Select (False)
Next

'output as .pdf with file name defined above[INDENT]ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=ActiveWorkbook.Path & "\Completed RI Reports\" & OutName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True[/INDENT]

End Sub

Private Sub FCADOptionButton_Click()[INDENT]FCADOptionButton.Value = True[/INDENT]
End Sub

Private Sub PDLOptionButton_Click()[INDENT]PDLOptionButton.Value = True[/INDENT]
End Sub

Private Sub CancelButton_Click()[INDENT]End[/INDENT]
End Sub

Sub OKButton_Click()[INDENT]UserForm1.Hide[/INDENT]
End Sub

 

[Yongle]

Does the userform contain anything other than these 2 options ?

 

[samerickson89]

Does the userform contain anything other than these 2 options ?

No, that's all I need on it at the moment.

 

[Yongle]

Ok - will post code tomorrow morning.

Just questioning the need for userform. You could use an input box (or even a message box) to elicit required response from user

 

[Yongle]

This avoids userform and uses a simple message box

Sub Print_Report()

[COLOR=#006400]'define variables for output file name[/COLOR]
    Dim PartNo As String, PurchNo As String, OutName As String, FPath As String
    Dim inspDate As String, dateRecd As String, msg As String
    Dim ws As Worksheet
    Set ws = Worksheets(1)
    FPath = ActiveWorkbook.Path & "\Completed RI Reports\"
    msg = vbTab & "FCAD" & vbTab & vbTab & "PDL"
    ws.Activate

[COLOR=#006400]'define output file name[/COLOR]
    PartNo = ws.Range("D6").Value
    PurchNo = ws.Range("D4").Value
    inspDate = Format(ws.Range("D2").Value, "yyyymmdd") 'replaced by DateRecd
    dateRecd = Format(ws.Range("D3").Value, "yyyymmdd")
    OutName = PartNo & "_" & PurchNo & "_" & dateRecd
    
[COLOR=#006400]'choose folder and define file path[/COLOR]
    If MsgBox(msg, vbYesNo, "YES=FCAD  NO=PDL") = vbYes Then
        FPath = FPath & "FCAD Completed RI Reports\"
    Else
        FPath = FPath & "PDL Completed RI Reports\"
    End If

[COLOR=#006400]'print to PDF[/COLOR]
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FPath & OutName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
End Sub

Will post userform version later

 

[Yongle]

I think I got the path wrong in post 9 - this is probably what you want

    If MsgBox(msg, vbYesNo, "YES=FCAD  NO=PDL") = vbYes Then
        FPath = FPath & "FCAD\"
    Else
        FPath = FPath & "PDL\"
    End If