Trying to get Code to run in excel 2007

[BENSON]

The macro code posted below works fine in excel 2003 but the last part of the code will not run in excel 2007

 
   ActiveSheet.Buttons("Button 958").Caption = "Press To Update Bom Link To Week 1"
          Application.ScreenUpdating = True
     Application.DisplayAlerts = True

the entire macro code is posted below,
Can anyone help

 
Sub Button958_Click()
       'Sub MessageBox6()
' A message box with Yes/No buttons
    Dim strAnswer As VbMsgBoxResult
    strAnswer = MsgBox("ANDRE HAVE YOU RESET THIS STORE STOCK USAGE SHEET IF NOT PRESS              (CONTROL R)     BEFORE PROCEDING?", vbQuestion + vbYesNo, "Decision time!")
    If strAnswer = vbYes Then
    strAnswer = MsgBox("ANDRE HAVE YOU UPDATED ALL THE WEEKLY STOCK FileS LOCATED ON THE L DRIVE ?        IF NOT DO NOT PROCEED ", vbQuestion + vbYesNo, "Decision time!")
    If strAnswer = vbYes Then
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Select Case ActiveSheet.Buttons("Button 958").Caption
            Case "Press To Update Bom Link To Week 1"
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
            Range("H4").Select
            Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
            Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton5_Click"
            Sheets("SEA POINT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SEA POINT WEEKLY  STOCKSHEET.xls"
            Application.Run "'SEA POINT WEEKLY  STOCKSHEET.xls'!Button123_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton1_Click"
            Sheets("WATERFRONT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
            Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton1_Click"
            Sheets("SOMERSET RD").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SOMERSET RD WEEKLY STOCKSHEET.xls", UpdateLinks:=3
            Application.Run "'SOMERSET RD WEEKLY STOCKSHEET.xls'!Button123_Click"
            ActiveWindow.SmallScroll ToRight:=3
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button13_Click"
            Sheets("GArRDENS").Select
            ActiveWorkbook.Save
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
            ActiveSheet.Buttons("Button 958").Caption = "Press To Update Bom Link To Week 2"
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
            Case "Press To Update Bom Link To Week 2"
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
            Range("H4").Select
            Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
            Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton6_Click"
            Sheets("SEA POINT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SEA POINT WEEKLY  STOCKSHEET.xls"
            Application.Run "'SEA POINT WEEKLY  STOCKSHEET.xls'!Button123_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!SEAPOINT_OptionButton2_Click"
            Sheets("WATERFRONT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
            Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton2_Click"
            Sheets("SOMERSET RD").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SOMERSET RD WEEKLY STOCKSHEET.xls", UpdateLinks:=3
            Application.Run "'SOMERSET RD WEEKLY STOCKSHEET.xls'!Button123_Click"
            ActiveWindow.SmallScroll ToRight:=3
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button14_Click"
            Sheets("GArRDENS").Select
            ActiveWorkbook.Save
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
            ActiveSheet.Buttons("Button 958").Caption = "Press To Update Bom Link To Week 3"
        Case "Press To Update Bom Link To Week 3"
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
            Range("H4").Select
            Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
            Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton2_Click"
            Sheets("SEA POINT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SEA POINT WEEKLY  STOCKSHEET.xls"
            Application.Run "'SEA POINT WEEKLY  STOCKSHEET.xls'!Button123_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton3_Click"
            Sheets("WATERFRONT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
            Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton3_Click"
            Sheets("SOMERSET RD").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SOMERSET RD WEEKLY STOCKSHEET.xls", UpdateLinks:=3
            Application.Run "'SOMERSET RD WEEKLY STOCKSHEET.xls'!Button123_Click"
            ActiveWindow.SmallScroll ToRight:=3
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button15_Click"
             Sheets("GArRDENS").Select
             ActiveWorkbook.Save
             ActiveSheet.Buttons("Button 958").Caption = "Press To Update Bom Link To Week 4"
             Application.ScreenUpdating = True
             Application.DisplayAlerts = True
        Case "Press To Update Bom Link To Week 4"
             Application.DisplayAlerts = False
             Application.ScreenUpdating = False
             Range("H4").Select
            Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
            Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton4_Click"
            Sheets("SEA POINT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SEA POINT WEEKLY  STOCKSHEET.xls"
            Application.Run "'SEA POINT WEEKLY  STOCKSHEET.xls'!Button123_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton117_Click"
            Sheets("WATERFRONT").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
            Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton4_Click"
            Sheets("SOMERSET RD").Select
            Range("H4").Select
            Workbooks.Open Filename:="L:\SOMERSET RD WEEKLY STOCKSHEET.xls", UpdateLinks:=3
            Application.Run "'SOMERSET RD WEEKLY STOCKSHEET.xls'!Button123_Click"
            ActiveWindow.SmallScroll ToRight:=3
            Application.Run _
            "'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button16_Click"
            Sheets("GArRDENS").Select
            ActiveWorkbook.Save
            ActiveSheet.Buttons("Button 958").Caption = "Press To Update Bom Link To Week 1"
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
        End Select
    End If
End If
End Sub

[Bob Phillips]

It works fine for me stand-alone, I cannot test the whole code.

What error is it throwing? Maybe the sheet is protected or that button doesn't exist on the Gardens sheet.

[BENSON]

Run Time error 1004

unable to set the caption property of the button class

[Bob Phillips]

Did you check out my two suggestions?

[BENSON]

Yes button is there and sheet not potected.Just ran it again in excel 2003 worked fine ,tried in excel 2007 works fine does all that is required untill the final line of code to change the caption on the button

[Bob Phillips]

Can you try removing some code until the problem stops (or not), we just cannot test that code.

[tpoynton]

try leaving screenupdating on; it shouldnt make a difference, but it solved a 2003/2007 issue for me in the past. I have no recollection of whether or not what I was doing was similar, but it's easy to try.

[BENSON]

Found that if I manually change the text in button the code will run