Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and a
check box for each one.

this is for my users to select which of there sheets they want to run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J

A combobox is the usual way of doing this. And a whole lot easier that
creating check boxes on the fly.

--
Jim
"JJ" wrote in message
oups.com...
| Ok. Heres what i want to do.
|
| I have a variable number of sheets with variable names.
|
| I want to at runtime produce a form which lists all the sheets and a
| check box for each one.
|
| this is for my users to select which of there sheets they want to run
| the macro on.
|
| Can any one point me in the right direction ?
|
| Or is there a better way to do this?
|
| Thanks in advance!
|
| J
|

Problem with creating a checkbox on the fly is that you also have to
generate some code to process them on the fly.

Far better IMO to add the max number of checkboxes you will need and hide
them, unhiding as required, or to use another method, such as dropping the
sheet names in a listbox or a combobox, and process the selected sheet.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JJ" wrote in message
oups.com...
Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and a
check box for each one.

this is for my users to select which of there sheets they want to run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J

Rather than adding controls consider a listbox with the properties

ListStyle = fmListStyleOption
MultiSelect = fmMultiSelectMulti

Cheers
Andy

JJ wrote:
Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and a
check box for each one.

this is for my users to select which of there sheets they want to run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

JJ, I've used this approach to make dynamic checkboxes
Dim ctlCheckBox As Control
For cnt = 1 to 10
Set ctlCheckBox = UserForm1.Controls.Add _
("Forms.Checkbox.1", "ctlCheckBox" & cnt)
ctlCheckBox.Caption = ActiveWorkbook.Worksheets(cnt).Name
Next

then call back the info like this:
For cnt = 1 to 10
MsgBox UserForm1.Controls("ctlCheckBox" & cnt).Value
Next
--
Charles Chickering

"A good example is twice the value of good advice."


"Andy Pope" wrote:

Rather than adding controls consider a listbox with the properties

ListStyle = fmListStyleOption
MultiSelect = fmMultiSelectMulti

Cheers
Andy

JJ wrote:
Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and a
check box for each one.

this is for my users to select which of there sheets they want to run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

and code them like how?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles Chickering" wrote in
message ...
JJ, I've used this approach to make dynamic checkboxes
Dim ctlCheckBox As Control
For cnt = 1 to 10
Set ctlCheckBox = UserForm1.Controls.Add _
("Forms.Checkbox.1", "ctlCheckBox" & cnt)
ctlCheckBox.Caption = ActiveWorkbook.Worksheets(cnt).Name
Next

then call back the info like this:
For cnt = 1 to 10
MsgBox UserForm1.Controls("ctlCheckBox" & cnt).Value
Next
--
Charles Chickering

"A good example is twice the value of good advice."


"Andy Pope" wrote:

Rather than adding controls consider a listbox with the properties

ListStyle = fmListStyleOption
MultiSelect = fmMultiSelectMulti

Cheers
Andy

JJ wrote:
Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and a
check box for each one.

this is for my users to select which of there sheets they want to run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

In this case the user doesn't need code behind them. He just needs to use a
command button to launch the macro and have the macro check the state of the
checkboxes. Normally I would make individual checkboxes and just hide them
but there could be a couple hundred sheets and placing all those checkboxes
would suck. Anyhow if you want to put code behind them can't you just use
j-walk's code for coding multiple buttons?
(http://j-walk.com/ss/excel/tips/tip44.htm)
--
Charles Chickering

"A good example is twice the value of good advice."


"Bob Phillips" wrote:

and code them like how?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles Chickering" wrote in
message ...
JJ, I've used this approach to make dynamic checkboxes
Dim ctlCheckBox As Control
For cnt = 1 to 10
Set ctlCheckBox = UserForm1.Controls.Add _
("Forms.Checkbox.1", "ctlCheckBox" & cnt)
ctlCheckBox.Caption = ActiveWorkbook.Worksheets(cnt).Name
Next

then call back the info like this:
For cnt = 1 to 10
MsgBox UserForm1.Controls("ctlCheckBox" & cnt).Value
Next
--
Charles Chickering

"A good example is twice the value of good advice."


"Andy Pope" wrote:

Rather than adding controls consider a listbox with the properties

ListStyle = fmListStyleOption
MultiSelect = fmMultiSelectMulti

Cheers
Andy

JJ wrote:
Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and a
check box for each one.

this is for my users to select which of there sheets they want to run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Yes you can.

--
Regards,
Tom Ogilvy

"Charles Chickering" wrote in
message ...
In this case the user doesn't need code behind them. He just needs to use
a
command button to launch the macro and have the macro check the state of
the
checkboxes. Normally I would make individual checkboxes and just hide them
but there could be a couple hundred sheets and placing all those
checkboxes
would suck. Anyhow if you want to put code behind them can't you just use
j-walk's code for coding multiple buttons?
(http://j-walk.com/ss/excel/tips/tip44.htm)
--
Charles Chickering

"A good example is twice the value of good advice."


"Bob Phillips" wrote:

and code them like how?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Charles Chickering" wrote
in
message ...
JJ, I've used this approach to make dynamic checkboxes
Dim ctlCheckBox As Control
For cnt = 1 to 10
Set ctlCheckBox = UserForm1.Controls.Add _
("Forms.Checkbox.1", "ctlCheckBox" & cnt)
ctlCheckBox.Caption = ActiveWorkbook.Worksheets(cnt).Name
Next

then call back the info like this:
For cnt = 1 to 10
MsgBox UserForm1.Controls("ctlCheckBox" & cnt).Value
Next
--
Charles Chickering

"A good example is twice the value of good advice."


"Andy Pope" wrote:

Rather than adding controls consider a listbox with the properties

ListStyle = fmListStyleOption
MultiSelect = fmMultiSelectMulti

Cheers
Andy

JJ wrote:
Ok. Heres what i want to do.

I have a variable number of sheets with variable names.

I want to at runtime produce a form which lists all the sheets and
a
check box for each one.

this is for my users to select which of there sheets they want to
run
the macro on.

Can any one point me in the right direction ?

Or is there a better way to do this?

Thanks in advance!

J


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info