I am currently attempting to enhance a VBA macro that produces multiple sheets of data from an input data file which are used for task management purposes. Because so many sheets are generated, my users wish to have the option to selectively print the contents based on a number of different selection criteria. i.e. all the sheets containing a particular manager's name; a specific sheet by Task Number; all sheets with start dates and end dates between selected dates etc.
I have designed a form which is loaded on request once the sheets have been created which I have populated with labels, option buttons, command buttons etc. so that the user will be initially presented with three primary selection options, then six secondary selection options. Depending on the combination of option buttons selected, I will then present the user with a series of command buttons which will contain the key data for the particular selection. They will then tick the items required and I'll be able to identify the sheets that are to be printed.
Because the number of command buttons required could be as many as two hundred, I have created 200 commandbuttons. Each has to have a unique name, so I have named them CB001; CB002 ... CB200.
In my VBA code which is behind the form, I have already created 200 x 'Private Sub' CBnnn_Click() to allow identification of which command boxes have been selected in which I set an variable which I will use during the eventual print subroutine.
However, during my userform_initialize routine I wish to hide all the command buttons and reveal as many as required when the time comes. Thus I have code which sets the CBnnn.visible = false.
My question is: rather than have 200 such commands, is there a way of setting each command button in a loop by way of a single line of code. I'm sure that I will be able to use a 'for each commandbutton in x n.visible = false' contstruct but I can't find out what to use for 'x' and 'n'.
Can anyone point me to a solution (or basic userform processing for beginners).
Many thanks in anticipation.
I have designed a form which is loaded on request once the sheets have been created which I have populated with labels, option buttons, command buttons etc. so that the user will be initially presented with three primary selection options, then six secondary selection options. Depending on the combination of option buttons selected, I will then present the user with a series of command buttons which will contain the key data for the particular selection. They will then tick the items required and I'll be able to identify the sheets that are to be printed.
Because the number of command buttons required could be as many as two hundred, I have created 200 commandbuttons. Each has to have a unique name, so I have named them CB001; CB002 ... CB200.
In my VBA code which is behind the form, I have already created 200 x 'Private Sub' CBnnn_Click() to allow identification of which command boxes have been selected in which I set an variable which I will use during the eventual print subroutine.
However, during my userform_initialize routine I wish to hide all the command buttons and reveal as many as required when the time comes. Thus I have code which sets the CBnnn.visible = false.
My question is: rather than have 200 such commands, is there a way of setting each command button in a loop by way of a single line of code. I'm sure that I will be able to use a 'for each commandbutton in x n.visible = false' contstruct but I can't find out what to use for 'x' and 'n'.
Can anyone point me to a solution (or basic userform processing for beginners).
Many thanks in anticipation.
