Processing multiple command buttons in a userform - best practice

ERIM

New Member
Joined
Sep 30, 2005
Messages
21
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ERIM

New Member
Joined
Sep 30, 2005
Messages
21
My faith with finding answers in the Mr. Excel message board has been shaken by no-one being able to give me any help with the above.

No matter, as I've been able to find the simple solution I was expecting with a Google search. All I needed to do was to use ...

Dim ctl as Control

For Each ctl In Me.Controls
:
:
Next ctl

... loop

All I need to know know is how to dictate what order the elements are presented. I'll try Google again!
 

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254
Hi Erim

Yes its incredible how the use of controls in somehow limited online and the forums. I am new to VBA too. I am glad you found your solution, I still dealing with a form that has about 50 buttons and a huge load of code attached to it. I am sure there has to be a way to reduce it but I have not found it.

Regarding your problem there is a property for buttons "Tag" it might be of some help. I would be worth to check it out.

I am sorry I couldn't help more :)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,287
Messages
5,485,897
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top