[SOLVED] Force Display of Vertical Scroll Bars

jpruffle · #1 07-04-2009, 11:12 AM

Hi,

I have created a form that has two textboxes on. It is used to edit existing data that is selected by a combobox on the same form.

Is there a way to force the textboxes to show scroll bars if the text exceeds the textbox height or just have them always shown?

It is difficult to distinguish on some of the data whether the data goes beyond what is visible. Without actually clicking into that textbox the scroll bars will not show.

Is this possible?

Thank you in advance

Andy Pope · #2 07-04-2009, 11:21 AM

From a recent thread
http://www.excelforum.com/excel-prog...in-a-form.html

Code:

Private Sub UserForm_Initialize()

    With TextBox2
        .TabIndex = 0
        .ScrollBars = fmScrollBarsVertical
        .SelStart = 0
    End With
    
End Sub

jpruffle · #3 07-04-2009, 11:40 AM

Thank you for the quick reply.

I did read that thread but wondered if it was possible for two textboxes to display the scrollbars at the same time? As you can only set the focus on one item I assume not?

Thank you in advance

Andy Pope · #4 07-04-2009, 11:48 AM

When the textboxes are preloaded with text the scrollbars get a bit fussy.

If you set the TabIndex to the second textbox and then in the activate event you set focus to the first textbox it appears to work.

Code:

Private Sub UserForm_Activate()
    TextBox1.SetFocus

End Sub

Private Sub UserForm_Initialize()

    With TextBox1
        .WordWrap = True
        .TabIndex = 0
        .ScrollBars = fmScrollBarsVertical
        .SelStart = 0
    End With
    
    With TextBox2
        .WordWrap = True
        .TabIndex = 0
        .ScrollBars = fmScrollBarsVertical
        .SelStart = 0
    End With
End Sub

jpruffle · #5 07-04-2009, 11:57 AM

Again thank you for the quick reply.

I tried your method but unfortunately as the data gets changed and loaded after the form has initialised (i.e. to edit it) it doesn't seem to work.

As an alternative can any code be added so for example if the textbox exceeds 75 in height then show a label "Select for more Info". Or something like that?

Andy Pope · #6 07-04-2009, 12:00 PM

you need to post example as I can not guess the order of events occurring with code I can not see.

jpruffle · #7 07-04-2009, 12:17 PM

The form is part of a very large spreadsheet so I have copied it over to a blank worksheet. However the functionality and data is not present.

On reflection I would almost prefer the ability to add text if the textbox exceeded a given height is that possible anyway?

I have attached my file.

Again thank you in advance.

Andy Pope · #8 07-04-2009, 12:24 PM

Text boxes do not show the scrollbars until they contain text that required scrolling

I don't understand what you mean.

Quote:

I would almost prefer the ability to add text if the textbox exceeded a given height is that possible anyway?

jpruffle · #9 07-04-2009, 12:35 PM

Apologies.

The form I attached can be used to add or edit existing data. It is more often used as a quick reference to look at an issue. So the user simply add an ID number and the relevant data is shown.

As the info and status textboxes do not show scroll bars at a quick glance the user is unable to determine whether there is more information further down without physically clicking into the textbox. I wanted to make that process quicker. I tried your example but I am unsure how when I retrieve the data how to show the scroll bars.

In relation to my second query in regarding textbox height. I was being stupid! I wondered if it might be possible for vba to know if the text inside the box is greater than say the text height. If so then show a label saying that it is, which again would quickly show the user that they need to click into the textbox.

Ultimately I'll tell the user to stop being lazy but thought it'd be worth a try.

Thank you in advance

Andy Pope · #10 07-04-2009, 12:46 PM

This code is the cut down to exclude all the assignments of data I don't have.
It fills the textboxes with rubbish. Reporting linecount in the labels.

Just click the combo box and enter "a"

Code:

Private Sub idlookup_Change()

    Me.taddinfo.Value = "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 17)" & vbLf & _
                        "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 17)" & vbLf & _
                        "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 17)"
    Me.taddresult.Value = "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 19)" & vbLf & _
                       "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 19)" & vbLf & _
                       "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 19)" & vbLf & _
                       "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 19)" & vbLf & _
                       "ws.Range(engageid).Cells(Me.idlookup.ListIndex + 1, 1).Offset(0, 19)"
    
    With Me.taddresult
        .SelStart = 0
        .SetFocus
        Label187.Caption = "Result/Update: [" & .LineCount & " lines]"
    End With
    With Me.taddinfo
        .SelStart = 0
        .SetFocus
        Label186.Caption = "Information: [" & .LineCount & " lines]"
    End With
    
End Sub

jpruffle · #11 07-04-2009, 12:55 PM

Thank you very much that will get me on the right track.

Thanks for your patience

Welcome to the Excel Forum

Forum Statistics