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]

I see. I think someone else here has already answered your question now. I do not think it's possible to have a combobox list show all the time. You have to click the scroll icon. Or use a listbox.

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]

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.

Actually, it's not that important that the full list does not show, as long as the first item shows.
The main objective was to have a more efficient way to populate the list than my "For....next" loop, and you have solved that. so thanks.

 

[tmccar]

I see. I think someone else here has already answered your question now. I do not think it's possible to have a combobox list show all the time. You have to click the scroll icon. Or use a listbox.

Yes, I think you're right. Anyway, my main issue was to have a more efficient way to populate the list, so I have that now.

 

[Akuini]

Actually, it's not that important that the full list does not show, as long as the first item shows.
The main objective was to have a more efficient way to populate the list than my "For....next" loop, and you have solved that. so thanks.

Ok, glad to help & thanks for the feedback.