VBA PDF # 22-228 2021 - V1.

4
 VBA SOURCE CODE BOOK

Download Your Free Watch The Training Video Got Questions? Join Our 48k
Workbook Here Here Community

COLOFON
Created: January 4, 2022 by Rob Haman (EXCEL SUPPORT)

Source: Excel For Freelancers – Randy Austin

Source Released: 04-01-2022

Shared through: Facebook – Excel For Freelancers Group

Purpose: This VBA Code book is intended to be used as an Aid for

Excel VBA Users & Developers to give a better readability
and insigth on the used VBA code.

Used Tool: VBACodePrint

PREVIOUS VBA SOURCE CODE BOOKS

MEMBERSHIP MANAGER RECURRING INVOICES TOURNAMENT MANAGER SPA & SALON MANAGER 1 CLICK QUICK VIEW
(21-227) (21-226) (21-225) (21-224) (21-223)

DYNAMIC RACI MATRIX FILL & SING DOCUMENT DYNAMIC SKILLS MATRIX DOCUMENT WORKFLOW PDF TO EXCEL
(21-222) CREATOR (21-221) (21-220) MANAGER (21-219) (21-218)

VBA PDF # 22-228 2021 - V1.4

 Table of Contents
Projects .............................................................................................................................................................................................................................4
Dynamic_Userform .......................................................................................................................................................................................................4
Documents................................................................................................................................................................................................................4
Customers (Customers) .......................................................................................................................................................................................4
(Declarations) ...............................................................................................................................................................................................???
Worksheet_Deactivate [Sub ] ...........................................................................................................................................................................4
Worksheet_SelectionChange [Sub ].................................................................................................................................................................4
ThisWorkbook (Dynamic_Userforms.xlsm) ..........................................................................................................................................................8
(Declarations) ...................................................................................................................................................................................................8
Setup (Setup) ........................................................................................................................................................................................................7
(Declarations) ...............................................................................................................................................................................................???
Worksheet_Change [Sub ] ...............................................................................................................................................................................7
Invoices (Invoices) ................................................................................................................................................................................................6
(Declarations) ...............................................................................................................................................................................................???
Worksheet_Deactivate [Sub ] ...........................................................................................................................................................................6
Worksheet_SelectionChange [Sub ].................................................................................................................................................................6
InvoiceItems (Invoice Items) .................................................................................................................................................................................5
(Declarations) ...................................................................................................................................................................................................5
Modules ..................................................................................................................................................................................................................15
Setup_Macros.....................................................................................................................................................................................................15
(Declarations) .................................................................................................................................................................................................15
ListAllSheets [Sub ].........................................................................................................................................................................................15
Setup_AddFormBackroundPic [Sub ] .............................................................................................................................................................15
QuickViewForm_Macros ......................................................................................................................................................................................9
(Declarations) ...................................................................................................................................................................................................9
ShowEditIcon [Sub ] .......................................................................................................................................................................................13
QuickViewForm_Show [Sub ] ...........................................................................................................................................................................9
SaveData_Macros ..............................................................................................................................................................................................14
(Declarations) .................................................................................................................................................................................................14
SaveData [Sub ]..............................................................................................................................................................................................14
Forms......................................................................................................................................................................................................................16
DynamicForm .....................................................................................................................................................................................................16
(Declarations) .................................................................................................................................................................................................16
CancelBtn_Click [Sub ] ...................................................................................................................................................................................16
SaveBtn_Click [Sub ] ......................................................................................................................................................................................16

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 3 of 17

 Customers (Customers) Dynamic_Userform

1 Option Explicit
2
3 Private Sub Worksheet_Deactivate()
4 Shapes("EditBtn" ).Visible = msoFalse 'Hide Edit Icon on Sheet deactivate
5 End Sub
6
7 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
8 If Not Intersect(Target, Range("A3:Z999" )) Is Nothing And Range("A" & Target.Row).Value
<> Empty Then
9 Setup.Range("E5" ).Value = Target.Row
10 ShowEditIcon
11 End If
12 End Sub

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 4 of 17

 InvoiceItems (Invoice Items) Dynamic_Userform

1 Option Explicit

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 5 of 17

 Invoices (Invoices) Dynamic_Userform

