We help IT Professionals succeed at work.

How to Dynamically Change the Size of a Frame and Scrollbar on an Excel UserForm

Quiver
Quiver asked
on
2,995 Views
Last Modified: 2014-12-16
I have a list of names on a spreadsheet that I am dynamically adding to a UserForm.  The list is variable resulting in the need to dynamically build the list.  The names are contained within a frame on the UserForm.

How can I adjust the size of the frame and scrollbar based on the number of names placed on the form?
I thought I could simply change the frame height and scrollbar height as the names were being placed on the form but this apparently does not work. (See attached)

Any help that you can provide would be greatly appreciated.

Thank you.
Sample-Dynamic-Frame-and-Scrollbar-v1.1.
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
You can change the height etc.

For example:

UserForm_search.Frame1.Height = 500

reduces the height significantly.

Author

Commented:
I thought I was varying the height dynamically with the following code where iCntrlList is incremented with each name line that is added to the UserForm.  But apparently this is not changing the height of the frame and scrollbar.  Any other suggestions?

    With UserForm_Search.Frame1
       .ScrollHeight = Frame_Scroll_Height + (Search_intHeight + Search_intVerticalGap) * iCntrList
       .Height = Frame_Height + (Search_intHeight + Search_intVerticalGap) * iCntrList
    End With
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
It may be changing it, but the calculation may not be correct.

My experiment to change it resulted in it being - not quite right.

As an experiment, try putting /3 at the end of each line.

Author

Commented:
/3 results in less names being displayed.

As currently coded, 22 of the 63 names are displayed.  The Scroll height increments to 1446 and the Height increments to 1046 while displaying the results.  When the Scroll height increments to 1489 and the Height increments to 1089 the results are no longer displayed.  I have attached some snagits from the displays that I put in.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'd like to try to help but I have no idea how to deal with the XML. I have a question though and that is why are you you using a frame and scrollbar and not a Listbox or Listview control?

Author

Commented:
It's not XML.  It is VBA.  This is one of many userforms, some with multiple pages, etc.  There is some static data on some of the forms that resulted in the frame being the best solution.  On many of the userforms/pages, the data is dynamically added but I know the maximum length of the frame.  

With this particular userform, I do not know the maximum length of the frame/scrollbars.  It is totally dependant on the number of rows of names (which is variable).

Thanks for your offer to help though.  Any and all help is appreciated.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It's not XML.  It is VBA.  
Here's what I see when I unpack your zip file.
XML?But in any case I ask again why are you using a frame rather then a listbox or listview, both of which are designed for dynamic data?

Author

Commented:
Hopefully, the file is now uploaded as a .xlsm file.  Sorry about that.  Unfortunately, it needs to be a frame to be consistent with the rest of the application.
Sample-Dynamic-Frame-and-Scrollbar-v1.1.

Author

Commented:
Just realized that I cannot name a file with a dot (.) when uploading.  Hopefully, the file is now being uploaded as a .xlsm file.
Sample-Dynamic-Frame-and-Scrollbar-v1-1.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sorry to say but the zip still looks the same. How are you creating the zip file? What version of Excel are you using?

Author

Commented:
Let's try again
Sample.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
OK now we're getting somewhere. Be back soon.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't understand something and that is why do you want to change the height of the frame? Since you have a scrollbar it would seem to me that you shouldn't need to do that.
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Actually, after you questioned why I was changing the height of the frame, I commented out the height in the code above, changed the default height value for the frame (350), changed the Frame_Scroll_Height value (65), and it worked perfectly.  I still need to adjust the scroll height dynamically to accommodate the variable amount of names.  

Thanks so much for your help!  For some reason I was definitely stuck on the idea that I needed to change both the height and scroll height.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I still need to adjust the scroll height dynamically to accommodate the variable amount of names.
Why? I didn't and it worked just fine. In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.