Hello Everyone
I am going stir crazy here trying to work out how to get my check box to work. I can get my check box to work if i open a fresh file and put it in and use the following
Sheets.CheckBox1 = True Then
this then picks it up fine. But when i put this into a diffrent spreadsheet where i am using the checkbox to run code i cannot get it to find the checkbox. The checkbox is on the tab ("compile") but no matter what i try i cant get it to work, I have attached the code. please help someone
code below:
Sub compile_with_dims()
Windows("NA West.xlsx").Activate
Sheets("compile").Select
ActiveSheet.Shapes("Check Box 1").Select
If Sheets.CheckBox1 = True Then
Dim Statement As String
Dim info As String
Dim Calcsheet As String
Dim salesperson As String
Dim Vision As String
Dim month As String
Statement = "C:\Documents and Settings\jaimiem\Desktop\P11 Clean up\New Folder\Statements\Bob Irby FY10 commission statement.xlsx"
Calcsheet = "NA West.xlsx"
salesperson = "Bob irby"
info = "Bob Irby FY10 commission statement.xlsx"
Vision = "C:\Documents and Settings\jaimiem\Desktop\P11 Clean up\New Folder\Vison report\NA West March Vision extract.xls"
month = "Jan"
Workbooks.Open Filename:=Statement
Windows(Calcsheet).Activate
Sheets(salesperson).Select
Cells.Select
Selection.Copy
Windows(info).Activate
Sheets("Calculation sheet").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C3").Select
Application.CutCopyMode = False
Windows(info).Activate
ActiveWorkbook.Save
Workbooks.Open Filename:=Vision
Sheets(salesperson).Select
Sheets(salesperson).Copy Before:=Workbooks( _
"Bob Irby FY10 commission statement.xlsx").Sheets(2)
Sheets(salesperson).Select
Sheets(salesperson).Name = month
Sheets("Calculation sheet").Select
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
End If
Bookmarks