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
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
