Private Sub Workbook_Open

  • 12 years ago


    I am trying to write a VBA code that formats a cell when the work book opens.  The Code that I am using is:

    Private Sub workbook_open()
    Call Test
    End Sub

    Could you please (if possible) tell me where I am going wrong.


  • 12 years ago

    Hi, a little more info would be helpful. are any macros working for example does this work

    Private Sub Workbook_Open()
    MsgBox "this should work"

    End Sub

    if this doesn't work, it is because you have your security settings set to high in excel.
    Do you get an error saying macros are disabled?

    If this is your problem, go the the Tools menu, select macros, select security and change your setting to medium ( ask whether to run macros). I do not reccoment setting it any lower this this.

    if this is not your problem, please supply a bit more info and i will try to help.

  • 12 years ago

    Yes that worked.  What I am trying to do is format the same cell in the worksheet.  I am trying to do this within a SAP BW context.


    Greg Chilton
  • 12 years ago

    sorry, don't know anything about SAP BW (just did a quick internet search and am still clueless)

    what do you need, code to format cells?

    something like this should work or you can put it in a sub  and call the sub.

    Private Sub workbook_open()

       Range("B3:C4").Select   'range of cells to format
       Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"      'number formatting if you want it
       With Selection.Font                                                          'font formatting
           .Name = "Arial"
           .FontStyle = "Bold"
           .Size = 14
           .Strikethrough = False
           .Superscript = False
           .Subscript = False
           .OutlineFont = False
           .Shadow = False
           .Underline = xlUnderlineStyleNone
           .ColorIndex = xlAutomatic
       End With
       With Selection.Interior                                         'background colour and pattern formatting.
           .ColorIndex = 4
           .Pattern = xlGray16
           .PatternColorIndex = xlAutomatic

       End With

    End Sub

    hope that's what you wanted

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.” - Rich Cook