Specific Treatment for Groups of Objects inserted in the VBA Toolbox (Excel)

Question

Due to repetitive actions with groups of objects that I create to work as if they were a single object, I grouped some of these objects (right-clicking and selecting group...), and dragged into the toolbox of VBA.

To my surprise it worked, an editable icon appeared that allows me to recreate at a single time each group of objects that I need to use repeatedly in different parts of the project or even in other projects.

However, there are some drawbacks to working in this way, I list some of them that are important to me:

1. The created "group" is not identified as a new object (evidently), so one must work with each object of a group as if it were an object placed individually. continuing these objects grouped or not (in fact they are simply new objects placed individually in the form).

2. Because they are distinct objects, the same sequential naming logic of each object is followed, for example, each group that has a single Label inserted in the form generates the Label1, Label2, Label3, etc. So if between a group and another is placed in the form some objects of the same type as some that exist in these groups, a Label that would be the Label7 the inclusion of the group shall be Label9, for two Labels were placed before this group. Therefore, it is necessary to check "object-to-object" of the group, which name he received to deal with in the code.

I show below a purely didactic example.

1. In this form are present the objects: Frame1, Image1, Label1, commandButton1 and commandButton2.

2. These objects were grouped (right click):

3. The resulting group was dragged into the tool box (see the icon of planet earth, modified to make clear the addition of group):

4. In sequence were created a group (to the right of the first), a normal label (below them) and a group (below the label):

The name of the new objects became like this:

• Frame2, Image2, Label2, commandButton3 and commandButton4 for the second group

• Label3 for the Label inserted in the form

• Frame3, Image3, Label4, commandButton5 and commandButton6 to the third group

Note that the Label of the third group 4 and does not follow the others of the group, which prevents a rigid treatment in the code.

QUESTION:

There is a way to do this by getting different names for the objects or generating properties for this tool box group?

For example, if the name of each object in the toolbox (by nonsense) could receive any name, each new object would create these objects with the expected numbering (the Label of the group could call Caption, generating Caption 1, Caption 2, Caption 3, etc. not interfering with the appointment of Labels who are not from one of these groups).

One way that would also solve it is if for that group they were associated estates, which would allow, for example, registering a "id" for each group created, or even some reference text.

Can do?

I have a certain knowledge and little experience in languages aimed at events or objects, so I believe I have a more appropriate way of dealing with this case.

Answer 1

The solution presented below (code and form with the test result) was made using "Controls" to treat the objects of each group.

Private Sub CommandButtonTeste_Click()

    Dim Bandeira As Variant

    'Pega o ícone da bandeira desejada
    Set Bandeira = LoadPicture("F:\Teste\Japao.ico")

    'Teste #1: Altera todos os dados do primeiro grupo
    GrupoDeObjetos 1, True, True, _
                   "Japão", _
                   Bandeira, _
                   "Arroz", _
                   "Perda", _

                   "Troca"

    'Teste #2: Torna os objetos do Segundo grupo invisíveis
    GrupoDeObjetos 2, False, True

    'Teste #3: Desabilita os objetos do terceiro grupo
    GrupoDeObjetos 3, True, False

    'Teste #4: Força um erro, pois o grupo 4 não existe
    GrupoDeObjetos 4, True, True

End Sub


