1

Okay, I have an excel macro that processes a lot of data and makes a lot of charts. I inherited the code from a guy that recently retired, while the code functions it is very inefficient so I am rewriting it. One of the things I am trying to fix is he activated/selected everything. I prefer to use references and never select/activate anything.

But I am running into a problem that when I create a new workbook or chart, it will steal focus from the original workbook. This is annoying, because I usually don't add the workbook object in my references for my main workbook.

For example, when I use these lines, the new chart/workbook becomes active

Set wb = Workbooks.Add Or Set wC = wb.Charts.Add

I then use Workbooks(FileName).Activate to reactivate the original workbook

I find it annoying to have to do this every time, and was wondering if there was a way to prevent the new objects from becoming active.

Edit: I just realized that other actions cause the charts to steal focus, like moving the chart with this command wC.Move After:=wb.Worksheets(wb.Worksheets.Count)

Community
  • 1
  • 1
OSUZorba
  • 1,099
  • 11
  • 13
  • Don't think its possible when you use .Add. You can activate your original sheet using Thisworkbook.Activate – neophyte May 13 '14 at 16:13
  • 2
    If you don't want to see the switches, use `Application.ScreenUpdating=False` Nothing you can do to prevent the other objects being activated though. – Tim Williams May 13 '14 at 16:31
  • I always use `Application.ScreenUpdating=False` but I am getting problems elsewhere when I try to use something like `Sheets(1).Cells(3,2)=X` it'll be in the wrong workbook and given an error. I have been adding `Thisworkbook.Activate` everywhere, but I was hoping there was a way to avoid the problem to begin with. – OSUZorba May 13 '14 at 18:55
  • 2
    Better than re-activating your workbook everywhere is to fully qualify all references to include the workbook object. That is simply the best way to avoid errors when dealing with multiple workbooks. – Rachel Hettinger May 13 '14 at 19:21
  • 1
    [This is worth reading](http://stackoverflow.com/a/10718179/2521004) – Automate This May 13 '14 at 20:23
  • Rachel, I agree that probably is the best way of going about it, instead of reactivating the main workbook, just to avoid adding the WB object. Especially since once I got deeper into the code, I found that there are some shapeobjects that will not get put in the correct location unless you make them active and turn on screenupdating, so I am bouncing around much more than I would like (would prefer to never activate anything). – OSUZorba May 23 '14 at 15:29

1 Answers1

1

If you declare an old worksheet after a new one I believe this accomplishes what you want. For example,

Dim newWst As Worksheet
Dim oldWsk As Worksheet

Set newWst = Worksheets.Add
Set oldWst = Worksheets("Sheet1")

Cells(1, 1) = "Test"

worked for me. It added "Test" to the old worksheet.

user3785199
  • 11
  • 3
  • That is an interesting work around, not sure it is better than using Thisworkbook.activate, but it is an alternative. – OSUZorba Jul 25 '14 at 18:02