I agree with Rick and have used his technique in the past. Assigning the
long string to the ControlTipText property is an easy and clean way to still
make your message available. To me, a clean interface is critical for end
users.
However, having said that, I thought I recalled John Walkenbach having an
example in his Excel 2007 Power Programming with vba. I was right, but tried
unsuccessfully for a couple of hours to cobble something together that would
work using the method I mentioned above.
I finally broke down and retrieved from my library of Walkenbach books the
answer. I was right in remembering an example. I finally modified my close
but no cigar method and used insights from Walkenbach's example and got it to
"work". I was unsuccessful even after getting it to scroll, to control the
speed of the scrolling. It is unreadable because of the speed that it
refreshes the label. And, it also required a control on the UserForm to
initiate and stop the scrolling. I could not discover a way to cause it to
scroll without it. Perhaps, and I didn't try it, use a mouseover event for
the initiation.
All the code resides in the UserForm, and follows:
Option Explicit
Dim myString As String
Dim myStringLen As Integer
Dim myLabel As String
Dim myLabelLeft As String
Dim myLabelRight As String
Dim myCaption As String
Dim myCaptionLeft As String
Dim myCaptionRight As String
Dim x As Integer
Dim myInterval As Integer
Dim Stopped As Boolean
Private Sub cmdStartStop_Click()
myString = "Let me see if I can think of some long string to scroll. "
myStringLen = Len(myString)
myLabel = myString
myCaption = myString
myInterval = 7
If cmdStartStop.Caption = "Start" Then
cmdStartStop.Caption = "Stop"
Stopped = False
Do Until Stopped
For x = 1 To myStringLen
'Display controls
lblScrollText = myLabel
txtX = x
txtLabelLength = myStringLen
frmScrollText.Caption = myCaption
'Change myLabel
myLabelLeft = Left(myLabel, myInterval)
myLabelRight = Right(myLabel, myStringLen - myInterval)
myLabel = myLabelRight & myLabelLeft
'Change myCaption
myCaptionLeft = Left(myCaption, myStringLen - myInterval)
myCaptionRight = Right(myCaption, myInterval)
myCaption = myCaptionRight & myCaptionLeft
DoEvents 'Causes the animation
Next
Loop
Else
Stopped = True
cmdStartStop.Caption = "Start"
End If
End Sub
--
Mark Trevithick
Rick Rothstein said:
See inline comments...
Is the ControlTipText thing sort of like a floating text box
kinda similar to the comment-box that you can install in Excel
cells?? This might be what I'm looking for.
Yes, but unlike the Comment Box which activates immediately, there is a
slight delay before the ControlTipText displays.
BTW, if another application is in the foreground, and Excel
is behind/underneath it (but partially visible), AND if I hover
the mouse pointer over my userform (which is in the background),
will the ControlTipText still appear??? (I hope that question
isnt too confusing, hehe!)
No, the ControlTipText will only activate if the UserForm has focus (this is
pretty much standard Windows functionality).
