+ Reply to Thread
Results 1 to 13 of 13

Vba design mode

  1. #1
    Registered User
    Join Date
    01-15-2017
    Location
    dallas, tx
    MS-Off Ver
    windows 10
    Posts
    13

    Vba design mode

    I have designed a excel spreadsheet, I added an opening event that was simply
    Private Sub Workbook_Open()
    'Sheets("output").Select
    'Main.Show
    End Sub


    This seems to cause trouble and I don't know why and make excel crash. Once I have recovered the file when I open up a sheet with a icon for calling up a user form, I can't get out of design mode. I have opened the developer made and tried to switch off design mode but it will not. Any help with be greatly appreciated.

    Courtney Hogan

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Vba design mode

    Using this code does not cause any issues here :

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-15-2017
    Location
    dallas, tx
    MS-Off Ver
    windows 10
    Posts
    13

    Re: Vba design mode

    thanks for looking at this, all the code seems to be good to me too but something in my system is making it fail. But ignoring that part I can't seem to be able to close the design mode. Any suggestions or thoughts

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vba design mode

    Hi,

    Do you get a message when you try to disable design mode?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Registered User
    Join Date
    01-15-2017
    Location
    dallas, tx
    MS-Off Ver
    windows 10
    Posts
    13

    Re: Vba design mode

    No I get no message or any movement whatsoever. Let me make sure I am phrasing my problem correctly. After my problem with the opening event, I have a command button on one sheet. When I put my cursor on the button, it does nothing but shows four circles surrounding the button. This seems to indicate to me that the software is in the design mode. So I have opened the developer menu and hit the design mode icon to switch it off. Nothing happens. Thank you for responding.

    It may also be that when the opening event fail it has cause excel to close and I have had time where it has stated excel has had to repair the spreadsheet. Maybe this is the problem. It is a complex spreadsheet that I have several hundred hours into developing so your help is greatly appreciated.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vba design mode

    A few supplementary questions
    Does the design mode icon change when you click it?
    Can you run the button's code manually via f5 in the VB Editor?
    Is the button an ActiveX button or a Form control?

  7. #7
    Registered User
    Join Date
    01-15-2017
    Location
    dallas, tx
    MS-Off Ver
    windows 10
    Posts
    13

    Re: Vba design mode

    The design mode icon does not change when clicked
    When I go and try to run the code manually excel quits and restarts and it shows the software is repaired.
    I believe it is an activex button.

    I have been getting other error messages when I try to reopen that I do not have enough memory. The file size is only 409k, but it is chock full of calculations.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vba design mode

    Which version of Office do you have?

  9. #9
    Registered User
    Join Date
    01-15-2017
    Location
    dallas, tx
    MS-Off Ver
    windows 10
    Posts
    13

    Re: Vba design mode

    Office 2016

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vba design mode

    It sounds to me as if your workbook has perhaps become corrupt, but we can check a few other things first.

    If you add a new commandbutton to the worksheet, does it work or is it also in design mode? Additionally, if you add a Form button to the sheet, can you assign a macro to it and run it successfully?

  11. #11
    Registered User
    Join Date
    01-15-2017
    Location
    dallas, tx
    MS-Off Ver
    windows 10
    Posts
    13

    Re: Vba design mode

    You maybe right. I have a version from a couple of weeks ago that is missing some of the updates I have made since then, but it seems the easier thing to do is to just start updating the prior version and keep more current backups. Any thought why this file may have become corrupt? It seems it all started when I setup an opening event.
    Private Sub Workbook_Open()Private
    'Sheets("output").Select
    main.show
    end sub

    Somehow when I would open it would show an error with Sheets("output").select.

    Thanks very much for your time. Greatly appreciated
    'Sheets("output").Select
    'Main.Show
    End Sub

    'Sheets("output").Select
    'Main.Show
    End Sub

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vba design mode

    In my experience, if you get an error on a simple line of code such as Sheets("output").select and you have activex controls on worksheets, it's due to an office update. When you add an activex control to a worksheet, a temporary .exd file is created in your temp directory that stores information about the control. If the dll that stores the control- usually FM20.DLL- is updated, there can be conflicts between the actual control and the temp file which render the control useless and cause seemingly random errors in your code. The solution is often to remove all the .exd files, which will then be recreated as necessary using the correct DLL. However, if you have different computers with different updates applied all using the same file you can still have problems. If it is possible I would suggest that you replace the activex controls with Form versions.

  13. #13
    Registered User
    Join Date
    01-15-2017
    Location
    dallas, tx
    MS-Off Ver
    windows 10
    Posts
    13

    Re: Vba design mode

    I did not realize there was a difference between activex and form control buttons. I have now changed the buttons and my opening event seems to work and things seem to be ok. Thanks again for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. can't get out of design mode
    By superchew in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2015, 11:12 AM
  2. Design mode
    By endjy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2015, 08:44 PM
  3. Replies: 4
    Last Post: 06-06-2013, 10:21 AM
  4. Design Mode Greyed Out
    By T. Grindlay in forum Excel General
    Replies: 2
    Last Post: 02-04-2012, 10:58 AM
  5. Design Mode
    By juliamh4 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2007, 02:13 PM
  6. [SOLVED] Hyperlinks Inserted in Design Mode inactive once Exited Design Mod
    By Craig in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2005, 01:06 PM
  7. Replies: 0
    Last Post: 01-07-2005, 04:06 PM

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