1 Option Explicit
2
3 Private Sub Worksheet_Deactivate()
4 Shapes("EditBtn" ).Visible = msoFalse 'Hide Edit Icon on Sheet deactivate
5 End Sub
6
7 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
8 If Not Intersect(Target, Range("A3:Z999" )) Is Nothing And Range("A" & Target.Row).Value
<> Empty Then
9 Setup.Range("E5" ).Value = Target.Row
10 ShowEditIcon
11 End If
12
13 End Sub

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 6 of 17

 Setup (Setup) Dynamic_Userform

1 Option Explicit
2
3 Private Sub Worksheet_Change(ByVal Target As Range)
4 If Not Intersect(Target, Range("G5:H99" )) Is Nothing And Range("G" & Target.Row).Value
<> Empty And Range("H" & Target.Row).Value <> Empty Then
5 If IsNumeric(Range("H" & Target.Row).Value) = False Then Exit Sub 'Header Row must
be numeric value
6 Dim ShtNm As String
7 Dim HeadRow As Long, HeadCol As Long, ShtCol As Long, FirstCol As Long, LastCol As
Long, SetupCol As Long, SetupRow As Long
8 HeadRow = Range("H" & Target.Row) 'Header Row
9 ShtNm = Range("G" & Target.Row).Value 'Worksheet Name
10 SetupCol = Range("U4:AB4" ).Find(ShtNm, , xlValues, xlWhole).Column
11 SetupRow = 5
12 Range(Cells(5, SetupCol), Cells(34, SetupCol)).ClearContents 'Clear Previous headers

13 LastCol = ThisWorkbook.Sheets(ShtNm).Cells(HeadRow, 23).End(xlToLeft).Column 'Last

Column With a value
14 FirstCol = ThisWorkbook.Sheets(ShtNm).Cells(HeadRow, 1).End(xlToRight).Column
15 If FirstCol = LastCol Then FirstCol = 1 'Set to First Column on no blank columns
16 Range("I" & Target.Row).Value = FirstCol 'Set Default First Col.
17 Range("J" & Target.Row).Value = LastCol 'Set Default Last Col.
18 For HeadCol = FirstCol To LastCol 'loop through all column headers
19 Cells(SetupRow, SetupCol).Value = ThisWorkbook.Sheets(ShtNm).Cells(HeadRow,
HeadCol).Value 'Header Row value
20 SetupRow = SetupRow + 1
21 Next HeadCol
22 On Error Resume Next
23 ThisWorkbook.Sheets(ShtNm).Names(Replace(ShtNm, " " , "_" ) & "_Headers" ).Delete
24 On Error GoTo 0
25 Setup.Names.Add Replace(ShtNm, " " , "_" ) & "_Headers" , "=" & Range(Cells(5,
SetupCol), Cells(SetupRow - 1, SetupCol)).Address
26 Range("K" & Target.Row).Validation.Delete
27 Range("N" & Target.Row).Validation.Delete
28 Range("K" & Target.Row).Validation.Add xlValidateList, xlValidAlertStop, , Formula1:
="=" & Replace(ShtNm, " " , "_" ) & "_Headers"
29 Range("N" & Target.Row).Validation.Add xlValidateList, xlValidAlertStop, , Formula1:
="=" & Replace(ShtNm, " " , "_" ) & "_Headers"
30 End If
31 End Sub
32
33

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 7 of 17

 ThisWorkbook (Dynamic_Userforms.xlsm) Dynamic_Userform

1 Option Explicit

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 8 of 17

 QuickViewForm_Macros Dynamic_Userform

