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.
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.
In design mode do right click on the checkbox and select view code and paste the below code.
Exit the design mode and click the check box. Replace the msgbox line with your code.Please Login or Register to view this content.
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
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.
Can you please post your code?
No code is not required just replace all the CheckBox1 with Philip in the suggested code.
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
Please refer post #5 for solution
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?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks