Run macro when workbook is opened

[pure vito]

Hi All,

I'm having a hard time getting this macro to run when the wookbook opens,

I have tried Private Sub Workbook_Open() with no luck here is the macro i'd appreciate any help thanks.

Sub DoMarquee7()

    Dim sMarquee As String
    Dim iWidth As Integer
    Dim iPosition As Integer
    Dim rCell As Range
    Dim iCurPos As Integer
Application.ScreenUpdating = False

Application.Cursor = xlWait
    'Set the message to be displayed in this cell
    sMarquee7 = Now()
        
'Set the cell width (how many characters you want displayed at once
    iWidth = 100
    
    'Which cell are we doing this in?
    Set rCell = Sheets("Pick Form").Range("Q1")
    
    '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, sMarquee7, 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(sMarquee7, 1, iWidth)
    Else
        'bump it
        rCell.Value = Mid(sMarquee7, 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"), "DoMarquee7"
Application.Cursor = xlDefault
Application.ScreenUpdating = True
End Sub

 

[RoryA]

Workbook_Open is the correct event, but the code must be in the ThisWorkbook module of the workbook.

 

[pure vito]

Thank you Rory,

That makes perfect sense now, unfortunately i have hit another snag,

At the bottom of the code we have this,

Application.OnTime Now + TimeValue("00:00:01"), "DoMarquee7"

so because iv'e changed from Sub DoMarquee7 () to Private Sub Workbook_Open()

I think in need to change this part of the code but i don't know what to change it to if i could ask for your help again that would be great.

 

[RoryA]

Leave the code as it was, then just make the Workbook_Open sub call DoMarquee7

 

[pure vito]

Thank you Rory but i'm having trouble implementing this change appreciate the help though thank you

 

[RoryA]

Leave the code as you originally had it (I'm assuming it was in a normal module), then in the ThisWorkbook module use:

Private sub Workbook_Open()
DoMarquee7
End Sub

 

[pure vito]

That's done the trick thanks for taking the time Rory very much appreciated