Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF isnt working right..Why? | Excel Worksheet Functions | |||
Conditional Formatting isnt working right | Excel Discussion (Misc queries) | |||
autofilter isnt working | Excel Discussion (Misc queries) | |||
AutoFill isnt working? | Excel Discussion (Misc queries) | |||
Selection.count isnt working | Excel Programming |