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

Figure 14.21: VBA Code Counts Number of Valid ComboBox Entries and Loads the VBA
Object.
' count the number of job names in the list for ComboBox1
Max = 0
Do Until test = "###"
idx = 4 + Max
i = Trim(Str(idx))
test = Worksheets("Control_info").Range("C" + i).Value 'skip first space
Max = Max + 1
If test = "" And idx > 4 Then test = "###"
Loop
' Load the job names into ComboBox1
idx = 4 ' Cell C4 - the first ComboBox1 value
Do Until idx > Max + 2
i = Trim(Str(idx))
UserForm1.ComboBox1.AddItem(Worksheets("Control_info").Range("c"+i).Value)
idx = idx + 1
Loop
14.4.1.4 Loading Data to the UserForm from a Selected Worksheet
One of the most useful things about the VBA code that supports this workbook is that the VBA
“ControlSource” fields of the workbook can be modified at run time. That means that the workbook has the
ability to change where the ControlSource value points, and therefore what is displayed on the UserForm.
So the ability to store several sets of SAS program variables allows the user (you) to select and store code
to be executed into different working directories. The figures below (14.22 to 14.24) show the actions.
Figure 14.22: UserForm1 Before Any Action and After Initialization.