0

I'm new at vba and I'm trying to create a form in excel with removable lists with items taken from another excel book which contains the items I need for the list.

At the same time as I want to change the label values dynamically I made a loop For Next which change the value of the label in every turn. I need to say before you see the code that I changed the labels name to start at 0 instead 1 so my 1st label is called label0 and not label1, I made this because I created an array with the names I need for labels.

I tried to do this: 

Private Sub UserForm_Initialize()
    'Declarando variables
    Dim file As Workbook          'variable que contiene el libro con la tabla de los archivos
    Dim var As Long               'variable que para el bucle for
    Dim i As Long                 'variable para el bucle for
    Dim label As Variant

    'Creamos una array que contenga los nombres posibles de label
    label = Array("Dirección", "Empresa", "Area/Planta del suceso")

    'Asignamos los valores para las variables de departamentos y empresas que abrirán los respectivos archivos que se encuentran en la ruta especificada
    Set file = Workbooks.Open("C:\Users\se72497\Desktop\Departamentos.xlsx")

    For var = 0 To 2
        'Asigna el nombre a las etiquetas
        Controls("label" & var).Caption = label(var)

        'Bucle que recorre cada una de las líneas que existen en la tabla de DEPARTAMENTOS y se añaden a la lista desplegable que se especifica
        For i = 2 To file.Sheets("Hoja1").Range("C2").End(xlDown).Row
            direccion.AddItem file.Sheets("Hoja1").Cells(i, 3).Value
        Next i
    Next

    file.Close
End Sub

I'm trying the variable file change every time the loop returns to the For var because when label2.caption is equal to label(2) for example needs another file than when it is label1.caption=label(0) I dont know if create another array with the files path, i suppose I can do that but if anyone know another way please let mw know.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Noelia Sancho Mendez
  • 141
  • 3
  • 15
  • I am voting to Close this question as inappropriate for StackOverflow because this is a general question about how an object functions, e.g., what is appropriate syntax. I could have listed as `Too Broad`, as this asks more than one question, but went with the one that states this may be more appropriate for SuperUser site based on the initial general questions. – Cyril Oct 24 '19 at 14:05
  • 1
    I will now try to touch on, generally, a couple questions... within the `userform` you can use `Me` to refer to the userform. If I want to refer to an object on the userform, i can write `Me.Controls()`, where `Controls()` is a generic listing which can utilize the name of a given control. In several cases, using `Me.Controls()` rather than a shorthand reference to a control is appropriate since there are limitations within userform scripts when there are a significant number of controls on the form. – Cyril Oct 24 '19 at 14:09
  • Hopefully last one... see [Loop Through Files in a Folder Using VBA](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) regarding point 3. – Cyril Oct 24 '19 at 14:11

0 Answers0