+ Reply to Thread
Results 1 to 9 of 9

Questions regarding Active x Checkbox

  1. #1
    Registered User
    Join Date
    06-18-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    46

    Questions regarding Active x Checkbox

    I made an Active x Checkbox called Philip. Im now wondering how i can access this value in vba to say if the checkbox is True (on) do this.....

    thanks.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Questions regarding Active x Checkbox

    In design mode do right click on the checkbox and select view code and paste the below code.

    Please Login or Register  to view this content.
    Exit the design mode and click the check box. Replace the msgbox line with your code.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    06-18-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Questions regarding Active x Checkbox

    Hi SixSenth, i did what you said but i get a compiler error at the if statement saying the variable is not defined. Do i have to declare it somehow?
    Last edited by behnam; 06-19-2013 at 07:30 AM.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Questions regarding Active x Checkbox

    Can you please post your code?

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Questions regarding Active x Checkbox

    No code is not required just replace all the CheckBox1 with Philip in the suggested code.

  6. #6
    Registered User
    Join Date
    06-18-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Questions regarding Active x Checkbox

    If CheckBox1.Value = True Then

    With ActiveSheet
    If .FilterMode Then
    .ShowAllData
    End If
    End With


    Range("A1").AutoFilter Field:=6, Criteria1:="Philip"


    'Working in Excel 2000-2013
    'Mail a copy of the ActiveWorkbook with another file name
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm


    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .to = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Report for the Production Meeting for Philip"
    .Body = "Report"
    .Attachments.Add TempFilePath & TempFileName & FileExtStr
    'You can add other files also like this
    '.Attachments.Add ("C:\test.txt")
    .Display 'or use .Send
    End With
    On Error GoTo 0

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End If


    The code works i tried it with cell values, like if cell x1 value is y, send the email so thats not the problem, i just want to make the if statment depend on whether checkbox1 is checked

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Questions regarding Active x Checkbox

    Please refer post #5 for solution

  8. #8
    Registered User
    Join Date
    06-18-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Questions regarding Active x Checkbox

    THe name is CheckBox1 not philip, i changed it once you posted your solution, but its still saying variable is not defined. At the beggining of my code it says option explicit, so do i have to declare it somehow?

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Questions regarding Active x Checkbox

    When you use the Option Explicit statement, you must explicitly declare all variables using the Dim, Private, Public, or ReDim statements. If you attempt to use an undeclared variable name, an error occurs:

    Option Explicit statement must appear in a script before any procedures.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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