+ Reply to Thread
Results 1 to 3 of 3

listbox.height setup will not work unless code is interrupted

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    listbox.height setup will not work unless code is interrupted

    I have a bizarre problem (to me). I must interrupt my code in VBA in order for the code to run correctly. Either, by setting a breakpoint or inserting a msgbox.
    This has to do with setting the listbox .height on the form. The listbox is named “lstAttendees”

    To interrupt the code, I use the line: MsgBox "height = " & .Height

    If you run it without the msgbox the form will stay small and only show two employees. If I include the msgbox the form expands correctly.
    Note: The form adjusts its height based on the listbox height and the listbox height is is based on the number of rows in the data field. There is also a maximum listbox height calculation to avoid problems when there are many columns.

    Private Sub UserForm_Initialize()
    
    Dim lastRow As Integer
    Dim emplArr
    Dim frmTop As Integer
    Dim startRowCnt As Integer
    Dim endRowCnt As Integer
    Dim holder As String
    Dim chbxTmp As Control
    Dim topicRng As Range
    Dim cbnOfSt As Integer
    Dim tmpHgt As Integer
    Dim topic As Range
        
    cbnOfSt = 60
    
     lastRow = Range(Range("b2"), Range("b2").End(xlDown)).Count + 1
    
      frmTop = 80
      emplArr = Range("B2:B" & lastRow)
      
      'list the names
      With lstAttendees
            .List = Range(Range("b2"), Range("b2").End(xlDown)).Value
            .ListStyle = fmListStyleOption
            .MultiSelect = fmMultiSelectMulti
      End With
    
    '!!!!!!!!!!!!!!!!!!!!!!!!!!PROBLEM AREA!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        'Listbox height
        With lstAttendees
            .Height = lastRow * 12.5
            'MsgBox "height = " & .Height  'Turn this on to see correct operation of listbox height
            
            ' set maximum size
            If .Height >= 300 Then
                .Height = 300
            End If
                 
            'workaround the scrollbar bug
            '.IntegralHeight = false
            '.IntegralHeight = True
            .MultiSelect = fmMultiSelectSingle
            .MultiSelect = fmMultiSelectExtended
            
        End With
    
      'Form height
        Me.Height = lstAttendees.Height + 140
        Me.ScrollHeight = Me.Height
        Me.ScrollBars = fmScrollBarsVertical
        
        
        'Button Positions, move to bottom of form
        cbn_Enter_Data.top = Me.Height - cbnOfSt
        cbn_Close_Form.top = Me.Height - cbnOfSt
      
        'setup Topic Listbox
        Set topicRng = Range("topics")
       
        'setup Topic Listbox (from forums)
        For Each topic In topicRng
            If Not topic.Value = "(Required)" Then
                With Me.cbx_Topic_Choice
                    .AddItem topic.Value
                End With
            End If
        Next topic
    
    End Sub
    THANKS FOR LOOKING!
    Robert
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: listbox.height setup will not work unless code is interrupted

    Hi,

    I think the problem is to do with trying to change a form object during the initialisation event. Add a brief 1 second pause with

    Application.Wait Now + TimeValue("0:0:1")

    just before setting the listbox height
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: listbox.height setup will not work unless code is interrupted

    thanks Richard, that works. But it sounds like changing the form object during the initialization event is "cough" bad form.
    Maybe I should be constructing the form differently. Ill have to think about that.
    Thanks again for helping me out. If I were on your side of the Atlantic I would buy you a pint! Fullers would be my choice.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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