Why variable isnt working
 

The code below is supposed to check a set of check boxes on a form, if they
are checked, add the strings to the variable (they are email addresses). this
works

The next section checks a radio button and is supposed to do the if then
else but that returns the strings from the check boxes too. I really want
that radio button to be a check box too. How do i differentiate that one from
the others that contain the email addressees?

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 CBCtrl.Object.Value = True Then
strReceipients = strReceipients & ";" & CBCtrl.Caption
End If
End If
Next

' get the randomly generated subject line from column 2
SubLine = Cells(Rnd * (Cells(Rows.Count, 2).End(xlUp).Row - 1) + 2, 2)

' if the Use Automated message is checked, then randomly select a message
from column 3
' or else use the message in the message text box
For Each RBCtrl In RndmemailFrm.Controls
If TypeOf RBCtrl Is MSForms.OptionButton Then
If RBCtrl.Object.Value = True Then
MsgBody = Cells(Rnd * (Cells(Rows.Count, 3).End(xlUp).Row - 1) + 2, 3)
Else
MsgBody = RndmemailFrm.MsgBdyTB.Text
End If
End If

Next
MsgBox MsgBody
End Sub

Why variable isnt working
 

Try the below to differentiate checkboxes with email address

Dim Ctrl As MSForms.Control
Dim strReceipients as String
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Object.Value = True Then
If Instr(Ctrl.Caption,"@") 0 Then
strReceipients = strReceipients & ";" & Ctrl.Caption
End If
End If
End If
Next
MsgBox Mid(strReceipients, 2)


If this post helps click Yes
---------------
Jacob Skaria


"thomas donino" wrote:

The code below is supposed to check a set of check boxes on a form, if they
are checked, add the strings to the variable (they are email addresses). this
works

The next section checks a radio button and is supposed to do the if then
else but that returns the strings from the check boxes too. I really want
that radio button to be a check box too. How do i differentiate that one from
the others that contain the email addressees?

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 CBCtrl.Object.Value = True Then
strReceipients = strReceipients & ";" & CBCtrl.Caption
End If
End If
Next

' get the randomly generated subject line from column 2
SubLine = Cells(Rnd * (Cells(Rows.Count, 2).End(xlUp).Row - 1) + 2, 2)

' if the Use Automated message is checked, then randomly select a message
from column 3
' or else use the message in the message text box
For Each RBCtrl In RndmemailFrm.Controls
If TypeOf RBCtrl Is MSForms.OptionButton Then
If RBCtrl.Object.Value = True Then
MsgBody = Cells(Rnd * (Cells(Rows.Count, 3).End(xlUp).Row - 1) + 2, 3)
Else
MsgBody = RndmemailFrm.MsgBdyTB.Text
End If
End If

Next
MsgBox MsgBody
End Sub

Why variable isnt working
 

Jacob,

The following code still picks up the non email address checkbox text, There
are 4 email checkboxes and 1 non-email check box ,whose text is Use Automated
Message. When the code runs with all check boxes check the string contains
all 4 email addresses AND the "Use automated message stirng

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 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 Skaria" wrote:

Try the below to differentiate checkboxes with email address

Dim Ctrl As MSForms.Control
Dim strReceipients as String
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Object.Value = True Then
If Instr(Ctrl.Caption,"@") 0 Then
strReceipients = strReceipients & ";" & Ctrl.Caption
End If
End If
End If
Next
MsgBox Mid(strReceipients, 2)


If this post helps click Yes
---------------
Jacob Skaria


"thomas donino" wrote:

The code below is supposed to check a set of check boxes on a form, if they
are checked, add the strings to the variable (they are email addresses). this
works

The next section checks a radio button and is supposed to do the if then
else but that returns the strings from the check boxes too. I really want
that radio button to be a check box too. How do i differentiate that one from
the others that contain the email addressees?

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 CBCtrl.Object.Value = True Then
strReceipients = strReceipients & ";" & CBCtrl.Caption
End If
End If
Next

' get the randomly generated subject line from column 2
SubLine = Cells(Rnd * (Cells(Rows.Count, 2).End(xlUp).Row - 1) + 2, 2)

' if the Use Automated message is checked, then randomly select a message
from column 3
' or else use the message in the message text box
For Each RBCtrl In RndmemailFrm.Controls
If TypeOf RBCtrl Is MSForms.OptionButton Then
If RBCtrl.Object.Value = True Then
MsgBody = Cells(Rnd * (Cells(Rows.Count, 3).End(xlUp).Row - 1) + 2, 3)
Else
MsgBody = RndmemailFrm.MsgBdyTB.Text
End If
End If

Next
MsgBox MsgBody
End Sub

Why variable isnt working
 

There is a correction in the code which you pasted. The line should be as
below.
Here we are checking whether the caption contains @ .Do the other checkboxes
caption contain @? If so you can replace @ with something else like. .com or
something like ......If InStr(CBCtrl.Caption, ".com") 0 Then

Dim CBCtrl As MSForms.Control
Dim RBCtrl As MSForms.Control
Dim strReceipients As String
Dim MsgBody As String

