Repeating code

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,621
Office Version
  1. 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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,173
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

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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,893
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,447
Members
430,549
Latest member
jayjay2022

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
Top