+ Reply to Thread
Results 1 to 8 of 8

Form takes a long time to open

Hybrid View

00Able Form takes a long time to open 11-26-2010, 11:25 PM
davesexcel Re: Adding a drop down... 11-27-2010, 02:59 AM
00Able Re: Adding a drop down... 11-27-2010, 02:31 PM
royUK Re: Adding a drop down... 11-28-2010, 04:51 AM
00Able Re: Adding a drop down... 11-28-2010, 07:56 PM
royUK Re: Adding a drop down... 11-27-2010, 02:40 PM
davesexcel Re: Adding a drop down... 11-27-2010, 03:33 PM
00Able Re: Adding a drop down... 11-27-2010, 08:14 PM
  1. #1
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Smile 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?
    Last edited by 00Able; 11-27-2010 at 09:08 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    Re: Adding a drop down Calendar to VB User Form

    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.
    Last edited by davesexcel; 11-27-2010 at 03:03 AM.

  3. #3
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: Adding a drop down Calendar to VB User Form

    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"

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Adding a drop down Calendar to VB User Form

    Quote Originally Posted by 00Able View Post
    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
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: Adding a drop down Calendar to VB User Form

    Quote Originally Posted by royUK View Post
    Your profile says Excel 2003, I suggest you amend it.


    Excel 2010 does not support Calnedar controls
    At home, I have 2007 and 2010; however, at work I have 2003...so I have to make sure everything is 2003 compatible...wish they would update...

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Adding a drop down Calendar to VB User Form

    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

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    Re: Adding a drop down Calendar to VB User Form

    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

  8. #8
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Re: Adding a drop down Calendar to VB User Form

    Thank you to both of you for both of your assistance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1