Public Sub GrupoDeObjetos( _
           ByVal IndiceDoGrupo As Integer, _
           ByVal GrupoVisivel As Boolean, _
           ByVal GrupoHabilitado As Boolean, _
  Optional ByVal LegendaDoFrame As String = "", _
  Optional ByVal Imagem As Variant = "", _
  Optional ByVal LegendaDaImagem As String = "", _
  Optional ByVal LegendaDoBotao1 As String = "", _
  Optional ByVal LegendaDoBotao2 As String = "")

    Dim i As Integer

    'Verifica o índice
    If IndiceDoGrupo < 1 Or IndiceDoGrupo > 3 Then

        Beep

        MsgBox "Índice do Grupo de Objetos inválido!" & _
                Chr(13) & Chr(13) & _
               "Índice: " & IndiceDoGrupo

        Exit Sub

    End If

    'Tratamento dos objetos do grupo
    ' Atualiza o índice do grupo com o valor
    ' do índice dos controles no formulário
    Select Case IndiceDoGrupo

        Case 1

            'Índice do Frame1 no formulário
            IndiceDoGrupo = 0

        Case 2

            'Índice do Frame2 no formulário
            IndiceDoGrupo = 5

        Case 3

            'Índice do Frame3 no formulário
            IndiceDoGrupo = 11

    End Select

    'Atualiza dados do objeto se os dados foram passados
    If LegendaDoFrame <> "" Then Me.Controls(IndiceDoGrupo).Caption = LegendaDoFrame
    If Imagem <> "" Then Me.Controls(IndiceDoGrupo + 1).Picture = Imagem
    If LegendaDaImagem <> "" Then Me.Controls(IndiceDoGrupo + 2).Caption = LegendaDaImagem
    If LegendaDoBotao1 <> "" Then Me.Controls(IndiceDoGrupo + 3).Caption = LegendaDoBotao1
    If LegendaDoBotao2 <> "" Then Me.Controls(IndiceDoGrupo + 4).Caption = LegendaDoBotao2

    'Trata os objeto do grupo (Visible/Enabled)
    For i = 0 To 4

        'Grupo visível ou não
        Me.Controls(IndiceDoGrupo + i).Visible = GrupoVisivel

        'Grupo habilitado ou não
        Me.Controls(IndiceDoGrupo + i).Enabled = GrupoHabilitado

    Next i

End Sub

Note on the code that the Indicedogrupo is a single variable treated in two different ways:

1. When calling the routine this index points to the desired group, in this case the groups of values 1, 2 and 3.

2. In the code, depending on the group chosen to handle, this variable receives the index value of the Frame of the group, in this case 0, 5 and 11 respectively for the groups 1, 2 and 3.

Notice that each group has 5 objects, such as the group 2 was created immediately after the group 1, the content of your Frame is the 5. (Group 1 objects have the indices: 0, 1, 2, 3 and 4)

The difference in the group 3 was 11 and not 10, is that the Label3 was inserted in the form before that group, therefore the Label3 got the index 10.

IMPORTANT!

For this code to work properly, when creating your group of objects insert each group object in the sequence you want to work, and do not insert any object other than this group until it is finished.

Group him, put in the toolbox and ready, just treat the content of each new object you put in the form (in this case starting from 1).

This is the test results:

Note that there are four tests, but I did not present the dialog box generated by the purposeful error of test #4 (it was closed before the Printscreen), to make it easy to identify in the image above what happened:

1. All data from group 1 were changed, including the image of the flag

2. The group 2 became invisible

3. The group 3 was disabled, however, although the image is also disabled, it is not "faded" (I did not do this effect in the example)

4. The error message caused by calling a fourth group that does not exist in the form generated the appropriate error message (I did not submit)

COMPLEMENT

If the Label3 is deleted from the form, the index of the third object will change, to solve this one can create a routine to fetch the index of the first object of the group when initializing the form, for example.

Simple suggestion of routine call:

    Dim Indice1, Indice2, Indice3 as Integer

    Indice1 = IndiceDoObjeto(UserForm1, "Frame1")
    Indice2 = IndiceDoObjeto(UserForm1, "Frame2")
    Indice3 = IndiceDoObjeto(UserForm1, "Frame3")

If the object is not located in the form I would indicate inform by means of a message and exit the program immediately after the message, because it does not make sense to continue having this type of error.

This way no errors will occur when manipulating objects between groups.

ORGANISATION IN THE ORDER OF THE CONTENTS OF OBJECTS IN THE GROUP

When working with groups of this type (much more extensive), I came across a problem that can occur in two ways:

1. If the objects of a group were not created in the desired or intuitive order (from the top left to the bottom right), the index of each object in the group will evidently not follow this order, which makes it difficult - at least - the initial treatment of these in the code.

2. If you change the position or order of the object in the form, with "Bring Forward" or "Send Back", for example, the contents of the objects in the form are changed, then it may occur that a group object is "replaced" by another in the treatment to group objects or even all objects have their contents changed (in case of bringing forward an object that was created before the group).

As a solution, I did a test, I started to sort the objects as desired, so that their indexes were in the order "intuitive". To do so, I did this in a new form, so there is no problem with other objects that could interfere in this process.

So, without having to "regroup", because they remained "grouped" even with the resources of type "Forward" and "Retreat", for example, at the end I put it in the toolbox.

At this time, this new group was ordered as desired, that is, when inserting it in any form, the order of the indexes of its objects keep the order in which I changed in the group.

SOLICITING

Where there are other ways of doing so, including through Object classes with properties to pass this data, if you can post here an example I would be very grateful!