1 Option Explicit
2 'Private Declare Function GetDC& Lib "user32.dll" (ByVal hwnd&)
3 'Private Declare Function GetDeviceCaps& Lib "gdi32" (ByVal hDC&, ByVal nIndex&)
4
5 #If VBA7 Then
6 Private Declare PtrSafe Function GetDC& Lib "user32.dll" (ByVal hwnd&)
7 Private Declare PtrSafe Function GetDeviceCaps& Lib "gdi32" (ByVal hDC&, ByVal nIndex&)
8 #Else
9 Private Declare Function GetDC& Lib "user32.dll" (ByVal hwnd&)
10 Private Declare Function GetDeviceCaps& Lib "gdi32" (ByVal hDC&, ByVal nIndex&)
11 #End If
12
13 Sub QuickViewForm_Show()
14 Dim ShtNm As String, BackPic As String, PicField As String, PicName As String, PicFolder
As String, PicPath As String, LinkedSht As String, LinkedID As String, ColWidths As
String
15 Dim XPos As Double, YPos As Double 'Used for Form positioning
16 Dim TxtField As MSForms.TextBox
17 Dim LblField As MSForms.Label
18 Dim ImgField As MSForms.Image
19 Dim ListBox As MSForms.ListBox
20 Dim Cntrl As Control
21
22 Dim TopPos As Long, LeftPos As Long, BotPos As Long, RowLimit As Long, ColCount As Long,
RowCount As Long, FontSize As Long
23 Dim ActRow As Long, ActCol As Long, DetailRow As Long, HeadRow As Long, DataCol As Long,
StartCol As Long, EndCol As Long, LinkIDDataCol As Long
24 Dim LastRow As Long, LastCol As Long, LastResultRow As Long, LastResultCol As Long,
LastDataCol As Long
25 Dim ResultRng As Range
26
27 With ActiveSheet
28 ShtNm = ActiveSheet.Name 'Active Sheet Name
29 If ActiveSheet.Name = "Setup" Then
30 MsgBox "Please run this on sheets other than the Setup sheet"
31 Exit Sub
32 End If
33
34 ActRow = ActiveCell.Row 'Active Row
35 ActCol = ActiveCell.Column 'Active Column
36
37 On Error Resume Next
38 DetailRow = Setup.Range("G5:G34" ).Find(ShtNm, , xlValues, xlWhole).Row
39 On Error GoTo 0
40 If DetailRow = 0 Then
41 MsgBox "Please make sure to setup this sheet in the Dynamic Userform Table in
the Setup Sheet"
42 Exit Sub
43 End If
44 RowLimit = Setup.Range("E4" ).Value 'Row Limit
45 'Header Row, Start Column, Ending Col area all required
46 If Setup.Range("H" & DetailRow).Value = "" Or Setup.Range("I" & DetailRow).Value =
"" Or Setup.Range("J" & DetailRow).Value = "" Then
47 MsgBox "Pelase make sure the Dynamic Userform detail row contains a header row
along with starting and ending columns"
48 Exit Sub
49 End If
50 HeadRow = Setup.Range("H" & DetailRow).Value 'Header Row
51 StartCol = Setup.Range("I" & DetailRow).Value 'Start Col
52 EndCol = Setup.Range("J" & DetailRow).Value 'End Column
53 BackPic = Setup.Range("FormBackPic" ).Value 'Form Background Picture
54 FontSize = Setup.Range("E6" ).Value 'Set Font Size
55 If Dir(BackPic, vbDirectory) = "" Then BackPic = "" 'Clear String if incorrect
file path
56 'Picture Path
57 If Setup.Range("K" & DetailRow).Value <> "" Then
58 PicField = Setup.Range("K" & DetailRow).Value 'Picture Field Name
59 End If
60 If Setup.Range("L" & DetailRow).Value <> "" Then
61 PicFolder = Setup.Range("L" & DetailRow).Value & "/" 'Picture Folder
62 End If

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 9 of 17

 QuickViewForm_Macros Dynamic_Userform

63
64 'Set Initial Left and top Positions
65 LeftPos = 10 'Set Initial Left Pos
66 TopPos = 30 'Set Initial Top Pos
67
68 'Clear All Labels and Text Boxes & List Poxes & Pictures
69 With DynamicForm
70 For Each Cntrl In .Controls
71 On Error Resume Next
72 If InStr(Cntrl.Name, "Txt" ) > 0 Then Cntrl.Remove 'Delete all Text Boxes
73 If InStr(Cntrl.Name, "Lbl" ) > 0 Then Cntrl.Remove 'Delete all Labels
74 If InStr(Cntrl.Name, "Pic" ) > 0 Then Cntrl.Remove 'Delete all Pictures
75 If InStr(Cntrl.Name, "ListBox" ) > 0 Then Cntrl.Remove 'Delete all Combo
Boxes
76 On Error GoTo 0
77 Next Cntrl
78 End With
79
80
81 With DynamicForm
82
83 'Set Fileds
84 For DataCol = StartCol To EndCol
85 If PicField = ActiveSheet.Cells(HeadRow, DataCol).Value Then 'Check For
Picture data type
86 PicName = ActiveSheet.Cells(ActRow, DataCol).Value 'Picture Name
87 If PicName <> Empty Then PicPath = PicFolder & PicName 'Picture Path
88 If Dir(PicPath, vbDirectory) = "" Then GoTo NextField
89 Set LblField = .Controls.Add("Forms.Label.1" , "LblBox" & DataCol)
90 With LblField
91 .Top = TopPos
92 .Width = 80
93 .Font.Size = FontSize 'Dynamic Font Size
94 .Left = LeftPos
95 .BackStyle = fmBackStyleTransparent
96 .Caption = ActiveSheet.Cells(HeadRow, DataCol).Value 'Set Header
Label
97 End With
98
99 Set ImgField = .Controls.Add("Forms.Image.1" , "PicField" & DataCol)
100 With ImgField
101 .Picture = LoadPicture(PicPath): .PictureAlignment =
fmPictureAlignmentCenter: .PictureSizeMode = fmPictureSizeModeStretch

