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
|