Results 1 to 3 of 3

listbox.height setup will not work unless code is interrupted

Threaded 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

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