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

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
>

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
> >

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
> > >

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
> > > >

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
> > > >

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
> > > > >

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
> > > > >