102 .AutoSize = False

103 .Width = 60
104 .Height = 60
105 .Top = TopPos
106 .Left = LeftPos + 65
107 TopPos = TopPos + 30 'Increment Top Pos.
108 End With
109 Else 'Non picture field
110 Set LblField = .Controls.Add("Forms.Label.1" , "LblBox" & DataCol)
111 With LblField
112 .Top = TopPos
113 .Width = 65
114 .Left = LeftPos
115 .BackStyle = fmBackStyleTransparent
116 .Font.Size = FontSize
117 .Caption = ActiveSheet.Cells(HeadRow, DataCol).Value 'Set Header
Label
118 End With
119
120 Set TxtField = .Controls.Add("Forms.TextBox.1" , "TxtBox" & DataCol)
121 With TxtField
122 .Value = ActiveSheet.Cells(ActRow, DataCol).Value 'Set Text Data
Field
123 .Top = TopPos
124 .Left = LeftPos + 65
125 .Width = 120
126 .Font.Size = FontSize

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 10 of 17

 QuickViewForm_Macros Dynamic_Userform

127 End With

128 End If
129
130
131 TopPos = TopPos + 30
132 If TopPos > BotPos Then BotPos = TopPos 'Set Lowest Bottom Position
133 If DataCol = RowLimit + 1 And DataCol <> EndCol Then 'Start New Column (but
not on last Field)
134 TopPos = 30 'Reset original top postion
135 LeftPos = LeftPos + 210 'Increment Left pos to the right
136 RowLimit = RowLimit + Setup.Range("E4" ).Value 'Increase Column Limit
137 End If
138
139
140 On Error GoTo 0
141 LastDataCol = DataCol
142 NextField:
143 Next DataCol
144
145 '
146 'Check For Linked Tables
147 If Setup.Range("M" & DetailRow).Value <> Empty And Setup.Range("N" & DetailRow)
.Value <> Empty Then
148 LinkedSht = Setup.Range("M" & DetailRow).Value 'Linked Sheet
149 LinkedID = Setup.Range("N" & DetailRow).Value 'Linked ID
150 On Error Resume Next
151 LinkIDDataCol = Range(Sheets(ShtNm).Cells(HeadRow, 1), Sheets(ShtNm).Cells(
HeadRow, EndCol)).Find(LinkedID, , xlValues, xlWhole).Column 'Locate Linked
ID Column
152 On Error GoTo 0
153 If LinkIDDataCol = 0 Then GoTo NoData
154
155 With Sheets(LinkedSht)
156 LastRow = .Range("A99999" ).End(xlUp).Row ' Last Row
157 If LastRow < 3 Then GoTo NoData
158 .Range("Y2" ).Value = Worksheets(ShtNm).Cells(HeadRow, LinkIDDataCol).
Value 'Criteria Header
159 .Range("Y3" ).Value = Worksheets(ShtNm).Cells(ActRow, LinkIDDataCol).
Value 'Criteria Value
160 .Range("A" & HeadRow & ":X" & LastRow).AdvancedFilter xlFilterCopy,
CriteriaRange:=.Range("Y2:Y3" ), CopyToRange:=.Range("AA2:AX2" ), Unique:
=True
161 LastResultRow = .Range("AA99999" ).End(xlUp).Row
162 If LastResultRow < 3 Then GoTo NoData
163 LastResultCol = .Range("AZ2" ).End(xlToLeft).Column 'Last Column of
results
164 Set ResultRng = Range(.Cells(3, 27), .Cells(LastResultRow, LastResultCol)
) 'Set Our results data range
165 ResultRng.Name = "LinkedTable" 'Assign Name To range
166 ColCount = LastResultCol - 26 'Number of Columns in Result Table
167 RowCount = LastResultRow - 1
168 If RowCount > 50 Then RowCount = 50 'Set Row Count limit
169 End With
170
171 'Create List Box
172 Set ListBox = .Controls.Add("Forms.ListBox.1" , "ListBox" )
173 If TopPos <> 30 Then LeftPos = LeftPos + 210 'Add Left Position Unless New
Column
174 With ListBox
175 .RowSource = "=linkedtable" 'Data Source
176 .ColumnCount = ColCount '# Of columns
177 .ColumnHeads = True
178 ColWidths = Application.WorksheetFunction.Rept("70;" , ColCount)
'Repeats column Pixel for # of Columns
179 .ColumnWidths = ColWidths ' "70;70;70" '
180 .Font.Size = FontSize - 2
181 .Height = RowCount * 12
182 .Left = LeftPos
183 .Top = 40 'Reset Initial Top Pos
184 Application.Wait Now + 0.00001 '(allows time to build source content
before setting width)

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 11 of 17

 QuickViewForm_Macros Dynamic_Userform

