Excel stops working with Userform Textbox populate

Hi All

This issue has been driving me crazy for hours...Can't figure out the cause...
The attached sample file runs perfectly in 2007 but crashes in 2010 and 2016 versions...
On entering the date in Textbox and then trying to enter the start time or end time in other textboxes, excel stops working ... error message appears...and crashes

Pls see attached sample file...can someone please put me out of my misery...

Code for Textboxes as follows:

Please Login or Register  to view this content.

Hi, I'm running 2016 and 2010 but no problems here.

You mention error messages but you do not tell us what they are so no answer for that.
Be more specific.
I entered a data and time values
Here's the pdf and xlsx it produced

Hi Keebellah

Thank you for your time...

It is not a error message per se...
Excel window pops up saying, Excel has stopped working...I do multiple surveys on 2007 no problems and as soon as i make use of two other pc's 2010 & 2016..it crashes.
excelmacrocrash2.png

I have also Repaired both versions of Office on said PC's

The thing is I opened your file with both versions and it works.
There are some compilation errors because Userforms are missing in one or more modules but that does not hurt the file when working with only when compiling.
My OS is 64-bit nut both my Office versions 32 bit.
If you open Excel first and then using file open you open this file, do you get the same problem?

Hi Keebellah

My apologies for the belated response...Completely forgot about thread...Did a repair and all worked well ...For a while...Now problem persists...Have loaded office 2007 and still errors...

Now problem persists

Why?

Hi sintek,

Do you have EnableEvents in Place, Turned AutoCalc to Manual, And checked Your stacking sequence?

Groete.

Hey there Winon...
you know what p..'s me off...This solution works perfectly on my 3 pc's at home ...had issues on clients PC...Did repair, re-installed and all worked well again...No a month down the line it crashes again..
Autocalc is automatice...What is stacking sequence??

Hi sintek,

Stacking sequence is ensuring all your Code fires in the correct order of events, but since it ran on a clients PC for about a month or so, I suspect a Memory issue, which my lead to a Cache overload and subsequent failure. Check out his PC, Memory, Disk Space Processing Speed etc.

Groete.

Thanks bud...will do...Just so blimey confused...
Oh my hat...happens in safe mode aswell

How much RAM is on that PC?

4Gig Quadcore processor...Perhaps the file has become corrupt???

Try running a PC tune-up along with a registry cleaner. How much free disk space does he have on the PC?

Hello sintek,

Your program uses some API calls to remove the Close X from the UserForm. When using the API you have to know which calls work with which systems. Not coding the API correctly can cause subtle issues with program execution. I have moved the API calls from the UserForm into a separate module named "API_Calls". The calls used are also different as they are for use with newer versions of Windows. The attached workbook contains all the updates to the code. This should fix your problems.

API Calls Module Code

Please Login or Register  to view this content.

UserForm Activate Event
The Initialize event has been removed.

Please Login or Register  to view this content.

Hi Leith...

Thanks a mill...Am I missing something...
Untitled-1.gif

Hello sintek,

One of the problems with using API calls designed for 64 bit Windows on a 32 bit system is the keywords PtrSafe ad LongPtr do not exist and cause a compilation error. I redesigned how the API calls are loaded. All of the API procedures and constants are now on a new worksheet "API Code" and divided into two sections. One section for Windows 32 bit API calls and the other for Windows 64 bit API calls.

There is a new module "Load_API_Calls" which determines which API code to use, reads it from the worksheet and copies it into the module "API_Calls". This happens during the UserForm_Initialize event on UserForm2. The call to remove the Close X occurs during the UserForm_Activate event.

This should be almost bulletproof. Give this version of the workbook a try and let me know the results.

Hi Leith

Thank you for the time and effort with this code...Really want to see this one play out. Will assist with all my Userform builds in future...

First error was Programmatic Access To Visual Basic Project Is Not Trusted...Corrected setting under macro settings...
Then an automation error popped up ... The object invoked has disconnected from its clients and excel stops working...

Hello sintek,

Sorry, I should have mentioned changing the Trust settings in my last post. As for the second problem, I understand the error message but don't know where in the code the problem is occurring. Does this the User Form display, even briefly, before the error message?

Error comes up when enabling macros...
No option for debug...When pressing end...microsoft excel has stopped working...
When stepping through the following error comes up...

Step-through.gif

When pressing end...

Step-through-2.gif

Can you attach the actual working file with the code as you have it now?
Do you have a customized ribbon?
Any specific Add-In installed?
You OS is it 64 or 32 bit
Your Office version? 64 or 32 bit?
I'm off for a long weekend so won't be answering before Monday late evening

Hey there Keebellah

Actually just finished removing all the API code and the file tests perfectly...
It is a Survey Solution with 15 Different userforms and lotsa sensitive info so not possible...

No customised ribbon, no add ins, 64 bit windows 2016

Gonna run as is as it tests perfectly...Will keep all future API coding out until i can get Leith code to work flawelessly...

Hello sintek,

The error is caused by the break point. You cannot stop the macro while VBA is editing the VBIDE. If you remove the beak point it should run and not generate that error. You can toggle the break point using the F9 key.

Understood
And the Office is it also 64 bit or 32 bit?

error is caused by the break point.

No breakpoint was put in place...I was just stepping through code
Edit...Nope you right Leith ... I did put a breakpoint in my bad lol...Was trying to see where the code was causing error...it is somewhere within that part...

