Results 1 to 23 of 23

Need help on Frame Scroll Bar ScrollHeight

Threaded View

  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)

    Option Explicit
    
    Private mvarText As Variant
    Private mblnFormFakeInitialize As Boolean
    
    Public Property Let FormText(ByVal varText As Variant)
        mvarText = varText
    End Property
    
    Private Sub UserForm_Activate()
        ' necessary due to Form Initializing before properties passed
        If Not mblnFormFakeInitialize Then
            Call pFormFakeInitialize
            mblnFormFakeInitialize = True
        End If
    End Sub
    
    Private Sub pFormFakeInitialize()
        Const lngcGAP As Long = 6
        Dim lng As Long
    
        With Me
            ' dynamic size & position
            ' Frame to almost fill the entire form
            .Frame1.Left = lngcGAP
            .Frame1.Width = .InsideWidth - (lngcGAP * 2)
            .Frame1.Top = lngcGAP
            .Frame1.Height = .InsideHeight - (lngcGAP * 2)
    
            'With .Label1
            With .TextBox1
                ' form object to fill Frame
                .Left = 0
                .Top = 0
                .Width = Me.Frame1.InsideWidth
                .Height = Me.Frame1.InsideHeight
    
                ' make TextBox look like a Label
                .BorderStyle = fmBorderStyleNone
                .Locked = True
                .WordWrap = True
                .MultiLine = True
            End With
    
            ' load string to form object
            '.Label1.Caption = mvarText
            .TextBox1.Text = mvarText
    
            'with .Label1
            With .TextBox1
                .Height = .Height + lngcGAP
                .SelLength = 1
            End With
    
            With .Frame1
                .Caption = ""
                'lng = Me.Label1.Top + Me.Label1.Height + lngcGAP
                lng = Me.TextBox1.Top + Me.TextBox1.Height + lngcGAP
    
                .ScrollHeight = lng
                .ScrollBars = fmScrollBarsVertical
            End With
        End With
    End Sub
    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

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

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. 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