185 .Width = 75 * ColCount 'Set Width

186 LeftPos = LeftPos + .Width - 180 'Increase left postion width of listbox

187 End With

188
189 'Add List Box Title Label
190 Set LblField = .Controls.Add("Forms.Label.1" , "LblListBox" )
191 With LblField
192 .Top = 20
193 .Width = ListBox.Width
194 .Left = ListBox.Left
195 .TextAlign = fmTextAlignCenter
196 .Font.Bold = True
197 .BackStyle = fmBackStyleTransparent
198 .Font.Size = FontSize + 1
199 .Caption = Sheets(LinkedSht).Range("AA1" ).Value 'Set Header Label
200 End With
201 NoData:
202 End If
203 'Add Form Details
204
205 'Set Start Position of Form
206 .Caption = ActiveSheet.Name 'Form Caption Name
207 .StartUpPosition = 0 'Set Start Position of Form to 0 (No initial setting
specified)
208 ' 1 inch = 72 points for usually 96 or 120 dpi
209 'Form Positioning Code Found here:
https://stackoverflow.com/questions/6116929/align-userform-to-cells-in-excel#_=_

210 XPos = GetDeviceCaps(GetDC(0), 88) / 72 'Set Leftmost Horizontal position

211 YPos = GetDeviceCaps(GetDC(0), 90) / 72 'Set Upper Vertical Screen Position
212 .Left = (ActiveWindow.PointsToScreenPixelsX(1 * XPos) * 1 / XPos) 'Set Forms
Left Position (First column)
213 .Top = (ActiveWindow.PointsToScreenPixelsY(ActiveCell.Top * YPos) * 1 / YPos) +
ActiveCell.Height 'Set Forms Top Position (One row under selected Row)
214
215 'Set form Width & Height
216 If TopPos <> 30 Then LeftPos = LeftPos + 210 'Add Left Position Unless New
Column
217 .Height = BotPos + 70
218 If LeftPos < 300 Then
219 .Width = 300 'Set min Width
220 Else
221 .Width = LeftPos 'Set Form width
222 End If
223
224 'Set Form header the same as sheet name
225 With .FormHeader
226 .Caption = ActiveSheet.Name
227 .BackStyle = fmBackStyleTransparent
228 .Left = DynamicForm.Left 'Set Left Postion of Form header
229 .Width = DynamicForm.Width 'Set width same as form
230 .TextAlign = fmTextAlignCenter
231 End With
232
233 'Set Save & Cancel buttons
234 .SaveBtn.Left = .Width / 2 - 130: .SaveBtn.Top = BotPos + 10
235 .CancelBtn.Left = .Width / 2 + 60: .CancelBtn.Top = BotPos + 10
236
237 If BackPic <> "" Then 'Add Background Picture if available
238 .Picture = LoadPicture(BackPic)
239 .PictureAlignment = fmPictureAlignmentCenter
240 .PictureSizeMode = fmPictureSizeModeStretch
241 End If
242
243 .Controls("TxtBox" & StartCol).SetFocus 'Set Focus to First Field
244 .Show 'Show Form
245 End With
246 End With
247 End Sub
248

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 12 of 17

 QuickViewForm_Macros Dynamic_Userform

