Excel VBA

css navigation by Css3Menu.com

Custom Dialogs

I have been getting a lot of notes about filling a custom dialog box. I particularly like this method because it allows me to edit the list and use it without re-programming. The lines between the § symbols are the key to dynamically filling the list.
Option Explicit
'------- Print List chosen from dialog

Sub PrintDirectory()
    Dim LastRow As Integer, Dlg As Object, sLineN As Integer, LstBox As Object
    Dim diRs, DiLog As Integer
    MainSht = ThisWorkbook.Name
    Set Dlg = DialogSheets("dlgChoose")
    Set LstBox = Dlg.DropDowns("Drop Down 5")
    Application.ScreenUpdating = False
    Sheets("Lists").Activate
    Range("B1").Select
 §  LastRow = Application.CountA(ActiveSheet.Range("B:B"))
    diRs = ActiveSheet.Range(Cells(1, 2), Cells(LastRow, 2)).Address
    With LstBox
        .ListFillRange = "Lists!" & diRs    'Range is address above
    End With
 §  DiLog = Dlg.Show        'Show dialog box
        If DiLog = 0 Then Exit Sub
        If LstBox.ListIndex = 0 Then
            MsgBox "Nothing selected"
            Exit Sub
        End If
    SelDirect = LstBox.List(LstBox.ListIndex) 'Get name
    sLineN = LstBox.ListIndex                 'Index number
    Application.StatusBar = "Selected " & SelDirect & " on line " & sLineN
    MainThang = Cells(sLineN, 1).Value      'Value of Indexed row, col 1
    DoExtract       		           'Run Extract program
                                            'PreparePrint
  Application.ScreenUpdating = True
  Application.StatusBar = False
End Sub


© 2000-2024

Updated:  06/21/2024 07:42
This page added:  01 July 2000