Inserting new sheets

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • yvhsb
    New Member
    • Mar 2008
    • 1
    #1

    Inserting new sheets

    Hi, I am using visual basic for the first time to edit a spreadsheet with some macros in it. I've managed to suss a few things, but what has really stumped me is the runtime error 9 message I have. By reading around it looks as though it is to do with the fact that I have inserted worksheets. What do I need to change to enable them to run?

    This is the thread. The highlighted in yellow line is this one:
    Sheets("Budget 1 - Directors ").Visible = False

    Could someone help me add a code so it knows there is other sheets?

    CHeers
    Hannah



    End Sub
    Sub auto_open()
    '
    ' auto_open Macro
    ' Macro recorded 04/04/2007 by Albany
    '
    Application.Scr eenUpdating = False
    ActiveWorkbook. Unprotect password:="vern on"
    Sheets("Listing ").Visible = False
    Sheets("Budget 4 - Young People").Visibl e = False
    Sheets("Budget 3 - Schools").Visib le = False
    Sheets("Budget 2 - 2 Boroughs").Visi ble = False
    Sheets("Budget 1 - Directors ").Visible = False
    Sheets("Purchas e Order Recall").Select
    ActiveSheet.Pro tect DrawingObjects: =True, contents:=True, Scenarios:=True
    Sheets("Index") .Select
    Range("B11:C14" ).Select
    Selection.Clear Contents
    Range("A1").Sel ect
    ActiveWorkbook. Protect password:="vern on", structure:=True , Windows:=False
    Application.Scr eenUpdating = True
    Interaction.Msg Box ("Welcome to your purchase order system. Please only enter data in shaded cells. Make sure codes exactly match those on the code sheet. Do not cut and paste cells or delete formulas. If you have any problems please call finance on x2832. Thank you.")
    Tags: None
    • ubentook
      New Member
      • Dec 2007
      • 58
      #2
      The subscript out of range error probably means that the sheet name is incorrect.
      Is there supposed to be a space after "Directors" ???



      Originally posted by yvhsb
      Hi, I am using visual basic for the first time to edit a spreadsheet with some macros in it. I've managed to suss a few things, but what has really stumped me is the runtime error 9 message I have. By reading around it looks as though it is to do with the fact that I have inserted worksheets. What do I need to change to enable them to run?

      This is the thread. The highlighted in yellow line is this one:
      Sheets("Budget 1 - Directors ").Visible = False

      Could someone help me add a code so it knows there is other sheets?
      CHeers
      Hannah



      End Sub
      Sub auto_open()
      '
      ' auto_open Macro
      ' Macro recorded 04/04/2007 by Albany
      '
      Application.Scr eenUpdating = False
      ActiveWorkbook. Unprotect password:="vern on"
      Sheets("Listing ").Visible = False
      Sheets("Budget 4 - Young People").Visibl e = False
      Sheets("Budget 3 - Schools").Visib le = False
      Sheets("Budget 2 - 2 Boroughs").Visi ble = False
      Sheets("Budget 1 - Directors ").Visible = False
      Sheets("Purchas e Order Recall").Select
      ActiveSheet.Pro tect DrawingObjects: =True, contents:=True, Scenarios:=True
      Sheets("Index") .Select
      Range("B11:C14" ).Select
      Selection.Clear Contents
      Range("A1").Sel ect
      ActiveWorkbook. Protect password:="vern on", structure:=True , Windows:=False
      Application.Scr eenUpdating = True
      Interaction.Msg Box ("Welcome to your purchase order system. Please only enter data in shaded cells. Make sure codes exactly match those on the code sheet. Do not cut and paste cells or delete formulas. If you have any problems please call finance on x2832. Thank you.")

        Comment

        Previous template Next
        Working...