249
250
251 Sub ShowEditIcon()
252 With ActiveSheet.Shapes("EditBtn" )
253 .Left = 1
254 .Top = ActiveCell.Top
255 .Visible = msoCTrue
256 End With
257 End Sub

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 13 of 17

 SaveData_Macros Dynamic_Userform

1
2 Option Explicit
3
4 Sub SaveData()
5 Dim ActRow As Long, DataCol As Long, DetailRow As Long, StartCol As Long, EndCol As Long
6 Dim TxtField As MSForms.TextBox
7 Dim Cntrl As Control
8 Dim ShtNm As String
9
10 If Setup.Range("E5" ).Value = Empty Then Exit Sub
11 ShtNm = ActiveSheet.Name 'Active Sheet Name
12 ActRow = ActiveCell.Row 'Active Row
13 On Error Resume Next
14 DetailRow = Setup.Range("G5:G34" ).Find(ShtNm, , xlValues, xlWhole).Row
15 On Error GoTo 0
16 If DetailRow = 0 Then
17 MsgBox "Please make sure to setup this sheet in the Dynamic Userform Table in the
Setup Sheet"
18 Exit Sub
19 End If
20
21 StartCol = Setup.Range("I" & DetailRow).Value 'Start Col
22 EndCol = Setup.Range("J" & DetailRow).Value 'End Column
23
24 If StartCol = 0 Or EndCol = 0 Then
25 MsgBox "Please make sure to add Start and Ending columns for this table within the
Setup Sheet"
26 Exit Sub
27 End If
28
29 For DataCol = StartCol To EndCol
30 On Error GoTo NextCol 'Ignore errors for non-existging text Fields (such as
pictures)
31 Set TxtField = DynamicForm.Controls("TxtBox" & DataCol)
32 If Sheets(ShtNm).Cells(ActRow, DataCol).Value <> TxtField.Value Then Sheets(ShtNm).
Cells(ActRow, DataCol).Value = TxtField.Value 'Make update on any change
33 NextCol:
34 Next DataCol
35 End Sub
36

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 14 of 17

 Setup_Macros Dynamic_Userform

1 Option Explicit
2 Dim WkSht As Worksheet
3 Dim ShtRow As Long
4 Dim BackPic As FileDialog
5 Sub ListAllSheets()
6 Setup.Range("B4:B999" ).ClearContents
7 ShtRow = 4
8 For Each WkSht In ThisWorkbook.Worksheets
9 Setup.Range("B" & ShtRow).Value = WkSht.Name
10 ShtRow = ShtRow + 1
11 Next WkSht
12 End Sub
13
14 Sub Setup_AddFormBackroundPic()
15 Set BackPic = Application.FileDialog(msoFileDialogFilePicker)
16
17 With BackPic
18 .Title = "Please select a the member default picture"
19 .Filters.Add "Picture Files" , "*.jpg,*.gif" , 1
20 .AllowMultiSelect = False
21 If .Show <> -1 Then GoTo NoSelection
22 Setup.Range("E3" ).Value = .SelectedItems(1) 'Set Folder Path
23 NoSelection:
24 End With
25 End Sub
26

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 15 of 17

 DynamicForm Dynamic_Userform

1
2 Option Explicit
3
4
5 Private Sub CancelBtn_Click()
6 Me.Hide
7 Unload Me
8 End Sub
9
10 Private Sub SaveBtn_Click()
11 SaveData
12 Me.Hide
13 Unload Me
14 End Sub
15
16
17

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 16 of 17

 Index
