VBA CheckBox Problems

jaimie1664 · #1 06-08-2010, 04:52 AM

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

Bob Phillips · #2 06-08-2010, 05:10 AM

I c an't test it of course, but see if this works (I have tidied up the code a bit as well)

Code:

Sub compile_with_dims()
Dim Statement As String
Dim info As String
Dim Calcsheet As String
Dim salesperson As String
Dim Vision As String
Dim month As String

    Windows("NA West.xlsx").Activate
    Sheets("compile").Select
    ActiveSheet.Shapes("Check Box 1").Select

    If ActiveSheet.CheckBox1.Value = True Then

        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
        With Sheets(salesperson).Cells
        
            .Copy Sheets("Calculation sheet").Range("A1")
            .Value = .Value
        End With
        ActiveWorkbook.Save
        
        Workbooks.Open FileName:=Vision
        With Sheets(salesperson)
            
            .Copy Before:=Workbooks( _
                "Bob Irby FY10 commission statement.xlsx").Sheets(2)
            .Name = month
        End With
        ActiveWorkbook.Save
        ActiveWindow.Close
        ActiveWindow.Close
    End If
End Sub

jaimie1664 · #3 06-08-2010, 05:18 AM

Thanks a lot for you help smartening up the code.

To make my problem easier to see - is have made the code smaller

see below example

Sub jayman()

Windows("NA West.xlsx").Activate
Sheets("compile").Select
ActiveSheet.Shapes("Check Box 1").Select

If ActiveSheet.CheckBox1.Value = True Then

MsgBox "true"
Else
MsgBox "false"

End If
End Sub

I cannot get this to work - I cannot find the check box? is this because i am not using the correct hierarchy?

Bob Phillips · #4 06-08-2010, 06:45 AM

AS you are selecting the shape, you can use

Code:

Sub jayman()
    Windows("NA West.xlsx").Activate
    Sheets("compile").Select
    ActiveSheet.Shapes("Check Box 1").Select
    
    If Selection.Value = 1 Then
    
        MsgBox "true"
    Else
        MsgBox "false"
    
    End If
End Sub

Bob Phillips · #5 06-08-2010, 06:47 AM

I meant to add that you can do it without selecting

Code:

Sub jayman()
    Windows("NA West.xlsx").Activate
    Sheets("compile").Select
    
    MsgBox ActiveSheet.CheckBoxes("Check Box 1").Value = 1
End Sub

jaimie1664 · #6 06-08-2010, 07:52 AM

Thanks very much for your help on this -

I have now found the problem - there are two diffrent codes needed depending on if you are using an ACTIVE or FORMS Checkbox

for Active use ActiveSheet.CheckBox1.Value

for Forms use ActiveSheet.[Check Box 1].Value

I have plugged this in now, and it is all working great -- thank god - took me ages

cheers
Jaimie

snb · #7 06-08-2010, 08:02 AM

You shouldn't use Activesheet at all.
Just refer directly tot what you mean:

Code:

Sub jayman()
    MsgBox Workbooks("NA West.xlsx").Sheets("compile").CheckBoxes("Check Box 1").Value 
End Sub

Welcome to the Excel Forum

Forum Statistics