How to automatically generate a user form with correctly labelled tickboxes to populate areas of a worksheet

ShogunPatch

Board Regular
Joined
May 8, 2009
Messages
52
I have a large workbook which includes a number of macros already, one of which automatically loops through a number of different values to generate a consolidated report for each of those values.
The available values in question are listed in cells C2:C101 of a sheet called 'Global Settings'. At the moment, so that the macro knows which values to run the report for, I have to go into the 'Global Settings' sheet and put an 'x' against the values I which to process (in cells B2:B101) before running the macro.
That works fine, however:
(a) ideally I would like to be able to hide the 'Global Settings' sheet as it contains other settings I do not wish to be available publicly; and
(b) the values in cells C2:C101 can change on a fairly regular basis (and in fact there are not always 100 of them, sometimes only the first 20 or 30 spaces have values).
What I need is some code which will automatically work out how many values there are (there will never be any spaces between the values), and then create an appropriately sized User Form with a tickbox (apologies if that is not the right terminology) for each available value which, when ticked, will then put an 'x' into the appropriate cells in B2:B101.
So for example if I have just five values (A, B, C, D, E) in cells C2:C6 (i.e. cells C7:C101 are blank), then the code would generate a user form with just five tickboxes automatically labelled A,B,C,D,E. If I then tick A, C and E the code would then populate cells B2, B4 and B6 on sheet 'Global Settings' with an 'x'.
What would be an added nicety would be if in addition to 'OK' (to populate column B) and 'Cancel' buttons (to terminate the macro), there were 'Select All' and 'Clear All' Buttons to make things easier if there are lots of tick boxes.
I hope this all makes some kind of sense and one of my MrExcel brethren might be able to help me out on this one.
Many thanks.

Patch
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,282
Members
418,185
Latest member
snoogz2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top