Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
I am trying to accomplish the following using checkboxes on a userform
1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
Ctrl is not delared
Did you try replacing the below line If InStr(Ctrl.Caption, "@") 0 Then with If InStr(CBCtrl.Caption, "@") 0 Then If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I am trying to accomplish the following using checkboxes on a userform 1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
Jacob,
I tried that, the message box strreceipients still showed the4 email messages plus the "Use automated message" label. I had changed it back to your original. "Jacob Skaria" wrote: Ctrl is not delared Did you try replacing the below line If InStr(Ctrl.Caption, "@") 0 Then with If InStr(CBCtrl.Caption, "@") 0 Then If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I am trying to accomplish the following using checkboxes on a userform 1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
I am sure you are doing something wrong..Fine Let us look at this in another
way. Right click the check box with email and from properties you will find a property call Tag. Type the word "Email" (without quotes) to the tag property of all checkboxes with email address and use the below code..... Private Sub CommandButton1_Click() Dim CBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String For Each CBCtrl In RndmemailFrm.Controls If CBCtrl.Tag = "Email" Then If CBCtrl.Object.Value = True Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If Next MsgBox Mid(strReceipients, 2) End Sub If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: Jacob, I tried that, the message box strreceipients still showed the4 email messages plus the "Use automated message" label. I had changed it back to your original. "Jacob Skaria" wrote: Ctrl is not delared Did you try replacing the below line If InStr(Ctrl.Caption, "@") 0 Then with If InStr(CBCtrl.Caption, "@") 0 Then If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I am trying to accomplish the following using checkboxes on a userform 1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
It is still adding the non email text to the string. I even tried swapping the If cbctrl.tag and the If cbctrl.object.value lines but it is still putting all checkbox captions in the string "Jacob Skaria" wrote: I am sure you are doing something wrong..Fine Let us look at this in another way. Right click the check box with email and from properties you will find a property call Tag. Type the word "Email" (without quotes) to the tag property of all checkboxes with email address and use the below code..... Private Sub CommandButton1_Click() Dim CBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String For Each CBCtrl In RndmemailFrm.Controls If CBCtrl.Tag = "Email" Then If CBCtrl.Object.Value = True Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If Next MsgBox Mid(strReceipients, 2) End Sub If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: Jacob, I tried that, the message box strreceipients still showed the4 email messages plus the "Use automated message" label. I had changed it back to your original. "Jacob Skaria" wrote: Ctrl is not delared Did you try replacing the below line If InStr(Ctrl.Caption, "@") 0 Then with If InStr(CBCtrl.Caption, "@") 0 Then If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I am trying to accomplish the following using checkboxes on a userform 1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
Jacob,
Firstly, thank you for all the help with this Secondly, for information purposes, the code resides under the worksheet, in the worksheet section under selection change "Jacob Skaria" wrote: I am sure you are doing something wrong..Fine Let us look at this in another way. Right click the check box with email and from properties you will find a property call Tag. Type the word "Email" (without quotes) to the tag property of all checkboxes with email address and use the below code..... Private Sub CommandButton1_Click() Dim CBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String For Each CBCtrl In RndmemailFrm.Controls If CBCtrl.Tag = "Email" Then If CBCtrl.Object.Value = True Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If Next MsgBox Mid(strReceipients, 2) End Sub If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: Jacob, I tried that, the message box strreceipients still showed the4 email messages plus the "Use automated message" label. I had changed it back to your original. "Jacob Skaria" wrote: Ctrl is not delared Did you try replacing the below line If InStr(Ctrl.Caption, "@") 0 Then with If InStr(CBCtrl.Caption, "@") 0 Then If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I am trying to accomplish the following using checkboxes on a userform 1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
The problem was I was changing the code in the wrong place. All is well now
thank you for your help "thomas donino" wrote: Jacob, Firstly, thank you for all the help with this Secondly, for information purposes, the code resides under the worksheet, in the worksheet section under selection change "Jacob Skaria" wrote: I am sure you are doing something wrong..Fine Let us look at this in another way. Right click the check box with email and from properties you will find a property call Tag. Type the word "Email" (without quotes) to the tag property of all checkboxes with email address and use the below code..... Private Sub CommandButton1_Click() Dim CBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String For Each CBCtrl In RndmemailFrm.Controls If CBCtrl.Tag = "Email" Then If CBCtrl.Object.Value = True Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If Next MsgBox Mid(strReceipients, 2) End Sub If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: Jacob, I tried that, the message box strreceipients still showed the4 email messages plus the "Use automated message" label. I had changed it back to your original. "Jacob Skaria" wrote: Ctrl is not delared Did you try replacing the below line If InStr(Ctrl.Caption, "@") 0 Then with If InStr(CBCtrl.Caption, "@") 0 Then If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I am trying to accomplish the following using checkboxes on a userform 1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
differentiating between checkboxes
Worksheet selection change do not have any thing to with form selection
change... To try the below... In a new workbook..launch VBE..insert a UserForm..place some controls...Also place a command button..Double click command button and place the below code under click event..If you wold like to send the workbook mail that to jacs_jay at y dot com replace y with the y .... a ...h ..o... o ... If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: Jacob, Firstly, thank you for all the help with this Secondly, for information purposes, the code resides under the worksheet, in the worksheet section under selection change "Jacob Skaria" wrote: I am sure you are doing something wrong..Fine Let us look at this in another way. Right click the check box with email and from properties you will find a property call Tag. Type the word "Email" (without quotes) to the tag property of all checkboxes with email address and use the below code..... Private Sub CommandButton1_Click() Dim CBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String For Each CBCtrl In RndmemailFrm.Controls If CBCtrl.Tag = "Email" Then If CBCtrl.Object.Value = True Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If Next MsgBox Mid(strReceipients, 2) End Sub If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: Jacob, I tried that, the message box strreceipients still showed the4 email messages plus the "Use automated message" label. I had changed it back to your original. "Jacob Skaria" wrote: Ctrl is not delared Did you try replacing the below line If InStr(Ctrl.Caption, "@") 0 Then with If InStr(CBCtrl.Caption, "@") 0 Then If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I am trying to accomplish the following using checkboxes on a userform 1. Scan all the check boxes (there are currently 5, 4 with email addresses) 2. Create a string with the email addresses in the checkboxes that are checked i am doing this by looking for the "@" in the string The code below is doing that EXCEPT it is also picking up the string from the non email checkbox It is operating from a command button Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim RBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'check to see what checkboxes are checked and add that email to the string in recipients For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(Ctrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If Next MsgBox strReceipients End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Differentiating Case Sensitivity in a Vlookup | Excel Discussion (Misc queries) | |||
Help differentiating numbers and dates | Excel Discussion (Misc queries) | |||
Differentiating Cell Drag Copy | Excel Programming | |||
differentiating between formulas and values | Excel Discussion (Misc queries) | |||
differentiating between "and"/"or" in a formula | Excel Worksheet Functions |