Solved

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

Posted on 2014-12-16
17
1,144 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.
0
Comment
Question by:Quiver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
17 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40502462
You can change the height etc.

For example:

UserForm_search.Frame1.Height = 500

reduces the height significantly.
0
 

Author Comment

by:Quiver
ID: 40502568
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40502866
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Quiver
ID: 40502970
/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.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40503003
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?
0
 

Author Comment

by:Quiver
ID: 40503234
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.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40503257
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?
0
 

Author Comment

by:Quiver
ID: 40503656
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.
0
 

Author Comment

by:Quiver
ID: 40503684
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.
0
 

Author Comment

by:Quiver
ID: 40503688
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40503693
Sorry to say but the zip still looks the same. How are you creating the zip file? What version of Excel are you using?
0
 

Author Comment

by:Quiver
ID: 40503701
Let's try again
Sample.xlsm
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40503778
OK now we're getting somewhere. Be back soon.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40503806
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.
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40503812
I just comment out this code in UserForm_Initialize and it seems to work correctly.
    With UserForm_Search.Frame1
       .ScrollHeight = Frame_Scroll_Height + (Search_intHeight + Search_intVerticalGap) * iCntrList
       .Height = Frame_Height + (Search_intHeight + Search_intVerticalGap) * iCntrList
      '  MsgBox ("ScrollHeight " & .ScrollHeight & " Height " & .Height)
    End With

Open in new window


Try it and tell me if you disagree.
0
 

Author Closing Comment

by:Quiver
ID: 40503967
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.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40503987
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
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question