@ keebellah...32bit

All the way to client all confident in solution...No Api code...bugger that little x lol
Get there and as the form opens and I select a Outlet from combobox it acts up again...
After all tested perfectly for 12 surveys at home ....:

I am at my whits end...so despondent...2 months working on this system and can't use because of problem...

Hi sintek,

Dit maak mens GATVOL!

Just an Idea, three checks. 1. Try setting all Userforms to Non-Modal. 2. Check if any other PC it runs on has more Ram than the PC it fails on. Delete that File from the problem PC. Rename it on another PC and check it on the "Problem" PC.

Holding thumbs.

Jislaaik man? Winon...jy is reg, ek is al so gatvol...lol

Tried that still no go....I even installed Office 2007 on the other PC running Windows 10.
One would think it is the code within the Userform but i have stepped through everything...All is solid.

Hi sintek,

Try om hierdie link te probeer.

Hi All .... Keebellah, Winon, Leith, Lewis

I think this thread merits an update....

I have done the following:

1. Repaired Office 2016
2. Uninstalled and re-installed office 2016
3. Installed Office 2007
4. Removed all API code
5. Remove code form Textbox1 & Textbox2
6. Tried safe mode
7. Check antivirus clashes if any
8. Checked add ins - there were none..
9. Done corrupt workbook repair
10. Checked PC processing
11, ran userforms vb modeless,
12. Final resort...Created new workbook with new sheets, inserted new userforms and copied over code and design...


Now this workbook even crashes on my PC...I give up...
Could the code be exceeding some kind of parameters...

Oh My!

Hope you have a Back-up.

Have you created the "NEW" Workbook in Excel 2007 and tested it in Excel 2016, or Excel 2007?

In VBE select Tools>References>and Check the Box for "Microsoft Visual Basic for Applications Exstensibility 5.3"

Hey Winon

Have Visual Basic For Applications checked...
None avail with ext Exstensibility 5.3

PS created in Windows 7 Office 2007 running windows 10 Office 2007 on other PC

Edit...Weirdest thing though..... Runs perfectly for 15 + surveys then all of a sudden it crashes....

Weirdest thing though..... Runs perfectly for 15 + surveys then all of a sudden it crashes....

It could be related to Active X Controls in your program. Try to avoid them with workarounds like Form Controls.

None are used in the workbook

A voice told me to execute my Save To Folder Code (which houses save to pdf) first before I executed the process and since then all my surveys test flawlessly...29. I created a sub called ThankyouLord and placed it first and foremost...

All is perfect...Can't explain...All I can say is it is a Miracle...
Am marking this one as solved as I know that there is no other code quick fix to make this work...

As was asked several posts earlier, can you attach the working file?
If not than send one of us a pm and maybe share the file privately so we can take a look and see if we can discover the issue that is causing you problems

Hi keebellah

Attached is a solid working sample...Contains everything required to test...
I added a button which allows all option buttons to be populated so that you do not have to tick one at a time...

Thank you for your interest and time on this...

I’ll be home tomorrow evening and will take a look

One thing, does work now or do you still need help?

It miraculously works with this sub...

Please Login or Register  to view this content.

Crashes without it

Conditional compilation using VBA7 would, in my opinion, be a much simpler option than VBE extensibility.

I'll check and see why you can thank the code

Hi sintek and Keebellah,

This Sample Workbook can lead to problems since visibility of the Application has been turned to False, and nowhere back on to True. If you exit the program, Excel keeps running in the background, and that is not good.

This is one hell of a project you have taken on here sintek, and your last added Sub looks fine to me. Well done buddy!

Regards.

Keebellah,

Goeie More,

Geniet dit.!

Groete.

I tried to figure out what could be the issue but I run into several issues due to my system settings. Since you use the system's date settings for the vloopkup I get an error, for me May is Mei and that is mot found so you'll have to see if that could cause problems on your side, I don't know if in Afrikaans there could be other settings if somebody else uses the file an if May is still May.
Another important thing is that you execute a Sub named CheckMe but even if the fields are incomplete it continues.
In these cases you cannot execute Call CheckMe if you've not included an option for 'what to do if the data userform is incomplete' well, simple convert it to a Function that returns a Boolean True/False if False don't continue.
I've attached the modified file and have also left the application window visible for debugging purposes.
Once all that is organized maybe it works like it should. See the modified code for the button 'CAPTURE' .

Oh yes,I hid all the surplus columns and rows in the Template sheet.

I would also suggest that instead of using Afterupdate and OnTime, you use BeforeUpdate thus

Please Login or Register  to view this content.

Thanks to all for latest input...Gonna have a look when i get a gap later today...Right now have a more important matter to fix...Have posted lol

@xlniwit: point of interest, the idea is great but then you will have to account for the not filling in of the date, if you press Exit it will hang on the prompt to enter the date textbox correctly, you either have to include Enable events is false when pressing Exit or another method.

for example:

Please Login or Register  to view this content.

@keebellah,

Enableevents doesn't affect userforms. You can add a simple test to see if there is anything in the control

Please Login or Register  to view this content.

It does affect it here.
If you started to edit the date and get the massage, then decide you do not want to continue and press Exit ... well, try it yourself

Application.Enableevents has no effect on userform events is what I am saying.

Okay, if you say so

Thanks guys

Have sorted both issues....Check Sub and Textbox....All working thus far..Gonna carry on praying...