Using Excel’s Userforms

I keep learning new things.

Lately, my boss wanted an easy way of "printing" her budget file as an Excel file but without formulas to send to our customers. Previously, she sent the budget file as a PDF but you know you can't use the information in Excel unless you have the professional version of Acrobat to convert the file from PDF or you key in the data into your own file. She needed a quick easy way to get rid of the formulas because there are maybe about 20 tabs in her budget file. So she asked me to program a version that she could keep as her personal workbook.

I decided to try to give her choices: she could "print" the entire file or choose certain tabs. I figured that there would be times that she needed to send only certain portions of the file.

So I went Googling the internet and came upon the concept of userforms. Below is a version designed to reside in a personal workbook and the how-to source came from Ken Purls (this time I remember to note the name!)

Below is a sample set of macros that I used to create the "printing" process. There are three parts: a module with a general declarations of variables, a module that kicks off the userform, and a userform module itself that is composed of a couple of separate macros.

Note: I can't due indentations in WordPress - at least I don't know how - so the If / then sequences don't have the indentations for easy identifications of beginning to end.

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

Module 1: General declarations of variables

I'm not sure if this is needed (I can't remember anymore) but here is what I have:

‘General declarations in separate module – Global variables

Public wbDest As Workbook        'the budget files
Public wbSource As Workbook      'the file with choices being offered

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

Module 2: Macro that starts the printing process with a userform

The following macro first checks if the file is unprotected and if it is, then starts the printing process by first offering a selection of tabs to print. (By printing, I mean to convert the formulas into values so that the file can be sent to clients without the formulas.)

Sub Print_Budget_For_Owner()
‘Found the general process from Ken Purls on the internet

‘This is the macro that you first call
'You have to start from file with the budget

Set wbDest = ActiveWorkbook
Application.EnableEvents = False

‘First check to see if the file has been password unprotected. If yes, then call the userform
Dim vbResult As VbMsgBoxResult
vbResult = MsgBox("Have you unlocked the file? ", vbYesNo)

If vbResult = vbYes Then
m2_UserForm_PrintChoice.Show            'Here is where we call the userform called m2_UserForm_PrintChoice
ElseIf vbResult = vbNo Then
MsgBox ("Then before running this printing macro, you must unlock the file.")
Exit Sub
End If

'Empty everything
Set wbDest = Nothing
Set wbDestcopy = Nothing

MsgBox ("Program done!")
Application.EnableEvents = True

End Sub

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

Userform 1 - The userform of choices and the associated printing macros

There are actually 2 pieces to this: the creation of the userform itself, which is shown on the right and the codes that runs the userform and performs the printing. As far as I know, the form that you see to the right has to be manually created - at least that is how I did it. To do that, you have to first insert the userform, rather than the usual module, in the VBA editor. The Insert option is found in the menu bar at the top. Then, in the VBAProject section on the left hand side, find the new userform and right click on it. You should see an option called View Object. After you click on that, a toolbox called Controls should appear and give you various options to use for creating your userform. I used the labels and checkbox. In the Properties section on the lower left, you will see the name of the label or checkbox. You can actually change the name to suit your purpose.

 

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

Userform 2 - The coding of the userform

After you've created the visual userform, and this could take a while, you are now ready to insert the code. In my userform, I have an "Okay" button and a "Cancel" button. Each button will have a code. The "Cancel" button will, of course, stop the macro. The "Okay" button will usually lead to a series of macros that does what was chosen on the userform. In my case, I had 23 choices so I had to create 23 separate macros. By the way, the checkbox form allows one to make multiple choices.

In some kind of userforms, you might need to "initialize the form". The "listbox" form seems to require initialization. I have an example of the initialization macro included below but it is not used in my "printing the budget file" macro.

Initialization macro
Private Sub UserForm_Initialize()
‘Sometimes you need to “initialize the Userform” such as fill in the options.
‘For the printing, this is not necessary. It would be necessary if you used list options.

'UnitName.Clear
'Fill UnitName with list choices

'With UnitName
    '.AddItem "UnitA"
    '.AddItem "UnitB"
    '.AddItem "UnitC"
    '.AddItem "UnitD"
'End With

End Sub

Cancel button macro
Private Sub cmdCancel_Click()
‘This macro is tied to the cancel button on the userform
'Unload the userform
Unload Me
'Alternative is probably Unload m2_UserForm_PrintChoice
End Sub

Okay button macro
Private Sub cmdOkay_Click()
‘This macro starts the printing process if you click on the Okay button on the userform

‘Other macros called by this macro
CheckBox1_PrintAll
CheckBox23_PrintAll_GS
CheckBox6_PrintAll
Checkbox2_PrintBudget
Checkbox5_PrintGSBudget
CheckBox3_PrintNarrative
CheckBox24_PrintTitleInput
CheckBox22_PrintAssumptions
CheckBox7_PrintRevenueWksheet
CheckBox8_PrintConcession
CheckBox9_PrintPayroll
CheckBox10_PrintUtilities
CheckBox11_PrintMarketing
CheckBox12_PrintContract
CheckBox13_PrintPayrollBenefits
CheckBox14_PrintMakeReady
CheckBox15_PrintRepairMaint
CheckBox16_PrintGenAdmin
CheckBox17_PrintRevenue
CheckBox18_PrintCapNReno
CheckBox19_PrintTaxNIns
CheckBox20_PrintNonOp
CheckBox21_PrintLoan

'Unload the userform
Unload Me
'Alternative is probably Unload m2_UserForm_PrintChoice

End Sub

Example of a printing macro called by the Okay button macro
Private Sub Checkbox2_PrintBudget()
If CheckBox2_BudgetReport = True Then
'Make a copy of sheet
wbDest.Sheets(Array("Budget Report", "Payroll Supplemental Rpt", "Revenue Wksheet", "Concession Wksheet")).Select
wbDest.Sheets(Array("Budget Report", "Payroll Supplemental Rpt", "Revenue Wksheet", "Concession Wksheet")).Copy
Set wbDestcopy = ActiveWorkbook

'Replace formulas with values/text and removes link back to mother file (which is the budget)
wbDestcopy.Sheets(Array("Budget Report", "Payroll Supplemental Rpt", "Revenue Wksheet", "Concession Wksheet")).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Take out formatting like borders
wbDestcopy.Sheets("Budget Report").Select
Columns("AD:BE").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

'Clear certain columns
Range("A:A").ClearContents
Range("AD:BE").ClearContents

'Reorganize order of tabs
Sheets(Array("Budget Report", "Payroll Supplemental Rpt")).Select
Sheets(Array("Budget Report", "Payroll Supplemental Rpt")).Move Before:=Sheets("Revenue Wksheet"

End If

End Sub

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]