Height, 10-12
A Hide, 16 S
ActCol, 9 hwnd, 9 SaveBtn, 12
ActiveCell, 9, 12-14 SaveBtn_Click, 16
ActiveSheet, 9, 10, 12-14 I SaveData, 14, 16
ActiveWindow, 12 Image, 9 SelectedItems, 15
ActRow, 9-11, 14 ImgField, 9, 10 SetFocus, 12
Add, 7, 10-12, 15 InStr, 10 Setup, 4, 6, 7, 9, 11, 14, 15
Address, 7 Intersect, 4, 6, 7 Setup_AddFormBackroundPic, 15
AdvancedFilter, 11 IsNumeric, 7 SetupCol, 7
AllowMultiSelect, 15 SetupRow, 7
Application, 11, 15 L Shapes, 4, 6, 13
AutoSize, 10 Label, 9 Sheets, 7, 11, 12, 14
LastCol, 7, 9 Show, 12, 15
B LastDataCol, 9, 11 ShowEditIcon, 4, 6, 13
BackPic, 9, 12, 15 LastResultCol, 9, 11 ShtCol, 7
BackStyle, 10, 12 LastResultRow, 9, 11 ShtNm, 7, 9, 11, 14
Bold, 12 LastRow, 9, 11 ShtRow, 15
BotPos, 9, 11, 12 LblField, 9, 10, 12 Size, 10-12
Left, 10-13 StartCol, 9, 10, 12, 14
C LeftPos, 9-12 StartUpPosition, 12
CancelBtn, 12 LinkedID, 9, 11
CancelBtn_Click, 16 LinkedSht, 9, 11, 12 T
Caption, 10, 12 LinkIDDataCol, 9, 11 Target, 4, 6, 7
Cells, 7, 10, 11, 14 ListAllSheets, 15 TextAlign, 12
ClearContents, 7, 15 ListBox, 9, 11, 12 TextBox, 9, 14
Cntrl, 9, 10, 14 LoadPicture, 10, 12 ThisWorkbook, 7, 15
ColCount, 9, 11, 12 Title, 15
Column, 7, 9, 11 M Top, 10-13
ColumnCount, 11 MSForms, 9, 14 TopPos, 9-12
ColumnHeads, 11 MsgBox, 9, 14 TxtField, 9, 10, 14
ColumnWidths, 11 msoCTrue, 13
ColWidths, 9, 11 msoFalse, 4, 6 U
Control, 9, 14 msoFileDialogFilePicker, 15 Unique, 11
Controls, 10-12, 14 Unload, 16
CopyToRange, 11 N
CriteriaRange, 11 Name, 9-12, 14, 15 V
Names, 7 Validation, 7
D NextCol, 14 Value, 4, 6, 7, 9-12, 14, 15
DataCol, 9-11, 14 NextField, 10, 11 VBA7, 9
Delete, 7 nIndex, 9 vbDirectory, 9, 10
DetailRow, 9, 11, 14 NoData, 11, 12 Visible, 4, 6, 13
Dir, 9, 10 NoSelection, 15
DynamicForm, 10, 12, 14 Now, 11 W
Wait, 11
E P Width, 10, 12
Empty, 4, 6, 7, 10, 11, 14 PicField, 9, 10 WkSht, 15
EndCol, 9-11, 14 PicFolder, 9, 10 Worksheet, 15
Explicit, 4-9, 14-16 PicName, 9, 10 Worksheet_Change, 7
PicPath, 9, 10 Worksheet_Deactivate, 4, 6
F Picture, 10, 12 Worksheet_SelectionChange, 4, 6
FileDialog, 15 PictureAlignment, 10, 12 WorksheetFunction, 11
Filters, 15 PictureSizeMode, 10, 12 Worksheets, 11, 15
Find, 7, 9, 11, 14 PointsToScreenPixelsX, 12
FirstCol, 7 PointsToScreenPixelsY, 12 X
fmBackStyleTransparent, 10, 12 PtrSafe, 9 xlFilterCopy, 11
fmPictureAlignmentCenter, 10, 12 xlToLeft, 7, 11
fmPictureSizeModeStretch, 10, 12 Q xlToRight, 7
fmTextAlignCenter, 12 QuickViewForm_Show, 9 xlUp, 11
Font, 10-12 xlValidAlertStop, 7
FontSize, 9-12 R xlValidateList, 7
FormHeader, 12 Range, 4, 6, 7, 9, 11, 12, 14, 15 xlValues, 7, 9, 11, 14
Formula1, 7 Remove, 10 xlWhole, 7, 9, 11, 14
Replace, 7 XPos, 9, 12
G Rept, 11
GetDC, 9, 12 ResultRng, 9, 11 Y
GetDeviceCaps, 9, 12 Row, 4, 6, 7, 9, 11, 14 YPos, 9, 12
RowCount, 9, 11
H RowLimit, 9, 11
hDC, 9 RowSource, 11
HeadCol, 7
HeadRow, 7, 9-11

Dynamic_Userforms.xlsm BY EXCEL SUPPORT 17 of 17