For Each CBCtrl In Me.Controls
If TypeOf CBCtrl Is MSForms.CheckBox Then
If CBCtrl.Object.Value = True Then
If InStr(CBCtrl.Caption, "@") 0 Then
strReceipients = strReceipients & ";" & CBCtrl.Caption
End If
End If
End If
Next
MsgBox Mid(strReceipients, 2)

If this post helps click Yes
---------------
Jacob Skaria


"thomas donino" wrote:

Jacob,

The following code still picks up the non email address checkbox text, There
are 4 email checkboxes and 1 non-email check box ,whose text is Use Automated
Message. When the code runs with all check boxes check the string contains
all 4 email addresses AND the "Use automated message stirng

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 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 Skaria" wrote:

Try the below to differentiate checkboxes with email address

Dim Ctrl As MSForms.Control
Dim strReceipients as String
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Object.Value = True Then
If Instr(Ctrl.Caption,"@") 0 Then
strReceipients = strReceipients & ";" & Ctrl.Caption
End If
End If
End If
Next
MsgBox Mid(strReceipients, 2)


If this post helps click Yes
---------------
Jacob Skaria


"thomas donino" wrote:

The code below is supposed to check a set of check boxes on a form, if they
are checked, add the strings to the variable (they are email addresses). this
works

The next section checks a radio button and is supposed to do the if then
else but that returns the strings from the check boxes too. I really want
that radio button to be a check box too. How do i differentiate that one from
the others that contain the email addressees?

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 CBCtrl.Object.Value = True Then
strReceipients = strReceipients & ";" & CBCtrl.Caption
End If
End If
Next

' get the randomly generated subject line from column 2
SubLine = Cells(Rnd * (Cells(Rows.Count, 2).End(xlUp).Row - 1) + 2, 2)

' if the Use Automated message is checked, then randomly select a message
from column 3
' or else use the message in the message text box
For Each RBCtrl In RndmemailFrm.Controls
If TypeOf RBCtrl Is MSForms.OptionButton Then
If RBCtrl.Object.Value = True Then
MsgBody = Cells(Rnd * (Cells(Rows.Count, 3).End(xlUp).Row - 1) + 2, 3)
Else
MsgBody = RndmemailFrm.MsgBdyTB.Text
End If
End If

Next
MsgBox MsgBody
End Sub

Why variable isnt working
 

I wonder if XL is interpreting "@" as a text format instead of the actual
string character. Therefore finding CB5.Caption is text and adding it to the
string. May need to find a different character(s) to compare captions with.

Mike F
"Jacob Skaria" wrote in message
...
There is a correction in the code which you pasted. The line should be as
below.
Here we are checking whether the caption contains @ .Do the other
checkboxes
caption contain @? If so you can replace @ with something else like. .com
or
something like ......If InStr(CBCtrl.Caption, ".com") 0 Then

Dim CBCtrl As MSForms.Control
Dim RBCtrl As MSForms.Control
Dim strReceipients As String
Dim MsgBody As String

For Each CBCtrl In Me.Controls
If TypeOf CBCtrl Is MSForms.CheckBox Then
If CBCtrl.Object.Value = True Then
If InStr(CBCtrl.Caption, "@") 0 Then
strReceipients = strReceipients & ";" & CBCtrl.Caption
End If
End If
End If
Next
MsgBox Mid(strReceipients, 2)

If this post helps click Yes
---------------
Jacob Skaria


"thomas donino" wrote:

Jacob,

The following code still picks up the non email address checkbox text,
There
are 4 email checkboxes and 1 non-email check box ,whose text is Use
Automated
Message. When the code runs with all check boxes check the string
contains
all 4 email addresses AND the "Use automated message stirng

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 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 Skaria" wrote:

Try the below to differentiate checkboxes with email address

Dim Ctrl As MSForms.Control
Dim strReceipients as String
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Object.Value = True Then
If Instr(Ctrl.Caption,"@") 0 Then
strReceipients = strReceipients & ";" & Ctrl.Caption
End If
End If
End If
Next
MsgBox Mid(strReceipients, 2)


If this post helps click Yes
---------------
Jacob Skaria


"thomas donino" wrote:

The code below is supposed to check a set of check boxes on a form,
if they
are checked, add the strings to the variable (they are email
addresses). this
works

The next section checks a radio button and is supposed to do the if
then
else but that returns the strings from the check boxes too. I really
want
that radio button to be a check box too. How do i differentiate that
one from
the others that contain the email addressees?

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 CBCtrl.Object.Value = True Then
strReceipients = strReceipients & ";" & CBCtrl.Caption
End If
End If
Next

' get the randomly generated subject line from column 2
SubLine = Cells(Rnd * (Cells(Rows.Count, 2).End(xlUp).Row - 1) + 2,
2)

' if the Use Automated message is checked, then randomly select a
message
from column 3
' or else use the message in the message text box
For Each RBCtrl In RndmemailFrm.Controls
If TypeOf RBCtrl Is MSForms.OptionButton Then
If RBCtrl.Object.Value = True Then
MsgBody = Cells(Rnd * (Cells(Rows.Count, 3).End(xlUp).Row - 1) +
2, 3)
Else
MsgBody = RndmemailFrm.MsgBdyTB.Text
End If
End If

Next
MsgBox MsgBody
End Sub