Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Horizontal Marquee (Excel VBA 97 SR2)

    This is in reference to <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=39975&page=1&view =collapsed&sb=5&o=0&fpart=>this thread</A> Given that there are so many 'topics' there, I'm starting a new thread on this.

    The case is as follows, A simple text box on a user form, populated by a variable. I would like the text within this box to Marquee.

    can anyone tell me if it's really as complicated as the afforementioned thread suggests? perhaps there is an easier method out there?

    <img src=/S/help.gif border=0 alt=help width=23 height=15> Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    DrkRealm

    I guess your biggest challenge might be in how do you run the code that would update the textbox after the UserForm is displayed. tried an Application.OnTime Now, but many things went wrong.

    I have this code that could be used to update the TextBox:

    Sub StartMarquee()
    Dim sMarquee As String
    Dim iPosition As Integer

    sMarquee = "This is a scrolling Marquee"

    With Me
    With .tbMarquee
    .Text = ""

    For iPosition = 1 To Len(sMarquee)
    .Text = .Text & Mid(sMarquee, iPosition, 1)
    Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1)
    Next iPosition
    End With
    End With

    'Beep
    'Application.OnTime Now + TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 2), "StartMarquee"
    End Sub

    OK so this might need Excel 2002...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    Here's what I did in Word 2000.

    Add a UserForm with a frame and within the frame a label. This contrains the label text.
    Set the label's properties to AutoSize True, WordWrap False

    I found it nicer to make the frame flat with no border but experiment with it.

    Add a button and Caption it 'Start Scroll"

    The lack of a Timer makes you use a couple of For loops. If this Userform is to run at the same time as other things then it will seriously degrade performance because of the loops.

    Add this code to the CommandButton1 control.

    Private Sub CommandButton1_Click()
    Dim i As Long, j As Long

    Label1.Caption = "This is a test of a scrolling label with I hope a lot of text that will be bigger than the frame it is encapsulated within" ' for testing make this text larger than the width of the label

    Label1.Left = Frame1.Width ' start the text from the right end of the frame
    ' you can change this to start the text anywhere within the frame.

    If CommandButton1.Caption = "Start Scroll" Then
    CommandButton1.Caption = "End Scroll"
    For i = 1 To 2500000 ' this value will approximate forever
    For j = 1 To 1000000 ' simulate the Timer
    Next j
    Label1.Left = Label1.Left - 1 ' scroll the text left one pixel
    If Label1.Left <= -Label1.Width Then 'check for the text disappearing
    Label1.Left = Frame1.Width ' start the text at the right side of the frame
    End If
    DoEvents ' update
    Next i
    Else
    End
    End If

    End Sub


    This is quick and dirty but it should give you a starting position. You can experiment with the loop counters and the scrolling amount.

    I hope this helps.

    Kevin Bell

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    DrkRealm

    After some work I got it to work perfectly. This is done in MS-Excel but I guess it can be ported to any VBA enabled Office component.

    Take a look at the attached workbook and let me know what you think.

    Wassim
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    Sorry,

    I forgot to attach the example.

    Cheers,

    Kevin Bell
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    Hey, Kevin,

    Tres cool, man.

    A label in a FRAME? How did you figure that one out?

    So will the incrementing the .top property of textbox in a frame create a vertical marquee???
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    Kevin,

    Not sure it improves your work one bit, but a call to the Sleep API function could replace your J loop. Looks nice anyway...


    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    Hey Kevin,

    I confess, I got lucky. I looked at the API scrolling functions and started with a frame to define the scroll and clip areas. When I added a picture that was larger than the frame I noticed that the picture could be scrolled using the Left or Top properties. I expanded the concept to labels later.

    I use labels rather than text boxes as I don't want anybody to be able to type in the scrolling box.

    I started to write an ActiveX control to do Vertical and Horizontal Marquees but abandoned it after I realised that the concept was so simple that there was no advantage in using a control.

    I still prefer using a timer and adjusting the speed with the amount of scroll. I posted a Zip file with IETimer in the other thread.

    Cheers,

    Kevin Bell

  9. #9
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    Just for you namesake, here's an example of a vertical Marquee based on the same principles. I guess the trick is to scroll the control and not the text in it.

    I also took your suggestion and used the Sleep API call.

    Regards,

    Kevin Bell
    Attached Files Attached Files

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    Would somebody get this guy an MVP.

    Absolutely beautiful. The poem ain't bad either.

    This one is in the toolkit!


    Thanks Kev.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    One more thing to think about...

    You mentioned an ActiveX control: the reason this (or an API technique) might be better is that the OS can maintain the scrolling thus eliminating the While loop. That loop prohibits other controls from taking focus and thus renders the form usable ONLY for the marquee.

    That suits one of my forms just fine (for which I am obviously grateful), but on a busy form the other controls would be useless.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  12. #12
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Horizontal Marquee (Excel VBA 97 SR2)

    I'm not sure what you mean Kevin. See the attached example of a userform with scrolling text and some other controls.

    I will agree that using the OS to scroll is a better solution but that complicates the issue by a power of 10.

    Cheers,

    Kevin Bell
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •