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


Board Regular
May 8, 2009
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.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...