BENSON
09-30-2009, 10:48 PM
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
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