Autorun Macro VBA on opening workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-1470476%22%20slang%3D%22en-US%22%3EAutorun%20Macro%20VBA%20on%20opening%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470476%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20Macro%20for%20a%20scrolling%20marquee%20which%20works%20fine%20but%20I'm%20just%20trying%20to%20get%20it%20to%20run%20on%20opening%20the%20workbook.%20Any%20suggestions%3F%20No%20issue%20with%20the%20Macro%2C%20just%20activating%20autorun.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20this%20code%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Workbook_Open()%3CBR%20%2F%3ESub%20DoMarquee()%3CBR%20%2F%3EDim%20sMarquee%20As%20String%3CBR%20%2F%3EDim%20iWidth%20As%20Integer%3CBR%20%2F%3EDim%20iPosition%20As%20Integer%3CBR%20%2F%3EDim%20rCell%20As%20Range%3CBR%20%2F%3EDim%20iCurPos%20As%20Integer%3C%2FP%3E%3CP%3E'Set%20the%20message%20to%20be%20displayed%20in%20this%20cell%3CBR%20%2F%3EsMarquee%20%3D%20%22Welcome.%20There%20is%20NEW%20information%20for%20you%20today.%22%3C%2FP%3E%3CP%3E'Set%20the%20cell%20width%20(how%20many%20characters%20you%20want%20displayed%20at%20once%3CBR%20%2F%3EiWidth%20%3D%2050%3C%2FP%3E%3CP%3E'Which%20cell%20are%20we%20doing%20this%20in%3F%3CBR%20%2F%3ESet%20rCell%20%3D%20Sheet1.Range(%22M2%22)%3C%2FP%3E%3CP%3E'determine%20where%20we%20are%20now%20with%20the%20message.%3CBR%20%2F%3E'%20instr%20will%20return%20the%20position%20of%20the%20first%3CBR%20%2F%3E'%20character%20where%20the%20current%20cell%20value%20is%20in%3CBR%20%2F%3E'%20the%20marquee%20message%3CBR%20%2F%3EiCurPos%20%3D%20InStr(1%2C%20sMarquee%2C%20rCell.Value)%3C%2FP%3E%3CP%3E'If%20we%20are%20position%200%2C%20then%20there%20is%20no%20message%2C%20so%20start%20over%3CBR%20%2F%3E'%20otherwise%2C%20bump%20the%20message%20to%20the%20next%20characterusing%20mid%3CBR%20%2F%3EIf%20iCurPos%20%3D%200%20Then%3CBR%20%2F%3E'Start%20it%20over%3CBR%20%2F%3ErCell.Value%20%3D%20Mid(sMarquee%2C%201%2C%20iWidth)%3CBR%20%2F%3EElse%3CBR%20%2F%3E'bump%20it%3CBR%20%2F%3ErCell.Value%20%3D%20Mid(sMarquee%2C%20iCurPos%20%2B%201%2C%20iWidth)%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E'Set%20excel%20up%20to%20run%20this%20thing%20again%20in%20a%20second%20or%20two%20or%20whatever%3CBR%20%2F%3EApplication.OnTime%20Now%20%2B%20TimeValue(%2200%3A00%3A01%22)%2C%20%22DoMarquee%22%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1470476%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470816%22%20slang%3D%22en-US%22%3ERe%3A%20Autorun%20Macro%20VBA%20on%20opening%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470816%22%20slang%3D%22en-US%22%3EMove%20the%20code%20starting%20from%20%22Sub%20DoMarquee%22%20up%20to%20the%20line%20starting%20with%20Application.OnTime%20to%20a%20normal%20module%20(From%20the%20menu%3A%20Insert%2C%20Module).%20Make%20sure%20both%20subs%20in%20both%20places%20have%20End%20Sub%20at%20their%20ends.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi there, 

 

I have a Macro for a scrolling marquee which works fine but I'm just trying to get it to run on opening the workbook. Any suggestions? No issue with the Macro, just activating autorun. 

 

Thanks.

 

Using this code: 

 

Private Sub Workbook_Open()
Sub DoMarquee()
Dim sMarquee As String
Dim iWidth As Integer
Dim iPosition As Integer
Dim rCell As Range
Dim iCurPos As Integer

'Set the message to be displayed in this cell
sMarquee = "Welcome. There is NEW information for you today."

'Set the cell width (how many characters you want displayed at once
iWidth = 50

'Which cell are we doing this in?
Set rCell = Sheet1.Range("M2")

'determine where we are now with the message.
' instr will return the position of the first
' character where the current cell value is in
' the marquee message
iCurPos = InStr(1, sMarquee, rCell.Value)

'If we are position 0, then there is no message, so start over
' otherwise, bump the message to the next characterusing mid
If iCurPos = 0 Then
'Start it over
rCell.Value = Mid(sMarquee, 1, iWidth)
Else
'bump it
rCell.Value = Mid(sMarquee, iCurPos + 1, iWidth)
End If

'Set excel up to run this thing again in a second or two or whatever
Application.OnTime Now + TimeValue("00:00:01"), "DoMarquee"
End Sub

1 Reply
Highlighted
Move the code starting from "Sub DoMarquee" up to the line starting with Application.OnTime to a normal module (From the menu: Insert, Module). Make sure both subs in both places have End Sub at their ends.