Repeating code

[tiredofit]

I have this code in two separate userforms but wondered if there is a way I can only write it once somewhere and refer to it.

Dim MyArray() As Variant
    
    MyArray = Array(1, 2, 3, 4, 5)
    
    Me.LsitBox1.List = MyArray
    
    Erase MyArray)

 

[ranman256]

Put the code in a module (not the form), but call it from the form

When you record a macro , it will create a module for you.

 

[tiredofit]

Put the code in a module (not the form), but call it from the form

When you record a macro , it will create a module for you.

I suppose I would have to have a parameter in the module to determine which userforms it's coming from.

 

[MickG]

Something like this:- in your "Userfrom modules"

Private Sub UserForm_Initialize()
Call Lb(ListBox1)
End Sub

and this in you ordinary module:-

Sub Lb(List_Obj)
List_Obj.List = Array(1, 2, 3, 4, 5)
End Sub

Regrds Mick

 

[Rick Rothstein]

ADVERTISEMENT

Something like this:- in your "Userfrom modules"

Private Sub UserForm_Initialize()
Call Lb(ListBox1)
End Sub

and this in you ordinary module:-

Sub Lb(List_Obj)
List_Obj.List = Array(1, 2, 3, 4, 5)
End Sub

Regrds Mick

I don't know if the OP would need the flexibility to change the number to "count to" for multiple ListBoxes, but if so,your approach can be made flexible to allow for that. Just change the LB subroutine to this...

Sub LB(List_Obj As ComboBox, CountTo As Long)
  List_Obj.List = Evaluate("TRANSPOSE(ROW(1:" & CountTo & "))")
End Sub

And then call it like this...

 Private Sub UserForm_Initialize()
  Call LB(ListBox1, 5)
  Call LB(ListBox2, 9)
 End Sub

 

[tiredofit]

Thanks to all.

 

[MickG]

Nice addition, Rick.