Thread: Form takes a long time to open

I also have attached it so you can view. This form is not large, but it seems to take awhile to open, can someone look at the code and tell me if something in the code is causing this?

Private Sub UserForm_Initialize()

Dim cNam As Range
Dim cRea As Range
Dim cLin As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cNam In ws.Range("LeadList")
  With Me.cboName
    .AddItem cNam.Value
  End With
Next cNam

For Each cRea In ws.Range("ReasonList")
  With Me.cboReason
    .AddItem cRea.Value
  End With
Next cRea

For Each cLin In ws.Range("LineList")
  With Me.cboLine
    .AddItem cLin.Value
  End With
Next cLin

Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtDate.SetFocus

End Sub

Also, where would you put code to add a calendar drop down, if that is possible?

Your one named range is 65536 rows long.

Use this to populate the Line Lead ComboBox

'-------------------------------------------
    Dim LstRw1 As Long, r As Range
    LstRw1 = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Range(ws.Cells(1, 2), ws.Cells(LstRw1, 1))
    cboName.List = r.Value
'-----------------------------------------------

Right click on the Tool Box,select add additional controls, see if You can find Microsoft Date and Time Picker. Click it to add to your tool box.

I wouldn't use the DatePicker control, it can be problematical - it's not always available on other users'PCs & from Office 2010 it is no longer available.

See the alternatives herehttp://excel-it.com/free_addins.htm, I generally recommend the ComboBox example

replace

For Each cNam In ws.Range("LeadList")
  With Me.cboName
    .AddItem cNam.Value
  End With
Next cNam

With this

-------------------------------------------
    Dim LstRw1 As Long, r As Range
    LstRw1 = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Range(ws.Cells(1, 2), ws.Cells(LstRw1, 1))
    cboName.List = r.Value

Originally Posted by 00Able

where do I place that at in the code? Also I have microsoft 2010 and I do not see the "Microsoft Date and Time Picker"

Your profile says Excel 2003, I suggest you amend it.


Excel 2010 does not support Calnedar controls