Resizing both a Userform and its Listbox to Search data results...

[chazrab]

Is it possible to resize both the userform AND its listbox to display only the "size" results( i.e., search results rows) in the listbox, instead of creating one huge userform with one huge listbox ? Below is the result. Although both have descriptive caption names, this is just the SAME
userform and lisrbox resized to accomodate search results ? I tried to make this as simple as possible. Hope its clear - just resizing the same userform and its listbox object.

Thanks for anyone's help.
cr

 

[Legacy 456155]

Upload a copy of your workbook in this folder. One or both of us are missing something.

 

[chazrab]

Upload a copy of your workbook in this folder. One or both of us are missing something.

done. The file is 2020 TEST in your folder. Three sheets. BUTTON tab has two buttons and my comments. Tried to make it as
simple to follow as possible, but complete, so you should be able to see what's happening and what's not happening with
the code. We both spent considerable time on this, and again, I thank you for all your help.

Please let me know if you have
any comments or questions in the eval. process I ran and reran this whole scenario exactly as I painted in the notes, as if another
were following it and no issues. Reports are generated with dates and category values in the old working form(blue) scenario and with the ADJUSTHEIGHT form(gray) and results should display as described.

 

[Legacy 456155]

Well, there may be an important distinction we are leaving out. Your examples, thus far, have led me to believe that we were working with a single column listbox. After viewing your file, I think we are working with multi-column listboxes. Is that the case? And if so, are you trying to resize for each column?

 

[chazrab]

Well, there may be an important distinction we are leaving out. Your examples, thus far, have led me to believe that we were working with a single column listbox. After viewing your file, I think we are working with multi-column listboxes. Is that the case? And if so, are you trying to resize for each column?

I don't think the columns need to be resized the way I see it, because the result reports will
always only be to col G and the widths are fixed as below in the Properties box. Which means the Listbox and userform widths are fixed. Only the listbox and userform height increase or decrease in rows of the result.

 

[Legacy 456155]

Ok. I'm still stuck on width because that is typically where the focus is. Replace all of your current code in form ADJUSTHEIGHT with this. It'll get you close. Maybe close enough.

Option Explicit

Private Const TweekListItemHeight = 2
Private ListBoxOriginalHeight As Single
Private ButtonOriginalTop As Single
Private FormOriginalHeight As Single

Private Sub cmdAutoSize_Click()
    AutoSize
End Sub

Private Sub AutoSize()
    lst1.Height = lst1.ListCount * (lst1.Font.Size + TweekListItemHeight)
    cmdAutosize.Top = ButtonOriginalTop + lst1.Height - ListBoxOriginalHeight
    Height = FormOriginalHeight + lst1.Height - ListBoxOriginalHeight
    'if you want to center your form after resizing
    Move ActiveWindow.Width / 2 - Width / 2, ActiveWindow.Height / 2 - Height / 2
End Sub

Private Sub UserForm_Initialize()
    ListBoxOriginalHeight = lst1.Height
    ButtonOriginalTop = cmdAutosize.Top
    FormOriginalHeight = Height
    With Sheets("REPORT")
        lst1.RowSource = "REPORT!" & .Range(.Cells(1), .Cells(7).End(xlDown).Offset(1)).Address
    End With
    lst1.IntegralHeight = False
End Sub

 

[chazrab]

Ok. I'm still stuck on width because that is typically where the focus is. Replace all of your current code in form ADJUSTHEIGHT with this. It'll get you close. Maybe close enough.

Option Explicit

Private Const TweekListItemHeight = 2
Private ListBoxOriginalHeight As Single
Private ButtonOriginalTop As Single
Private FormOriginalHeight As Single

Private Sub cmdAutoSize_Click()
    AutoSize
End Sub

Private Sub AutoSize()
    lst1.Height = lst1.ListCount * (lst1.Font.Size + TweekListItemHeight)
    cmdAutosize.Top = ButtonOriginalTop + lst1.Height - ListBoxOriginalHeight
    Height = FormOriginalHeight + lst1.Height - ListBoxOriginalHeight
    'if you want to center your form after resizing
    Move ActiveWindow.Width / 2 - Width / 2, ActiveWindow.Height / 2 - Height / 2
End Sub

Private Sub UserForm_Initialize()
    ListBoxOriginalHeight = lst1.Height
    ButtonOriginalTop = cmdAutosize.Top
    FormOriginalHeight = Height
    With Sheets("REPORT")
        lst1.RowSource = "REPORT!" & .Range(.Cells(1), .Cells(7).End(xlDown).Offset(1)).Address
    End With
    lst1.IntegralHeight = False
End Sub

No need for any concerns - works perfectly!. Exactly what I wanted. See the two images. All I did was widen the userform to a much greater width than the sum of the total column widths in the Column width property line.

This gives your code room to reduce or expand the listbox and userform. See how the width is constant in both images but the listbox and userform sizes change with the autofilter search results.

I also placed Autosize in the code block below ADJUSTHEIGHT.Show, where the code is run, so there would be no need to click the Autosize button on the displayed reslt form. The final adjusted userform and listbox displays immediately.

One comment: I don't know in the annals of Excel solutions if this has ever been attempted and done before, and it would surprise me greatly if it had not, with the many Excel MVP's and slick code solutions out there. Once again, many thanks. With my level of Excel experience, this would have taken me way too long for me to figure out. cr

 

[Legacy 456155]

if this has ever been attempted and done before

Oh, I'm sure there is plenty of code out there to resize a listbox. Better than what I gave you.