+ Reply to Thread
Results 1 to 3 of 3

Excel acting strange after macro completes - ActiveSheet not shown

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    South
    MS-Off Ver
    Excel 2007
    Posts
    2

    Excel acting strange after macro completes - ActiveSheet not shown

    I realize that "strange" isn't very descriptive - but the problem isn't easily described:

    I have a nice little suite of macros, and most of them are fine. Two features of these macros, however, are pretty intense -
    Excel changes after I run said features.

    Excel accepts clicks, etc. - it isn't "not responding" in the crashing sense. But it isn't responsive. The sheet it shows you is usually the sheet you started from. If you click on a cell, nothing about 'the view' changes. If you type, nothing about 'the view' changes.

    That said, if you save, close (all open workbooks), and reopen - the thing you typed will be on the sheet the macro made active, about where you clicked. The issue is gone.

    While Excel is acting up, you can't scroll either. The ribbon is unresponsive -
    Except for the file button. If you click on the file button a couple times, Excel blanks out. Mostly - here's a screen shot:
    screenshot.jpg

    As you can see - the font name and size are still showing. And there's a paste options icon. And the rest is white. No title bar, nothing.
    If you click again, the file menu appears. If you hide that menu, Excel reappears - on the active sheet. You can scroll, update, etc. ... but sometimes it just stops, and you have to click the file menu some more.

    My first thought was that Application.ScreenUpdating = True wasn't working. But running MsgBox Application.ScreenUpdating returns True.
    In my search for answers, I've seen that something similar will happen when you unprotect a worksheet with VBA - a recognized bug. But these macros don't unprotect worksheets ...

    Now I'm thinking it's a memory thing. An Excel limit. Unfortunately, I have to use 32 bit - we use a few other programs that interface with Excel, and they require 32 bit - so maybe Excel is just ... overloaded?

    Obviously, I can have the macro save, then close all workbooks. I can tell my users with a MsgBox to reopen. I assume there isn't way to reopen via macro, as EVERY workbook must be closed at the same time to completely remove the bug - including PERSONAL.
    That solution isn't ideal; I don't like leaving work for the user.

    I'm really hoping someone has some ideas? Is reminded of a bug with a work-around, maybe?

    I've tried:
    (His problem is similar ... not quite the same ... he says a wait message before turning off ScreenUpdating solved his issue. I tried this, DoEvents ... I tried one, then both before and after turning it on ... no combination of any of that worked -)
    https://www.experts-exchange.com/que...-updating.html

    (His problem is also similar ... his solution (selecting a cell on the page that wasn't updating properly) didn't work for me.
    http://www.ozgrid.com/forum/showthread.php?t=176432

    My macros are huge (one module has 5000+ lines) and posting ALL my code here is very impractical.
    (I can post more, of course ... if someone thinks that will help.)

    Here's how I open:
    Please Login or Register  to view this content.
    Here's how I close:
    Please Login or Register  to view this content.
    Last edited by Zars01; 05-25-2016 at 03:12 PM.

  2. #2
    Registered User
    Join Date
    02-14-2013
    Location
    Dallas TX
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Excel acting strange after macro completes - ActiveSheet not shown

    Probably related to a Add-in, this very common in Excel 2013 and is related to memory. try increasing the memory allocation setting Excel to LrgWorkbook (below youll have to find the code for 2013). another option is saving, opening in read-only in a different instance but that will have probably have half the performance.

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Last edited by fbplaya02; 05-25-2016 at 03:29 PM.

  3. #3
    Registered User
    Join Date
    08-07-2012
    Location
    South
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Excel acting strange after macro completes - ActiveSheet not shown

    Thank you!

    I usually stick to VBA so you're response was a bit over my head ... I did a little digging though and the LARGEADDRESSAWARE flag is perfect.

+ 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. [SOLVED] Dynamic named range - INDEX - acting strange!
    By Geoff. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2015, 07:47 PM
  2. [SOLVED] "End" button acting strange
    By dcoates in forum Excel General
    Replies: 0
    Last Post: 08-08-2014, 03:27 PM
  3. A strange nonlinear problem or question has shown up again.
    By mel. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2013, 05:38 PM
  4. Subroutine is halted, but Excel acting strange - Cells don't appear selected...
    By paulcola in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 05-05-2013, 08:00 PM
  5. Pausing macro until .xla function completes
    By rtylerdunn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-16-2007, 05:57 PM
  6. Excel acting strange
    By mrgeek2u in forum Excel General
    Replies: 5
    Last Post: 06-07-2007, 04:54 PM
  7. Excel macro acting differently on two machines
    By michaelw00d in forum Excel General
    Replies: 8
    Last Post: 06-06-2007, 08:20 AM

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