+ Reply to Thread
Results 1 to 11 of 11

VBA CheckBox Problems

  1. #1
    Registered User
    Join Date
    04-20-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    VBA CheckBox Problems

    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

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    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)

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-20-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    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?

  4. #4
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: VBA CheckBox Problems

    AS you are selecting the shape, you can use

    Please Login or Register  to view this content.

  5. #5
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: VBA CheckBox Problems

    I meant to add that you can do it without selecting

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-20-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    26

    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

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA CheckBox Problems

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

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-06-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA CheckBox Problems

    Hello everyone!

    Please, help me with my Checkbox.

    I can't change the column width by checkbox.

    Code

    Please Login or Register  to view this content.
    Last edited by arlu1201; 11-06-2012 at 05:40 AM.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA CheckBox Problems

    Nelia,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

    Also,

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  10. #10
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: VBA CheckBox Problems

    Quote Originally Posted by nelia View Post
    Hello everyone!

    Please, help me with my Checkbox.

    I can't change the column width by checkbox.

    Code

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: VBA CheckBox Problems

    Or:
    Please Login or Register  to view this content.
    Or:
    Please Login or Register  to view this content.
    Last edited by HSV; 11-06-2012 at 03:17 PM.
    Kind regards, Harry.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1