Excel VBA
I am a Leader at vbCity where I
answer questions mostly in the VBA forum. I created this page so I can keep
track of my own solutions that I come up with. That way I won't have to ask
myself, "Where is that piece of code??" If you have a question about any code
on this page, please go to vbCity, become a
member and post your question.
Exporting & Importing Code Modules
This procedure will export all of the worksheet and stand alone VBA
modules from an open project called Currmonth_gw.xls, and import the exported
modules into the Active Project. Note that for this to work, you must set (in
the Excel window, not the VBA IDE), Tools, Macro, Security, Trusted Publishers
tab, check Trust access to Visual Basic Project.
vbCity thread: Making two Excel workbooks into one in VBA
'Exports certain modules from an Excel VBA Project and imports them into the Active Project.
Sub ExportImportModules()
Dim intCounter As Integer
Dim intModCount As Integer
Dim lngType As Long
intModCount = Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents.Count
'This will export all of the worksheet and stand alone VBA modules from an open project called Currmonth_gw.xls,
'and import the exported modules into the Active Project.
For intCounter = 1 To intModCount
lngType = Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents(intCounter).Type
'1 or 2 or 100.
If lngType = vbext_ct_StdModule Or lngType = vbext_ct_ClassModule Or lngType = vbext_ct_Document Then
Application.VBE.ActiveVBProject.VBComponents(intCounter).Export _
("C:\" & Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents(intCounter).Name & ".bas")
'Now import the module into this project.
Application.VBE.ActiveVBProject.VBComponents.Import _
"C:\" & Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents(intCounter).Name & ".bas"
'You may have to do some module name maintenance, especially if both projects have the same module names.
End If
Next intCounter
End Sub
Reading in a text file
This solution is in here because I can never remember the exact syntax
for the "Input" statement. I can remember the "Open" syntax but if I press F1
(Help) over "Open", the Example does not refer to the "Input" statement.
Furthermore, the "See Also" link in Help does not refer to "Input" either.
vbCity thread: import txt file in LISTBOX. The example below reads in a
text file a loads the contents into a UserForm listbox.
Private Sub UserForm_Initialize()
Dim intCounter As Integer
Dim strLine As String
Dim strList() As String
ListBox1.ColumnCount = 2
intCounter = 0
Open "C:\Documents and Settings\My Documents\testinput.txt" For Input As #1
Do While Not EOF(1)
Input #1, strLine
strList = Split(strLine, " ", -1)
ListBox1.AddItem strList(0)
ListBox1.List(intCounter, 1) = strList(1)
intCounter = intCounter + 1
Loop
Close #1
End Sub
Setting the Rowsource/ListFillRange of a Listbox or Combobox on a Worksheet
Setting the Rowsource of a Listbox or Combobox in a UserForm is straightforward. If
the list is static:
Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = "Sheet1!A1:A4"
End Sub
If the list is dynamic:
Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = "Sheet1!A1:A" & Sheet1.Range("A1").End(xlDown).Row
End Sub
However if the listbox (from the Controls Toolbox) is in a worksheet,
setting the Rowsource is trickier. Actually a Listbox on a worksheet doesn't
have a Rowsource property - it has a ListFillRange property. Because the Shape
object can contain multiple types of objects, it uses a special object to hold
the specific object instance. This is the OleFormat() property of the Shape
object. To get a reference to the underlying object use the following code.
Thanks to vbCity member Lankymart for this solution
vbCity thread: Combobox listfillrange on a worksheet
Dim cmbList1 As Object
Set cmbList1 = ThisWorkbook.Worksheets("Sheet1").Shapes("Combobox1").OleFormat.Object
'Now manipulate various properties of the control using the cmbList1 reference.
cmbList1.ListFillRange = "Lists!H2:H4082"