+ Reply to Thread
Results 1 to 23 of 23

Need help on Frame Scroll Bar ScrollHeight

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Need help on Frame Scroll Bar ScrollHeight

    I am not experienced with ScrollHeight so I need help on this.

    I am creating a dynamic form to display a lot of text to the end user. There will be so much text that I will need a scroll bar to make navigation obvious/easily accessible to end users. Now I have have the text displayed on a control * which sits just inside a Frame that contains a working scrollbar (thanks to code by Andy Pope)

    Unfortunately the scrollbar movement is usually too small depending on the amount of text being passed.

    I need a formula that will evaluate the size of text passed (should I do this by linebreaks or len?) and then use this to calculate an appropriate Scrollheight so that the end result is that the scroll bar top/bottom = the top/bottom of the text displayed inside the textbox inside the frame.




    * I started off using a Label but was forced to adopt a TextBox to bypass the character limits on Labels


    P.S. I didn't think this would be needed but if it helps solve this thread faster, below is the code in the form I am creating. I have used red font where I am stumped (If anybody wants to recreate this form - place a textbox inside a frame on a new userform. The code will dynamically set size & position of the frame & textbox)

    Please Login or Register  to view this content.
    Last edited by mc84excel; 04-07-2019 at 09:00 PM.
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Need help on Frame Scroll Bar ScrollHeight

    Hi,
    What you need is a textbox, and that may be placed inside the frame, just give the frame a size you think is sufficient to read, make sure the textbox's proprerties are set to multiline and wordwrap is set to true then you'll automaticcaly get schollbars on the textbox and the user can scroll up and down.
    If you set the property to Locked = True then you will no be able to scroll.
    I do not know how much text is involved but take it from there. Ihaven't used Any Pope's frame scroll bars but thst is problably more meant to dispaly more controls if the frame is larger than the userform but not for this purpose
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by Keebellah View Post
    ...a textbox, and that may be placed inside the frame, just give the frame a size you think is sufficient to read, make sure the textbox's proprerties are set to multiline and wordwrap is set to true then you'll automaticcaly get schollbars on the textbox and the user can scroll up and down.
    I already have a working scrollbar as noted in my opening post.

    My question is: How do I adjust the ScrollHeight at runtime so that its movement equates to the amount of text that will be displayed.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Need help on Frame Scroll Bar ScrollHeight

    I know you said you had a scroll bar, but the textbox adds it's own scroll bar if the text exceeds the textframe
    I haven't tried it yet but there must be a formula to calculate the number of characters and the word wrap in the textbox based on that number of characters
    I would have to play around with it and google for scripting tips.

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    THREAD BUMP!

    Is there any Form/Frame/Scrollbar expert that can help me with setting the ScrollHeight of a Frame so that its scroll movement (i.e. upper + lower limits) agrees with the amount of text shown on the Textbox that sits just inside said Frame?

    The amount of text displayed will vary (the text is passed to the textbox prior to the Form being displayed) but couldn't a formula handle this? The formula could use the Len of the text string to set the ScrollHeight of the Frame?

    I would do this myself except that I am new to programming scrollbars - and most important - I don't have a clue what formula I need or even how to go about working out what this formula should be.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Need help on Frame Scroll Bar ScrollHeight

    Could you attach a sample file with the userform and a record or two with the text you want displayed and the macros you have so far?
    I don't even know if it's possible but without trying ... you never know.
    So, attach a file and I'll play with it, other's might pick it up to and who knows

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Need help on Frame Scroll Bar ScrollHeight

    You didn't answer Keebellah, what's wrong with the scrollbar on the textbox itself? - That would appear to do exactly what you want

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: Need help on Frame Scroll Bar ScrollHeight

    Maybe further explanation is required.

    Forget frames. In fact look at this gently swinging pocket watch......foooooorget fraaaaaaaames. You are feeling sleepy. Forget fraaaaaames.

    Ok, now replace the frame you've just forgotten about with a text box. When you put text in it it will automatically add scrollbars which equate to the amount of text that is displayed.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Need help on Frame Scroll Bar ScrollHeight

    As an aside, you don't really need an expert for this, it's pretty trivial, consider:
    Please Login or Register  to view this content.

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Need help on Frame Scroll Bar ScrollHeight

    Hey Kyle123, Nice code, tried it out, I modified it for the textbox and looks good
    Please Login or Register  to view this content.
    Now it's just a question of playing around with it.
    Thanks for sharing, wonder if this helps the OP

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Need help on Frame Scroll Bar ScrollHeight

    I’m confused, you don’t need to do this if you’re using the scroll bars on the text box. It’s built in?

    I doubt it will help the op, they’ve got a tendency towards the obscure and difficult ways of doing things

  12. #12
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by Kyle123 View Post
    You didn't answer Keebellah
    ...because I have been sick for almost a week. I'm working through the posts now.
    Last edited by mc84excel; 04-17-2019 at 02:00 AM.

  13. #13
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by Kyle123 View Post
    As an aside, you don't really need an expert for this, it's pretty trivial, consider:
    Please Login or Register  to view this content.
    I don't know what is going wrong but it didn't show any scroll bars.

    I tried putting this code into a fresh workbook. The form shows the lorem ipsum when clicked but no scroll bars - neither on the frame nor the textbox.

  14. #14
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by Keebellah View Post
    wonder if this helps the OP
    Hi Keebellah, I tried it but it doesn't show any scroll bars for me.

  15. #15
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by Keebellah View Post
    Could you attach a sample file with the userform and a record or two with the text you want displayed and the macros you have so far?
    Hmm I will upload a test copy soon. FWIW I did leave instructions in the OP on how to easily replicate the userform (the code does all the tedious stuff) and the text itself isn't important - any text will do for testing provided that it is long!

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by mc84excel View Post
    ...because I have been sick for almost a week. I'm working through the posts now.
    I was referring to his post on the 7th, the one that you bumped immediately after. I’m not sure why the code doesn’t work for you, did you turn text wrap on and autoheight as Keebellah explained?

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Need help on Frame Scroll Bar ScrollHeight

    Let's wait for the test copy and then see what can be done, it's all quite unclear

  18. #18
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by Keebellah View Post
    I know you said you had a scroll bar, but the textbox adds it's own scroll bar if the text exceeds the textframe
    That works. Thanks & reps

  19. #19
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by BellyGas View Post
    Forget frames. In fact look at this gently swinging pocket watch......foooooorget fraaaaaaaames. You are feeling sleepy. Forget fraaaaaames.

    Ok, now replace the frame you've just forgotten about with a text box. When you put text in it it will automatically add scrollbars which equate to the amount of text that is displayed.
    3, 2, 1 and you're back!

    Hey, that works! Thanks BellyGas. Reps.

  20. #20
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Quote Originally Posted by Kyle123 View Post
    I’m confused, you don’t need to do this if you’re using the scroll bars on the text box. It’s built in?
    After I dropped the scrollbar off the Frame and enabled it on the TextBox, that fixed it for me.

    I have no idea why the code in posts 9 & 10 did not work for me but that doesn't matter now.

    Quote Originally Posted by Kyle123 View Post
    I doubt it will help the op, they’ve got a tendency towards the obscure and difficult ways of doing things
    ...thanks for that. We all had to learn at one point.

    In this case, it was a classic example of the old XY problem. I was trying to get the scrollbars on the Frame to sync with the contents of the Textbox. When I should have been checking if TextBox had a scrollbar and if that could be used instead.

    In case anybody is curious as to how I ended up using this "obscure and difficult" method. I started off trying to fake scrollbars on a Label (see thread http://www.excelforum.com/excel-prog...user-form.html which lead me to use scrollbars on the Frame. Then I discovered that a Label was no good for me as it couldn't display enough text. So I switched to TextBox but wasn't aware that TextBox control had its own scrollbar.

  21. #21
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Need help on Frame Scroll Bar ScrollHeight

    Demo workbook attached

    UPDATE: Change made since uploading. Edited the form code so that the last textbox code is
    Please Login or Register  to view this content.
    This to force the scrollbar to start from the top.
    Attached Files Attached Files
    Last edited by mc84excel; 04-17-2019 at 03:02 AM.

  22. #22
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Need help on Frame Scroll Bar ScrollHeight

    Glad you got it sorted

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Need help on Frame Scroll Bar ScrollHeight

    Great, happy coding

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Scroll Wheel mouse on a userform frame
    By Laurelb in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 01-10-2017, 07:43 PM
  2. Scroll Wheel mouse on a userform frame for 64 bit OS.
    By hurhey1006 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2016, 02:05 AM
  3. [SOLVED] Looping through controls by frame on userform (frame within a frame)
    By njs27 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2015, 10:21 AM
  4. [SOLVED] Scroll Bar Jumping When frame is selected
    By AdamManning in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2014, 02:40 PM
  5. [SOLVED] Frame and scroll bar related
    By TUNGANA KURMA RAJU in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2006, 03:40 AM
  6. [SOLVED] User Form - frame scroll with wheel mouse and cut & paste from tex
    By Kenjaro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2005, 11:06 PM
  7. MAKE WHEEL MOUSE WORK ON USER FORM FRAME WITH SCROLL BAR HELP!!!!!
    By Kenjaro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2005, 11:06 PM

Tags for this Thread

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