Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Seo Services company Manchester

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 06-08-2010, 04:52 AM
jaimie1664 jaimie1664 is offline
Registered User
 
Join Date: 20 Apr 2010
Location: london
MS Office Version:Excel 2003
Posts: 10
jaimie1664 is becoming part of the community
VBA CheckBox Problems

Please Register to Remove these Ads

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
Reply With Quote
  #2  
Old 06-08-2010, 05:10 AM
Bob Phillips's Avatar
Bob Phillips Bob Phillips is offline
Valued Forum Contributor
 
Join Date: 03 Sep 2005
Location: Wessex
Posts: 710
Bob Phillips is attaining expert status Bob Phillips is attaining expert status
Re: VBA CheckBox Problems

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
Reply With Quote
  #3  
Old 06-08-2010, 05:18 AM
jaimie1664 jaimie1664 is offline
Registered User
 
Join Date: 20 Apr 2010
Location: london
MS Office Version:Excel 2003
Posts: 10
jaimie1664 is becoming part of the community
Re: VBA CheckBox Problems

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?
Reply With Quote
  #4  
Old 06-08-2010, 06:45 AM
Bob Phillips's Avatar
Bob Phillips Bob Phillips is offline
Valued Forum Contributor
 
Join Date: 03 Sep 2005
Location: Wessex
Posts: 710
Bob Phillips is attaining expert status Bob Phillips is attaining expert status
Re: VBA CheckBox Problems

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
Reply With Quote
  #5  
Old 06-08-2010, 06:47 AM
Bob Phillips's Avatar
Bob Phillips Bob Phillips is offline
Valued Forum Contributor
 
Join Date: 03 Sep 2005
Location: Wessex
Posts: 710
Bob Phillips is attaining expert status Bob Phillips is attaining expert status
Re: VBA CheckBox Problems

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
Reply With Quote
  #6  
Old 06-08-2010, 07:52 AM
jaimie1664 jaimie1664 is offline
Registered User
 
Join Date: 20 Apr 2010
Location: london
MS Office Version:Excel 2003
Posts: 10
jaimie1664 is becoming part of the community
Re: VBA CheckBox Problems

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
Reply With Quote
  #7  
Old 06-08-2010, 08:02 AM
snb snb is offline
Forum Contributor
 
Join Date: 09 May 2010
Location: Europe
MS Office Version:Excel 2003
Posts: 660
snb is very confident of their ability snb is very confident of their ability snb is very confident of their ability snb is very confident of their ability
Re: VBA CheckBox Problems

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
Reply With Quote


Reply

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Forum Jump