Macro to write code

[aftamath77]

My code:

Private Sub UserForm_Initialize()

'populate the shell od dropdown list
With ComboBox1
.ListRows = 15
.AddItem ("3.500")
.AddItem ("4.500")
.AddItem ("5.563")
.AddItem ("6.625")
.AddItem ("8.625")
.AddItem ("10.750")
.AddItem ("12.750")
.AddItem ("14.000")
.AddItem ("16.000")
.AddItem ("18.000")
End With

End Sub

I would like to build a userform that would automate writing code. If a
user has a new size that needs to be permanently added to the list, I would
like the code to insert the new line under this sub.

Example: The New size is 20.5. After running the userform, the code will
look like this:

....
....
.AddItem ("16.000")
.AddItem ("18.000")
.AddItem ("20.500")
End With

End Sub

 

[Marcel]

It is possible to have the list you want in your combobox be stored in
a worksheet?

Assuming the list is in A2:Axx, you could use

'storing new value the user has put in TextBox1
Dim intLastRow as Integer
intLastRow = Worksheets("Sheet1").Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'returns the number of used rows in Sheet1
Worksheets("Sheet1").Range("A1").Offset(intLastRow,0).Value =
TextBox1.Value

'populating the list
Dim intLastRow as Integer
Dim i as Integer
intLastRow = Worksheets("Sheet1").Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'returns the number of used rows in Sheet1Dim i as Integer
With ComboBox1
For i = 1 to intLastRow
ComboBox1.AddItem
Worksheets("Sheet1").Range("A1").Offset(intLastRow,0).Value
Next i
End With

Hope you understand what I mean, good luck!

 

[Per Jessen]

Hi

I would rather use the "RowSource" statement. With all sizes listed in
A1 and down, new sizes are added at the bottom of the list, and the
combobox is populated like this:

Private Sub UserForm_Initialize()
Me.ComboBox1.ListRows = 10
Me.ComboBox1.RowSource = Range("A1", Range("A1").End(xlDown)).Address
End Sub

Regards,
Per

 

[Marcel]

Sounds way more efficient than what I came up with, thanks Per!