Improve VBA Macro

[tmccar]

I have written 2 macros in VBA which prompt the user to select a value from a "combo box", and based on that value, it generates some Autocad drawings.
It works OK, but it's not ideal because:

1. The Combobox list values are taken from an Excel file, and the For... loop populates the list each time the macro is run. It's probably not the most efficient way to do this. Would there be a better way to set it up?

2. The list is not displayed until the user selects the scroll bar on the ComboBox. It's blank at first. How can I get the list to appear at the start?



Private Sub UserForm_Initialize()

Dim ro As Integer, m As Variant, sh As Excel.Worksheet, vtag As String, pvtag(100) As String
Dim xlbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlbook = GetObject("C:\07509\LB_RACKTMC.xlsx")
Set xlSheet = xlbook.Sheets("LB RACK")
'Set src = xlSheet.Range("BGind").CurrentRegion.SpecialCells(xlCellTypeVisible)

Set sh = xlbook.Sheets.Add
Set xlapp = xlbook.Parent

Set xlbook = GetObject("C:\07509\LB_RACKTMC.xlsx")
Set xlSheet = xlbook.Sheets("LB RACK")
Set src = xlSheet.Range("bgind")

src.Copy sh.Range("a1")
m = sh.Range("a1").CurrentRegion
With ComboBox1

For ro = LBound(m) + 1 To UBound(m)

.AddItem sh.Cells(ro, 1)
Next ro

End With

End Sub

[/URL][/IMG]

 

[My Aswer Is This]

This may help you:

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "A1:A20"
End Sub

 

[tmccar]

This may help you:

I have tried this:
xlSheet.Select
ComboBox1.RowSource = "A1:A20"

And I get Runtime error 30 - Could not set the Rowsource property. Invalid property value

 

[Akuini]

This is one way to populate combobox list from a range:

Private Sub UserForm_Initialize()
Dim vlist
vlist = Sheets("Sheet1").Range("A1:A5")
ComboBox1.List = vlist
ComboBox1.ListIndex = 0
End Sub

this line:

ComboBox1.ListIndex = 0

make the first item in the list shows in the combobox.

 

[tmccar]

This is one way to populate combobox list from a range:

Private Sub UserForm_Initialize()
Dim vlist
vlist = Sheets("Sheet1").Range("A1:A5")
ComboBox1.List = vlist
ComboBox1.ListIndex = 0
End Sub

this line:

ComboBox1.ListIndex = 0

make the first item in the list shows in the combobox.

Thanks. I will check when I'm back in the office on Friday.
Tom

 

[Akuini]

I forgot, this should be more simple, like this:

Private Sub UserForm_Initialize()
ComboBox1.List = Sheets("Sheet1").Range("A1:A5").Value
ComboBox1.ListIndex = 0
End Sub

 

[My Aswer Is This]

I test all my scripts. Either you did not make this a part of your UserForm Initialize or some other reason it did not work. When your UserForm first launches it looks at the Initialize code and sets up your UserForm with all sorts of things as per your instructions to the Initialize code. This is not a Module code. It goes in your Userform.
I'm using Excel 2013 not sure what your using.

 

[tmccar]

I test all my scripts. Either you did not make this a part of your UserForm Initialize or some other reason it did not work. When your UserForm first launches it looks at the Initialize code and sets up your UserForm with all sorts of things as per your instructions to the Initialize code. This is not a Module code. It goes in your Userform.
I'm using Excel 2013 not sure what your using.

I am using Excel 2010.
And I'm running the script from Autocad (so it's not from within Excel). Maybe you thought it was?
So I think I will need to reference the Excel file (and sheet). That may be the cause of the error.

 

[tmccar]

I forgot, this should be more simple, like this:

Private Sub UserForm_Initialize()
ComboBox1.List = Sheets("Sheet1").Range("A1:A5").Value
ComboBox1.ListIndex = 0
End Sub

Yes, that works. But it only displays the first item in the Combobox (cell "A1"). Then when you click the scroll bar, you see the others.

 

[Akuini]

Yes, that works. But it only displays the first item in the Combobox (cell "A1"). Then when you click the scroll bar, you see the others.

Oh, sorry I misunderstood what you want.
Actually I don't know how to do that.
I tried

ComboBox1.DropDown

but it didn't work.

Wait, do you want the list shown only in the beginning or all the time?
If you want it all the time I suggest you use Listbox instead of combobox.