Private Sub Workbook_Open

  • 8 years ago

    G'day


    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.


    Cheers
    Greg

  • 8 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.

  • 8 years ago
    Hello

    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.

    Regards

    Greg Chilton
  • 8 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).

Contribute

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.

“An expert is a man who has made all the mistakes that can be made in a very narrow field” - Niels Bohr