Userform module drop-down list

2D plans Layout
1

Hi all

I created a userForm in the form of a checklist with CheckBoxes to check, I would like to make it mandatory to check one of the boxes of each line before having access to a combobox grouping the different modules of the macro (each module has a vba code) then I want to add a CommandButton that will launch the chosen module. 

I know it must be a mess for the VBA gods, I'm not a programmer and I don't know if it's possible?

PS: The macro is launched by a drawing software (Solidworks), that's why the checklist is in the userform and not in an excel sheet.

Thanks in advance


test.jpg
2

Hello

Here is an example that seems to me to answer what you want to do...

Good code to you...

Kind regards


macrouserform.swp
3 Likes
3

Hello Roger,

Thank you for your help, I changed my userForm with your code for the checklist that's good, on the other hand in the drop-down list, I would have liked the list of the 4 modules below (see image test.jpg) and depending on the module chosen, launch the module macro.

I hope it's possible. 

Thanks in advance

4

Hello

Try like this:

    For i = 0 To Application.Modules.Count - 1
        MalistBox.AddItem Application.Modules(i).Name         
    Next i

 

1 Like
5

Hello

Thank you I'll look tomorrow

6

Hello

I tested the code by changing Roger's macro, I can't do it

The drop-down list does not display the modules 

 

7

Hello

As the names of your 4 modules never change, you should be able to fill it in as soon as you launch the macro and put something like this:

Private Sub UserForm_Activate()
   MonCombobox.AddItem ("Mon module 1")
   MonCombobox.AddItem ("Mon module 2")
   MonCombobox.AddItem ("Mon module 3")
   MonCombobox.AddItem ("Mon module 4")
End Sub

Of course you replace "MyCombobox" with the name of your control and the "My module ... " by the name of your modules.

Kind regards

2 Likes
8

Hello

And if you really want to list your modules by code then you need to be able to do something like:

Private Sub UserForm_Activate()
    Dim vbProj As VBProject
    Dim vbComp As VBComponent
    Dim i As Integer
    Dim maMacro As String
    maMacro = "MacroUserForm" 'à remplacer par le nom de la macro
    For Each vbProj In Application.VBE.VBProjects
        i = 1
        If vbProj.Name = maMacro Then
            For Each vbComp In vbProj.VBComponents
                If vbProj.VBComponents.Item(i).Type = 1 Then
                    ComboBox1.AddItem vbProj.VBComponents.Item(i).Name
                End If
                i = i + 1
            Next
        End If
    Next
End Sub

Be careful, don't forget to put "Microsoft Visual Basic for Applications Extensibility" in Tools/References.

Kind regards

3 Likes
9

Thank you Roger,

I'll test it during the day

10

After a few modifications, the userform works, it shows me all the modules in the drop-down list.

Small problem see attached file:

1- From the start my drop-down list is accessible with the choice of modules, it would be better if it is grayed out as long as the boxes are not checked

 2- The button does not launch the module chosen from the drop-down list

It's progressing, it's good 

Thank you very much


macrouserform_2.swp
11

Hello

You can for example do as in the attached macro but this is only an example, it's up to you to adapt to your needs.

Kind regards


macrouserform_3.swp
1 Like
12

Hello Roger,

it works ooooooh, you're too strong 

 I had a mistake:( Error 438" object does not handle this property or method)

To make it pass, I removed the . Line Value:         If TypeName(Ctrl) = "CheckBox" And Ctrl.Object.Value = "True" Then No. = No. + 1

I'm so happy

Thank you, thank you, thank you very much for taking your time for me.