Hi There,
I've never studied VBA, as a result I tend to do things the long way. Because there are so many combo boxes, this seems to be running slowly.
Could someone let me know if there is a way to speed up this code? It's for a userform that's taking too long to initialise
Thanks Heaps!
Code
Private Sub UserForm_Initialize()
With ComboBox8
.AddItem ("No")
.AddItem ("Yes")
End With
DTPicker1.SetFocus
With ComboBox4
.AddItem ("Patron")
.AddItem ("Staff")
.AddItem ("Contractor")
End With
With ComboBox3
Dim c As Range
With Sheets("Lists")
For Each c In .Range("C:C", .Range("C" & Rows.Count).End(xlUp))
If c.Value <> "" Then ComboBox3.AddItem c.Value
Next c
End With
End With
With ComboBox1
.AddItem "Male"
.AddItem "Female"
End With
With ComboBox5
.AddItem ("Front")
.AddItem ("Back")
.AddItem ("Both")
End With
With ComboBox6
Dim e As Range
With Sheets("Lists")
For Each e In .Range("E:E", .Range("E" & Rows.Count).End(xlUp))
If e.Value <> "" Then ComboBox6.AddItem e.Value
Next e
End With
End With
With ComboBox2
Dim d As Range
With Sheets("Lists")
For Each d In .Range("D:D", .Range("D" & Rows.Count).End(xlUp))
If d.Value <> "" Then ComboBox2.AddItem d.Value
Next d
End With
End With
With ComboBox7
Dim f As Range
With Sheets("Lists")
For Each f In .Range("G:G", .Range("G" & Rows.Count).End(xlUp))
If f.Value <> "" Then ComboBox7.AddItem f.Value
Next f
End With
End With
With ComboBox9
Dim h As Range
With Sheets("Lists")
For Each h In .Range("J:J", .Range("J" & Rows.Count).End(xlUp))
If h.Value <> "" Then ComboBox9.AddItem h.Value
Next h
End With
End With
End Sub
Private Sub CommandButton1_Click()
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 1).Value = DTPicker1.Value
Cells(emptyRow, 2).Value = TextBox1.Value
Cells(emptyRow, 3).Value = ComboBox4.Value
Cells(emptyRow, 4).Value = TextBox3.Value
Cells(emptyRow, 5).Value = DTPicker2.Value
Cells(emptyRow, 6).Value = ComboBox1.Value
Cells(emptyRow, 7).Value = TextBox4.Value
Cells(emptyRow, 8).Value = TextBox5.Value
Cells(emptyRow, 9).Value = TextBox6.Value
Cells(emptyRow, 10).Value = TextBox7.Value
Cells(emptyRow, 11).Value = TextBox8.Value
Cells(emptyRow, 12).Value = ComboBox5.Value
Cells(emptyRow, 13).Value = TextBox10.Value
Cells(emptyRow, 14).Value = TextBox13.Value
Cells(emptyRow, 15).Value = ComboBox2.Value
Cells(emptyRow, 16).Value = ComboBox6.Value
Cells(emptyRow, 17).Value = TextBox14.Value
Cells(emptyRow, 18).Value = ComboBox7.Value
Cells(emptyRow, 19).Value = TextBox15.Value
Cells(emptyRow, 20).Value = ComboBox8.Value
Cells(emptyRow, 21).Value = TextBox16.Value
Dim answer As Integer
answer = MsgBox("Are you sure you want to clear the form?", vbYesNo + vbQuestion, "Clear form")
If answer = vbYes Then
Dim z As Control
For Each z In UserForm1.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
Call UserForm_Initialize
Else
'do nothing
End If
End Sub
Display More