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"

Dynamically updating the contents of a listbox in a userform

I recently developed an Excel workbook application that had a userform with two listboxes and a frame of option buttons. The userform had a summary listbox on top and a detail listbox below. The user selected an item in the summary listbox, and the click event populated the detail listbox. The option buttons were supposed to change a value in the summary listbox. The summary listbox had a range as a Rowsource like so:
wksCustInfoTemp.Activate
Me.listSummary.RowSource = Range("A2:AI" & Range("C1").End(xlDown).Row).Address
Here's where the problems began. The option buttons click event put a value in column A, part of the summary listbox's Rowsource. When this happened all sorts of events were triggered, such the listbox's click event, and Excel would crash. The workaround was to have the option buttons put a value in a column that wasn't in the listbox's Rowsource, say out in column AA. Then after the userform closed and was reopened, the initialize event of the userform would transfer the data from column AA to column A, before setting the Rowsource property. But this workaround was cumbersome, and it depended on the user, or some event, opening the userform one last time, or one more time than was necessary. I tried putting the data transfer process in the Terminate event of the userform, but that still triggered unexpected userform events.

The breakthrough came when I realized that I could set the Rowsource property to a null string, transfer that data to the Rowsource range, and reset the Rowsource like so:

Private Sub optTM_Click()

    Dim intIndex As Integer
    Dim strLocId As String
    
    intIndex = Me.listSummary.ListIndex
    
    If intIndex >= 0 Then
        strLocId = Me.listSummary.Column(6, intIndex) & vbNullString
        If strLocId = wksCustInfoTemp.Range("G" & intIndex + 2).Value Then
            'Remove the Rowsource.
            Me.listSummary.RowSource = ""
            'Modify the Rowsource range.
            wksCustInfoTemp.Range("A" & intIndex + 2).Value = "TM"
            'Reset the Rowsource.
            wksCustInfoTemp.Activate
            Me.listSummary.RowSource = Range("A2:AI" & Range("C1").End(xlDown).Row).Address 
        End If
    End If
End Sub