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]
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
![DLIST_zpse84rka4g.jpg](/board/proxy.php?image=http%3A%2F%2F%5BURL%3D%22http%3A%2F%2Fs1372.photobucket.com%2Fuser%2Ftmccar%2Fmedia%2FDLIST_zpse84rka4g.jpg.html%22%5D%5BIMG%5Dhttp%3A%2F%2Fi1372.photobucket.com%2Falbums%2Fag357%2Ftmccar%2FDLIST_zpse84rka4g.jpg&hash=89f5457db7bfc7493e69be0957f18859)