Create an Excel Workbook That Runs SAS Programs - Exchanging Data Between SAS and Microsoft Excel - page 240

Figure 14.8: A Text Description of the Items on the UserForm, Relative to the Location on
the UserForm.

Label1 - text -- Contains the title, and is not used for anything else .

Label2 - text
“Year”, no other
use.

TextBox1 - Contains
the value for variable
Year linked to an
Excel cell.

Label4 - “Program
Name”, no other use.

TextBox3 - Renamed to
Program_name. Contains the
value for variable
Program_Name linked to an
Excel cell.

Label3 - text
“Month”, no other
use.

TextBox2 - Contains
the value for variable
Month linked to an
Excel cell.

Label5 - “Work
Directory”, no other use.

TextBox4 - Contains the
value for variable
Work_Directory linked to an
Excel cell.

Label6 - text
“Program ID”, no
other use.

ComboBox1 -
Contains the list of
programs installed in
the tool; this is
initialized by VBA
code and linked to
cells in the Excel
workbook.

CommandButton1 -
“Load Parameters”; this
control is linked to a
VBA routine that copies
data from a worksheet to
TextBox5 below.

CommandButton2 - “Run
Program”; this control is
linked to a VBA routine that
executes the VBA code to do
the processing.

Label7 - text
“Unique
Parameters”, no
other use.

TextBox7 - Renamed to Program_directory so
that we can take advantage of the properties of a
TextBox without writing special code to do the
work; we will not make this visible on the GUI
screen.

CheckBox1 - A flag that is
true (when checked) to be
used in the VBA code to
control processing.

TextBox5 - Renamed 'Program_Parms' to take advantage of TextBox properties without special code;
this Excel control (like TextBox3, TextBox6, and TextBox7) points to cells on the JOB_ xx _PARMS
worksheets. The contents of the ControlSource field will be pointed to the current JOB_ xx _Parms
spreadsheet.

CommandButton3
- “Back-up
Parameters”; this
control is linked
to a VBA routine
that copies data
from TextBox5 to
a backup
worksheet.

CommandButton4 -
“Restore Parameters”;
this control is linked
to a VBA routine that
copies data from a
backup worksheet to
TextBox5

TextBox6 - Renamed to
Program_Number so that
we can take advantage of
the properties of a
TextBox without writing
special code to do the
work. We will not make
this visible on the GUI
screen.

CommandButton5 - “Save
and Exit” this control is
linked to a VBA routine that
saves the contents of the
workbook and exits Excel.
Because VBA runs a macro
there are always workbook
changes.

14.3.6 Storing Control Information in the Excel Workbook Worksheets
Now that we know what the menu looks like (see Figure 14.7) and have some idea about how to put it to
work, let's define some worksheets to use for the data storage of parameters and control information for the
tool. For this version of the tool, we will need six worksheets in our workbook. The Excel sheet names
(Sheet1, Sheet2, etc.) do not need to be in order because we will be using the user-assigned names of the
sheets for all of the references to the sheets. The list below describes their functions:

1. Sheet1 - “Control_Info”

Storage for tool information like variable values.

2. Sheet2 - “Common_Parm_Text”

Storage for text output by the tool to control jobs.

3. Sheet3 - “Job_01_Parms”

Storage for Job 01 parameters.

4. Sheet4 - “Job_01_Backup”

Backup Storage for Job 01 parameters.

5. Sheet5 - “Job_02_Parms”

Storage for Job 02 parameters.

6. Sheet6 - “Job_02_Backup”

Backup Storage for Job 01 parameters.