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]

as promised here is USERFORM version
- UserForm1 contains 2 option buttons (FCADOptionButton & PDLOptionButton)
- all the code goes in the USERFORM module
- the userform is opened to run the report


Place code in USERFORM module

Private Sub FCADOptionButton_Click()
    Call Print_Report_PDF("FCAD")
End Sub

Private Sub PDLOptionButton_Click()
    Call Print_Report_PDF("PDL")
End Sub

Private Sub Print_Report_PDF(aFolder As String)
[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\" & aFolder & "\"
    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]'print to PDF and unload userform[/COLOR]
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FPath & OutName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
       
    Unload Me

End Sub

Code behind worksheet button which shows the userform:

Sub Print_Report()
    UserForm1.Show
End Sub

 

[samerickson89]

Thanks for the suggestions! I think I would still prefer to go with the userform since the msgbox method could a) cause confusion for new users who don't understand the yes/no options, and b) make it difficult to add additional folders. But using the command button on the worksheet to show the userform and placing the rest of the code in the userform module makes a lot more sense!

Beyond that, I took a slightly different approach and I think I've almost got it working. I'm hoping there's just a syntax error. Basically I'm using the option buttons to assign a value to the FoldLoc variable, then using the OK button to run the Continue_Print subroutine with FoldLoc as the input. That way the choice can be changed or canceled before it runs. I get an error "Run-time error '-2147024773 (8007007b)': Document not saved." and the debugger points to the last line before End Sub.

Dim FoldLoc As String

Sub FCADOptionButton_Click()
    FoldLoc = "FCAD Completed RI Reports\"
End Sub

Sub PDLOptionButton_Click()
    FoldLoc = "PDL Completed RI Reports\"
End Sub

Private Sub CancelButton_Click()
    End
End Sub

Sub OKButton_Click()
'hide userform and run subroutine to continue printing
    UserForm1.Hide
    Call Continue_Print(FoldLoc)
End Sub

Sub Continue_Print(Folder As String)
    Worksheets(1).Activate

'define output file name
    Dim PartNo As String, PurchNo As String, InspDate As String, DateRecd As String, OutName As String
    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

'page setup
    Dim ws As Worksheet
    For Each ws In Sheets
        With ws.PageSetup
            .Orientation = xlPortrait
            .PrintArea = "$A$1:$Q$33"
            .Zoom = False
            .FitToPagesTall = False
            .FitToPagesWide = 1
        End With
    'select all visible sheets, don't replace selection
        If ws.Visible Then ws.Select (False)
    Next

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

End Sub

 

[Yongle]

Did you test the code provided and get it working ?

 

[samerickson89]

Did you test the code provided and get it working ?

I just did, and I get the same run-time error. Debug highlights the following line of code:

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=FPath & OutName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

It also goes straight to printing (or trying to print) as soon as a selection is made, without the option to change selection or cancel.

 

[Yongle]

I am struggling to understand what has gone wrong for you because the code is fairly simple and works without any problems on my PC

1. Sub-Folders
I created the following subfolders below ActiveWorkbook.Path
Completed RI Reports\FCAD
Completed RI Reports\PDL

Are those the correct subfolders?

2. Valid Path and Name
Are there any invalid characters in the generated name?

Insert this line immediately below OutName = PartNo & "_" & PurchNo & "_" & dateRecd

Debug.Print FPath & OutName

Look in the Immediate Window to see if the path & file name are valid (show immediate window when in VBA editor with {CTRL} G )
Create a PDF manually with that name just to check that is not the problem

3.

It also goes straight to printing (or trying to print) as soon as a selection is made, without the option to change selection or cance

That is most peculiar because the options appear and when I select PDL the pdf is saved to that subfolder (same for FCAD)

Which version of Excel are you running ?
Are you using the code totally unmodified ?

What does "as soon as a selection is made" mean ?
- other than selecting either PCL or FCAD there are no other selections and the code should immediately print after that

 

[samerickson89]

I am struggling to understand what has gone wrong for you because the code is fairly simple and works without any problems on my PC

1. Sub-Folders
I created the following subfolders below ActiveWorkbook.Path
Completed RI Reports\FCAD
Completed RI Reports\PDL

Are those the correct subfolders?

2. Valid Path and Name
Are there any invalid characters in the generated name?

Insert this line immediately below OutName = PartNo & "_" & PurchNo & "_" & dateRecd

Debug.Print FPath & OutName

Look in the Immediate Window to see if the path & file name are valid (show immediate window when in VBA editor with {CTRL} G )
Create a PDF manually with that name just to check that is not the problem

3.


That is most peculiar because the options appear and when I select PDL the pdf is saved to that subfolder (same for FCAD)

Which version of Excel are you running ?
Are you using the code totally unmodified ?

What does "as soon as a selection is made" mean ?
- other than selecting either PCL or FCAD there are no other selections and the code should immediately print after that

The subfolders will actually be "Completed RI Reports\FCAD Completed RI Reports" and "...\PDL Completed RI Reports" but I changed that as needed. Turns out the problem I was having with the code you posted was that I hadn't created those subfolders in the location I was using to test the code. Once I realized that, your code ran just fine.

When I say "as soon as a selection is made" I mean the PDF is saved as soon as an option button is clicked. Instead, I'd like to wait until an option button is clicked and then the OK button. That way, the user has the chance to change their selection, or click the cancel button, before printing.

 

[samerickson89]

Oh my gosh, I forgot to include OutName at the end of FileName. It works! Thank you so much for the suggestion to add all the code within the user form, I never would've thought to do it that way!

 

[Yongle]

Good
- I will post amendment tomorrow morning (when back at my PC) to allow the user an opportunity to change his mind
- a command button needs adding to the userform
- the option button click events need deleting
- the code needs am bring slightly and placed in command button click

 

[Yongle]

to allow the user to confirm before committing ..

- delete 2 procedures FCADOptionButton_Click & PDLOptionButton_Click

- delete line Unload Me from procedure Print_Report_PDF

- add a Command Button on the userform with this code

Private Sub CommandButton1_Click()
    If FCADOptionButton.Value = True Then
        Print_Report_PDF ("FCAD")
    ElseIf PDLOptionButton.Value = True Then
        Print_Report_PDF ("PDL")
    End If
    Unload Me
End Sub