Control Array Help...
 

Here is what my objective is (trying to do it in VBA/Excel):

Based on a user's selection, a variable number of options show up.
These options are drawn from a biger list comprising hundreds of
options. For example

Selection: A
Options: 1, 7 and 32

Selection: B
Options 7, 96

This part of the code is simple.

Now the user should have the ability to select (checkbox or some other
control) one/all of the options being displayed. Once the user has
made her selection, some operation is performed at the backend only
for the options selected (e.g. timeVerified for the selected option
is updated in another spreadsheet etc)

I am struggling with the functionality to set up a variable number of
checkboxes that show up based on the initial selection by the user.
Additionally, how can the VALUE in of the selected option be passed
(e.g. 7, 96 etc) so that the underlying program knows what to
change.

Any help/sample code will be appreciated.

Control Array Help...
 

What you could do is have one option per row, and hide those rows. When a
selection is made unhide the relevant rows.

Then use the worksheet selectionchange event to trap clicking in say column
A of those rows, adding a letter 'a', with a font of Marlett if clicked,
clearing it if clicked again (remember to clear all when first unhding).
Then you can simply test which rows have an 'a' in them, and get the row
number.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"excel_slave - ExcelForums.com" wrote
in message ...
Here is what my objective is (trying to do it in VBA/Excel):

Based on a user's selection, a variable number of options show up.
These options are drawn from a biger list comprising hundreds of
options. For example

Selection: A
Options: 1, 7 and 32

Selection: B
Options 7, 96

This part of the code is simple.

Now the user should have the ability to select (checkbox or some other
control) one/all of the options being displayed. Once the user has
made her selection, some operation is performed at the backend only
for the options selected (e.g. timeVerified for the selected option
is updated in another spreadsheet etc)

I am struggling with the functionality to set up a variable number of
checkboxes that show up based on the initial selection by the user.
Additionally, how can the VALUE in of the selected option be passed
(e.g. 7, 96 etc) so that the underlying program knows what to
change.

Any help/sample code will be appreciated.

Control Array Help...
 

An easier option than multiple checkboxes would be a multiselect listbox
(from the controls toolbox), and if you use the option
ListStyle=fmListStyleOption the list will show with "checkboxes" beside each
item so it looks and acts similar to checkboxes.

You can build your list in various ways:
1) with the .Clear (to erase the old list) and .AddItem methods:
ListBox1.Clear
ListBox1.AddItem(Option1) 'Option 1 is how you want that item to read in the
list
ListBox1.AddItem(Option2)
etc...
2) by populating a range in your worksheet with the options and the setting
the .ListFillRange:
ListBox1.ListFillRange = "A1:A3" ' uses text in cells A1, A2, A3 as your
list items
3) by setting the .List property - see Help for details.

Then, after the user has made selections from the list, you can then loop
through the list to see what is selected:
With .ListBox1
For i = 0 to .ListCount - 1
If .Selected(i) Then....
Next i
(note that the list index begins at zero, so to see if the first item is
selected you use .Selected(0))
--
- K Dales


"excel_slave - ExcelForums.com" wrote:

Here is what my objective is (trying to do it in VBA/Excel):

Based on a user's selection, a variable number of options show up.
These options are drawn from a biger list comprising hundreds of
options. For example

Selection: A
Options: 1, 7 and 32

Selection: B
Options 7, 96

This part of the code is simple.

Now the user should have the ability to select (checkbox or some other
control) one/all of the options being displayed. Once the user has
made her selection, some operation is performed at the backend only
for the options selected (e.g. timeVerified for the selected option
is updated in another spreadsheet etc)

I am struggling with the functionality to set up a variable number of
checkboxes that show up based on the initial selection by the user.
Additionally, how can the VALUE in of the selected option be passed
(e.g. 7, 96 etc) so that the underlying program knows what to
change.

Any help/sample code will be appreciated.

Control Array Help...
 

Almost forgot: To read the value of the selected item stored in the list
(after determining that ListBox1.Selected(i) is true), use ListBox1.List(i)
--
- K Dales


"excel_slave - ExcelForums.com" wrote:

Here is what my objective is (trying to do it in VBA/Excel):

Based on a user's selection, a variable number of options show up.
These options are drawn from a biger list comprising hundreds of
options. For example

Selection: A
Options: 1, 7 and 32

Selection: B
Options 7, 96

This part of the code is simple.

Now the user should have the ability to select (checkbox or some other
control) one/all of the options being displayed. Once the user has
made her selection, some operation is performed at the backend only
for the options selected (e.g. timeVerified for the selected option
is updated in another spreadsheet etc)

I am struggling with the functionality to set up a variable number of
checkboxes that show up based on the initial selection by the user.
Additionally, how can the VALUE in of the selected option be passed
(e.g. 7, 96 etc) so that the underlying program knows what to
change.

Any help/sample code will be appreciated.