Results 1 to 10 of 10
  1. #1
    Kettle is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    5

    Resize a list box to less than height setting.

    Hello, I am new to access/vba but not to programming. I have a listbox within a form. The listbox height property is set to 5" and the listbox is anchored at all four sides. The form border is set to sizeable. If the user expands the form by dragging a corner, everything operates as I would expect, however if they make the form smaller, the listbox will stop shrinking once it gets to the 5" height. After that, the form will continue to shrink but the listbox will go off the edge of the form. In C# there is a setting called autosize that I can turn off to make this behavior go away, but I don't know how to do it with access and vba. Thanks for your time.



    I am using access 2007 on windows 7 64bit.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Are you saying the listbox expands if the form expands? I am not seeing that behavior. What I see is the listbox size is fixed no matter the form size. Just as happens to all the controls if the form is reduced small enough, controls are not visible.

    I don't understand what behavior you want to make go away.

    Why do you allow the form to be sizable?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Kettle is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    If you anchor the sides of the listbox then it will expand as the form expands. It should contract as well, and does so long as you are over the default size for the listbox. I want it to continue contracting. I allow the form to be sizable because it displays information of variable size and I think it makes for a more pleasing user experience.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I normally don't allow forms to be sizable.

    I don't think you will get what you are looking for.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Kettle is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    It's possible that this is the answer. In C# you can customize everything. In access/vba I am finding that a lot of functions simply aren't there. Like where is the event "on resize"? I could normally just tap into that and slap something together to fix this behavior, but access and vba doesn't have it. Thanks for trying. I still have hope someone out there knows a solution.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by Kettle View Post

    ...where is the event "on resize"...
    In Form Design View, with the Form, itself, selected, go to Properties - Events and you'll find the OnResize Property, about a third of the way down.

    Linq ;0)>

  7. #7
    Kettle is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    Heyoooooo! I don't know how I missed that (embarrassing). I was able to do a slap and tape to make it do what I want. If anyone knows where the checkbox is to fix this I would still love to know, but this solution will work for me. Thanks guys.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Well, I learned something new.

    Want to show the code you use?

    Mark thread solved with Thread Tools above the first post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by Kettle View Post

    ...I don't know how I missed that...
    It's easy enough to miss, there being so many properties! The easiest way to find one, for me, is to go into the Code Module, use the left-hand dropdown box to select the object, then use the right-hand box to drop down a list of all of the associated events.

    Good luck with your project!

    Linq ;0)>

  10. #10
    Kettle is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    5
    Quote Originally Posted by June7 View Post
    Well, I learned something new.

    Want to show the code you use?

    Mark thread solved with Thread Tools above the first post.
    Code:
        'MsgBox "DefaultHeight=" & Me.InsideHeight & " DefaultWidth=" & Me.InsideWidth & _
        '    " VerticalAdjust=" & Me.InsideHeight - lstSalesOrders.Height & _
        '    " HorizontalAdjust=" & Me.InsideWidth - lstSalesOrders.Width & _
        '    " ButtonAdjust=" & cmdOpen.Top - lstSalesOrders.Height - lstSalesOrders.Top
        
        If Me.InsideHeight < 8625 And Me.InsideHeight > 1065 Then
            lstSalesOrders.Height = Me.InsideHeight - 1065
            
            Dim intButtonPosition As Integer
            intButtonPosition = lstSalesOrders.Height + lstSalesOrders.Top + 120
            
            cmdDuplicate.Top = intButtonPosition
            cmdOpen.Top = intButtonPosition
            cmdCancel.Top = intButtonPosition
        End If
        
        If Me.InsideWidth < 5400 And Me.InsideWidth > 420 Then
            lstSalesOrders.Width = Me.InsideWidth - 420
        End If
    So this basically says, if the form is smaller than the default size, then resize the listbox to be a constant amount smaller than the form size. (1065 fewer units vertically and 420 units horizontally). I used message box output to tell me what the constant values were. I had to move three buttons as I resized as well (cmdDuplicate, cmdOpen, cmdCancel). I just plunked this in form.onresize. Thanks again for the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change Height of Box to height of textBox
    By oxicottin in forum Reports
    Replies: 7
    Last Post: 03-15-2013, 09:54 PM
  2. Setting Report Height In Code
    By ccordner in forum Reports
    Replies: 1
    Last Post: 12-07-2012, 10:26 PM
  3. Resize question
    By oatsybob in forum Access
    Replies: 3
    Last Post: 10-24-2012, 11:44 PM
  4. Replies: 4
    Last Post: 09-15-2012, 07:31 PM
  5. Resize form automatically
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-30-2010, 10:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums