+ Reply to Thread
Results 1 to 19 of 19

Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

  1. #1
    Registered User
    Join Date
    08-06-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    23

    Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Hi guys,

    I'm new to VBA and so spend all day yesterday making a user form that effectively works as a register/roll call. So that a manager would be able to select where each of their member of staff was that day and what they were doing. Once finished they pressed 'submit' and the data went into a list in Excel. It worked a treat. But then this morning I tried it and the following happens... every time:

    The form pops up when you open the sheet (as intended) but when you fill it in and press submit it freezes, just showing that windows little blue hollow circle going round and round. Then when you open task manager to shut excel down, as soon as you press end task the usual windows asking if you are sure you want to end task pops up, as well as another window from Excel saying ‘Cannot quit Microsoft Excel’ (with a blue exclamation point and an 'OK' button).

    If you select end task, Excel closes, but if you press cancel and then click ‘OK’ on the Excel message then the message I programmed into visual basic pops up (the one that pops up when you have successfully inserted and submitted the data)… you click OK on that and all of a sudden the userform has worked.

    Can anyone tell me what on earth is going on? Thank you so much in advance.

    Cheers

    Will Register 2.xlsm

  2. #2
    Registered User
    Join Date
    08-06-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Come on guys! I thought this would be a breeze for you boys!

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    You haven't turned screen updating back on, you've just turned it off 7 times

  4. #4
    Registered User
    Join Date
    08-06-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Hi Kyle,

    Thank you for coming back to me on this... would you mind telling me what I should put and where to solve the issue?

    As I mentioned I am really new to this VBA business and don't have much of a clue! Thanks a lot.

    Will

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    You put Application.ScreenUpdating=False at the beginning of the macro and Application.ScreenUpdating=True just before the Msgbox.

    You can delete all the other Application.Screenupdatings since they aren't doing anyhting

  6. #6
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    @willneal

    Sir Kyle said that on your code

    Please Login or Register  to view this content.
    Don't forget to mark your thread as [SOLVED].

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    When I open the file I get 2 file access denied error messages, then Excel crashes.

    When you re-open the workbook Excel has 'repaired' it and when you look at the repair log it says that an object has been removed.

    I did notice on your userform that below the Submit button there was just a blank space.

    Is there meant to be something in that space?

  8. #8
    Registered User
    Join Date
    08-06-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Hi guys,

    Thanks for your help with this. However I think I have done what you have said but I am getting the same problem. Any ideas?

    Please Login or Register  to view this content.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    The problem doesn't appear to be the Application.ScreenUpdating = False.

    When I remove it and add a Application.ScreenUpdating = True then step through the code stops on this line and it's task manager time.
    Please Login or Register  to view this content.

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Hmmm, I don't get the same error as you norie, looks like a copy and paste to a new workbook jobbie?
    Last edited by Kyle123; 10-19-2012 at 09:26 AM.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Kyle

    I just tried that and everything seemed fine until I hit the submit button and Excel was down again.

  12. #12
    Registered User
    Join Date
    08-06-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Did you mention that I should redo the userform in a new Excel file Kyle? Do you think that could solve the issue?

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Well norie says he tried that and it still didn't work, but no harm in having a go

    Are you using some exotic control on the form at all? Should there be anything below the submit button?

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    I've tried various methods of recreating the file but it just seems to crash, normally when I'm halfway through trying something.

    Personally I'd be tempted to recreate from scratch, at least the userform anyway - can't see a problem with copying the data.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    I think I know what the problem is, it's the use of Control Source.

    Every (almost) control on the userform has it's ControlSource property set.

    I don't know why you are using ControlSource as the code put's the values from the controls onto the worksheet.

    Try clearing the ControlSource of all the controls on the form.

    1 Select all the controls (CTRL+A).

    3 Goto the ControlSource property.

    5 Type some nonsense in it and then clear it.
    Attached Files Attached Files
    Last edited by Norie; 10-19-2012 at 10:24 AM.

  16. #16
    Registered User
    Join Date
    08-06-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    There's nothing exotic as far as I am aware...

    My only goal was to create a user form that worked as a register, so that a manager could open up the sheet at the start of each day and just say what everyone was doing. This data would then jump into Excel in a format that I could easily pivot.

    As I mentioned I am totally new to VBA, so I did cobble together various things I found on the internet to make this work and bizzarely it did work! The day I made it, it would perfectly. Then the next day it froze every time I hit the button. It is as though it is still trying to perform a task in some endless loop, but not understanding VBA at all, I cannot see that.

    But from your experience guys, is this how you would copy muliple rows of data into a spreadsheet from a userform in one go? Or is there a much better/less problematic way of doing this?

    Thanks again for your interest in helping me here.

    Cheers

    Will

  17. #17
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Actually, your code's pretty good, it could be shortened a little, but a LOT better than some of the code I see

  18. #18
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Will

    Did you see my last post and attachment?

    Since clearing the ControlSource properties of all the controls the userform has been working just fine.

  19. #19
    Registered User
    Join Date
    08-06-2009
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Created a userform in VBA that worked beautifully yesterday and crashed Excel today!

    Hi Norie,

    Sorry for the delay, I meant to respond earlier to this. Yes I did see it and yes, redoing the whole thing seemed to work a treat. Strange that using the controlsource would have such an effect, I mean, what is the point in having it as an option if its not sustainable? Anyway, thank you sincerely for your time and effort in helping me here. And thank you to all others who contributed. Much appreciated.

    Thanks

    Will

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1