Excel 2016 Form Checkbox Issues

Question

Works in Excel 2010 and 2013 on different PCs.  Fails in Excel 2016 on both PC and Virtual Desktop environments.

I use check boxes to construct portions of a form letter that gets emailed.

Once emailed, the VBA code clears the checkboxes.  Clicking on the checkbox at this point will update the linked cell correctly and the checkbox.value is correct, but it does not display as checked until you click on something else.  Since the display is wrong, users click it again...now it shows checked, but reads "false".  Email is sending fine.  Everything is initialized before user action and cleared after email.  

My biggest challenge in narrowing this down:

It works fine in 2010 and 2013.  The issue is only in 2016.  BUT, if I comment out the one line of code that actually sends the CDO email, the issue does not occur and the checkboxes work fine (but the email wasn't sent so the whole application becomes pointless).

I've been trying to figure this out for a bit and can't find anything online about it.

Any help is appreciated.

***Post moved by the moderator to the appropriate forum category.***

Answer 1

I know I have been quite a while on this but hopefully you will think it is worth it. It got to me after a while and I just had to find a solution, especially when I could send a stand alone email and the checkboxes were not affected and I decided that it has nothing to do with the email code.

I have combined a Word document and the Excel workbook in the zipped file at the following link on OneDrive. Was easier to write the explanation in Word than to write a long explanation here.

Feel free to get back to me if any problems.

Link to Excel Workbook and Word Document

Answer 2

I could provide you with code to change the Forms Control Check Boxes to ActiveX checkboxes but that will also require a lot of work to change the code throughout the project and it might still not work.

Too much work to do all this without testing if going to work first. Try a test workbook with maybe just one line of ActiveX checkboxes and see if you can send an email and still have the CheckBoxes work as they should.

If they work with ActiveX controls then you can make up your mind if you want to change the entire project. (I don't have the time or inclination to do all of that but I do have code to change the checkboxes so let me know if you want it.).

Another option is to go back to where you got the email code. Was it from Ron de Bruins site? If so, then try contacting him and describe the problem for him and see if he can help.

Link to Ron de Bruin - Select Contact from header

Answer 3

Progress update (or should I say lack of progress update).

The error occurs for me and you are correct in that the error is initiated with the  "CDO_Mail_Object.Send" line. If that line is commented out then all works fine.

I am going to have a look at converting the forms controls to ActiveX controls and see what occurs. I should be able to write code to convert the controls but will require some editing of the VBA code to reference ActiveX controls in lieu of Forms controls.

About 5:30pm in my part of the world and I have other commitments tonight so will get back to you tomorrow.

Answer 4

OssieMac,

Thanks for taking a look at this.

Hopefully I did it right.  The share link is:

https://sayrevillek12-my.sharepoint.com/personal/robert\_jerome\_sayrevillek12\_net/\_layouts/15/guestaccess.aspx?docid=179368b10afed4f999e1a9ac55ed84837&authkey=AdYHEsPekcy0Xn9--iHgLP4&expiration=2017-06-12T02%3a03%3a55.000Z

I stripped down the application but left the fundamentals. 

The intention is to have a tab for each class I teach.  For each student, check what I want to include in the email, then the button will send for that sheet. Next period, switch tabs and continue as needed.

On the Set-Up tab I removed sender and SMTP info, so you'll need to fill that in with your info for testing.  Also, scroll down a bit and enter email address for a few students in class 1 and class 2.  I removed my personal/school, and my students' info.

I know my code is not very elegant as I'm still learning, so any advice is appreciated.

I tested this exact file today and the issue still presents.  With "test mode" deselected the intent to that email will be sent.  In the macro to the "send" button the one critical line seems to be "CDO_Mail_Object.Send" toward the bottom.  When this line is executed...that's when the issue presents.

On the class tabs I added display of the linked cells (to the right of the checkboxes), as this is how I closed in on the issue.  Check a box, have it successfully send an email, then try checking boxes again (same tab or switch) and the displayed checkbox doesn't match the value in the linked cell.

Thanks in advance for your help,

RJ

Answer 5

I can't emulate your problem so can you upload a copy of the workbook to OneDrive and I will have a look at it. Replace any sensitive data with dummy data.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)