Repeating code

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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.
 
Upvote 0
Something like this:- in your "Userfrom modules"
Code:
Private Sub UserForm_Initialize()
Call Lb(ListBox1)
End Sub

and this in you ordinary module:-
Code:
Sub Lb(List_Obj)
List_Obj.List = Array(1, 2, 3, 4, 5)
End Sub
Regrds Mick
 
Upvote 0
Something like this:- in your "Userfrom modules"
Code:
Private Sub UserForm_Initialize()
Call Lb(ListBox1)
End Sub

and this in you ordinary module:-
Code:
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...

Code:
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...

Code:
 Private Sub UserForm_Initialize()
  Call LB(ListBox1, 5)
  Call LB(ListBox2, 9)
 End Sub
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top