PDA

View Full Version : [SOLVED] Trying to get Code to run in excel 2007



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

Bob Phillips
10-01-2009, 12:30 AM
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
10-01-2009, 03:09 AM
Run Time error 1004

unable to set the caption property of the button class

Bob Phillips
10-01-2009, 03:12 AM
Did you check out my two suggestions?

BENSON
10-01-2009, 03:30 AM
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
10-01-2009, 04:18 AM
Can you try removing some code until the problem stops (or not), we just cannot test that code.

tpoynton
10-01-2009, 08:37 AM
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
10-01-2009, 10:30 PM
Found that if I manually change the text in button the code will run