CODEWITHSUNDEEP
excelchartsvba
OSUZorba
OSUZorba

Reputation: 1129

Prevent excel from activating new objects (Sheets, Charts, Workbooks)

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)

Upvotes: 1

Views: 351

Answers (1)

user3785199
user3785199

Reputation: 11

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.

Upvotes: 1

Related Questions