Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 19th, 2008, 06:39 AM
Authorized User
Join Date: Feb 2007
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default Looping Code 2

below is code that takes detials from a spreadsheet and unloads and reloads and continues like that. my only problem is after a while the text stops scrolling and the application seems to stop but with no errors I am convinced there may be a problem with the loop. Help !

Function Marquees()

        Sheets("Sales Info").Select

        Dim StartTimer As Single, timeWait As Single
        Dim i As Integer
        Dim Marquee As String
        timeWait = 0.08

           'Marquee without trailing spaces
           Marquee = Range("b46")
           Me.txtMarquee.TextAlign = fmTextAlignLeft
           For i = Len(Marquee) To 1 Step -1
              Me.txtMarquee.Text = Right(Marquee, i)
              StartTimer = Timer
              Do While Timer < StartTimer + timeWait
           Next 'i
           'No time-delay here for immediate flow from the right
           Me.txtMarquee.Text = ""

           Me.txtMarquee.TextAlign = fmTextAlignRight
           For i = 1 To Len(Marquee) - 1
              Me.txtMarquee.Text = Mid(Marquee, 1, i)
              StartTimer = Timer
              Do While Timer < StartTimer + timeWait
           Next 'i
           'No time-delay after the full string, for smooth re-flow
           Me.txtMarquee.Text = Mid(Marquee, 1, i)

           Call Displays


End Function

Function Displays()

        lblDate.Caption = Date
        lblDate.Caption = Format(lblDate, "Long Date")


        lblProduction.Caption = Range("a11")
        lblProduction.Caption = Format(expression:=lblProduction.Caption, Format:="00,000")

        lblProductionTarget.Caption = Range("b11")
        lblProductionTarget.Caption = Format(expression:=lblProductionTarget.Caption, Format:="00,000")

        lblPacked.Caption = Range("c11")
        lblPacked.Caption = Format(expression:=lblPacked.Caption, Format:="00,000")


        Sheets("Sales Info").Select

        lblSales.Caption = Range("a41")
        lblSales.Caption = Format(expression:=lblSales.Caption, Format:="00,000")

        lblSalesTarget.Caption = Range("b41")
        lblSalesTarget.Caption = Format(expression:=lblSalesTarget.Caption, Format:="00,000")

        Label15.Caption = Range("c41")
        Label15.Caption = Format(expression:=Label15.Caption, Format:="00,000")


        Sheets("Complaints Info").Select

        lblComplaints.Caption = Range("a18")
        lblComplaintsTarget.Caption = Range("b18")
        Label16.Caption = Range("c18")

        Call Marquees

End Function

Private Sub UserForm_Activate()

Call Displays

End Sub

Private Sub UserForm_Click()
Unload Me
End Sub

Similar Threads
Thread Thread Starter Forum Replies Last Post
Speed up code - looping and copy / paste vba_user Excel VBA 6 March 23rd, 2011 05:27 PM
looping code with if's chimp Excel VBA 0 June 30th, 2008 12:44 PM
Looping Code takwirira Excel VBA 7 April 18th, 2008 04:17 AM
Looping..? dedex C# 2 January 6th, 2005 11:24 PM
How to speed up looping ADO code? llowwelll Pro VB Databases 7 October 24th, 2004 11:12 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.