Create Vertical Scrolling Text in UserForm

[Delhi_IT]

Trying to create a userform in VBA excel where in the text can be scrolled vertically to show what I add in specific cells of excel. I am using version 2016 of excel.

Have taken VBA code from google which is given below:

Private Sub UserForm_Initialize()
Me.Label1.Caption = Sheet1.Range("b4").Value
Me.Label2.Caption = Sheet1.Range("E9").Value & vbCrLf & vbCrLf & Sheet1.Range("E10").Value & vbCrLf & vbCrLf & Sheet1.Range("E11").Value & vbCrLf & vbCrLf & Sheet1.Range("E12").Value & vbCrLf & vbCrLf & Sheet1.Range("E13").Value
Me.Label2.Top = Me.Height
End Sub

This part of code works well when I comment "Me.Label2.Top = Me.Height" this part of the code.

The second part of the code is for Vertical Scroll where in the scrolling happens however the loop where in the scrolling has to be repeated is not working. Code given below:

Sub verti_scroll()
Call UserForm1.Show(vbModeless)

Do
i = UserForm1.Height - 42

Do
i = i - 1
DoEvents
For a = i To 5000000
a = a + 1
Next
UserForm1.Label2.Top = i
If i = 100 Then GoTo Nextz

Loop
Nextz:
x = x + 1
If x = 2 Then GoTo nextx
Loop
nextx:
End Sub

 

[rlv01]

This part of code works well when I comment "Me.Label2.Top = Me.Height" this part of the code.

Change it to something like Me.Label2.Top = Me.Top + 10

however the loop where in the scrolling has to be repeated is not working

Seems functional to me. Number of times banner repeats is controlled by If x = 2 Then GoTo nextx. Increase "x" for more loops.

 

[Delhi_IT]

Change it to something like Me.Label2.Top = Me.Top + 10


Seems functional to me. Number of times banner repeats is controlled by If x = 2 Then GoTo nextx. Increase "x" for more loops.

Thanks so much rlv. Really appreciate your help on this. However, changing the code as you suggested in the first part is working, however it is affecting the alignment of the text in the user form and the second part have increased the counter to 6, but still not working. Some issue may be with the user for heights is impacting the code which I am not able to identify. Any help in this regard would be appreciated. I tried to upload the excel sheet for your quick reference, however not sure how to do this.

 

[Delhi_IT]

Dear RLV, with your suggestion I was able to fix the first part of code. However still struggling with the second part. Would really appreciate if you can help and refine the second part of the code. Have tried many times however that doesn't work somehow.

 

[rlv01]

Give this a try.

Private Sub UserForm_Initialize()
    Dim ScrollRange As Range
    Dim ScrollMsg As String
    Dim I As Long

    Me.Label2.Top = Me.Top - 100
    Me.Label2.Height = Me.Height
    Set ScrollRange = Sheet1.Range("E9:E13")

    For I = 1 To ScrollRange.Rows.Count
        If I < ScrollRange.Rows.Count Then
            ScrollMsg = ScrollMsg & ScrollRange.Cells(I, 1).Value & vbCrLf & vbCrLf
        Else
            ScrollMsg = ScrollMsg & ScrollRange.Cells(I, 1).Value
        End If
    Next I

     Me.Label2.Caption = ScrollMsg

    With Me.Label1
        .Caption = Sheet1.Range("b4").Value
        If Trim(.Caption) = "" Then
            .Caption = "No Data"
        End If
    End With
End Sub

Sub verti_scroll()
    Dim I, a, x, J

    UserForm1.Show (vbModeless)
    Do
        I = UserForm1.Label2.Height
        J = UserForm1.Label2.Top
        Do
            I = I - 1
            For a = I To 300000
                a = a + 1
            Next a
            UserForm1.Label2.Top = I
            If I <= J Then Exit Do
            DoEvents
        Loop
        x = x + 1
        If x = 3 Then Exit Do
    Loop
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Create Vertical Scrolling Text in UserForm
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Delhi_IT]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Create Vertical Scrolling Text in UserForm
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Dear Fluff, Was not aware regarding the cross-post rule. Would make sure to abide by the rules of the Forum.

 

[Delhi_IT]

Give this a try.

Private Sub UserForm_Initialize()
    Dim ScrollRange As Range
    Dim ScrollMsg As String
    Dim I As Long

    Me.Label2.Top = Me.Top - 100
    Me.Label2.Height = Me.Height
    Set ScrollRange = Sheet1.Range("E9:E13")

    For I = 1 To ScrollRange.Rows.Count
        If I < ScrollRange.Rows.Count Then
            ScrollMsg = ScrollMsg & ScrollRange.Cells(I, 1).Value & vbCrLf & vbCrLf
        Else
            ScrollMsg = ScrollMsg & ScrollRange.Cells(I, 1).Value
        End If
    Next I

     Me.Label2.Caption = ScrollMsg

    With Me.Label1
        .Caption = Sheet1.Range("b4").Value
        If Trim(.Caption) = "" Then
            .Caption = "No Data"
        End If
    End With
End Sub

Sub verti_scroll()
    Dim I, a, x, J

    UserForm1.Show (vbModeless)
    Do
        I = UserForm1.Label2.Height
        J = UserForm1.Label2.Top
        Do
            I = I - 1
            For a = I To 300000
                a = a + 1
            Next a
            UserForm1.Label2.Top = I
            If I <= J Then Exit Do
            DoEvents
        Loop
        x = x + 1
        If x = 3 Then Exit Do
    Loop
End Sub

Thanks so much rlv. Really appreciate for all the efforts and help you provided. Thanks a lot. Really appreciate. Thanks

 

[rlv01]

Thanks so much rlv. Really appreciate for all the efforts and help you provided. Thanks a lot. Really appreciate. Thanks

It's great that you appreciate it, but since you had already had someone provide you with a solution over on another Excel forum before I ever posted mine here, my feelings about helping you are mixed.

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →

excelguru.ca

 

[Siddharth Rout]

Just came to know about this now.. This has been cross posted in so many places.

Another one: Create Vertical Scrolling Text in UserForm