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.
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
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.)
‘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.")
Set wbDest = Nothing
Set wbDestcopy = Nothing
MsgBox ("Program done!")
Application.EnableEvents = True