Excel 2013 worksheet activate

Question

I have an issue with activating a sheet from a user form, the same code works fine in Excel 2003 to Excel 2010, doesn't work with Excel 2013.

This is how to simply reproduce the issue:

Have a workbook with 2 worksheets in it, called Sheet1 and Sheet2 let's say, and on Sheet1 2 buttons:

On click of Button1 activates Sheet2 worksheet using a Macro1 with 1 line in it:
```
ThisWorkbook.Sheets("Sheet2").Select
```
and I can edit data from it fine.
On click of Button2 a UserForm1 pops up and on click of CommandButton1 call same Macro1 like this:
```
Unload Me
Macro1
```
the Sheet2 worksheet is activated, sort of, but if I edit data in it, it actually updates corresponding cells in Sheet1, if I click on Sheet1 I can see data entered in there!

Clicking back to Sheet2 worksheet activates the Sheet2 sheet properly.

Has anyone seen such behaviour? If yes, are there any coding workarounds to properly activate Sheet2?

i think you might have `application.screenupdating = false` at this time. Also you MUST call the userform like this `userform.show False`. check the sheet name, and be sure to have `on error goto 0`. — Patrick Lepelletier, Nov 23 '14 at 12:58

score 3 · Answer 1 · answered May 30 '14 at 05:40

3

Wow, I was able to reproduce this error using 2013. This is a fantastic failure of Excel's new SDI Interface. What is also interestingly odd is that when you follow the following steps

Select cell A1 on Sheet1
Select cell B2 on Sheet2
Click Button2 which opens the form
Click the CommandButton1 on the form (which hides the form and activates Sheet2)

it looks like cell B2 on Sheet2 is selected, but when you start typing, the text is going into cell A1; Once you hit Enter the text disappears to cell B2 on Sheet1

You almost have to see it to believe it.

The only thing that I've found that works is using the Ontime function.

Private Sub CommandButton1_Click()
    Unload Me
    'Call Macro1
    Application.OnTime Now(), "Macro1"
End Sub

...but I have a feeling that this isn't going to help everyone

answered May 30 '14 at 05:40

Profex

1,370
8
20

1

Note that Excel 2016, which also uses SDI, does not have this problem, so there could be another root cause of this bug. – OfficeAddinDev Sep 26 '15 at 11:16
@MathieuGuindon, I agree with you that nothing should come after `Unload Me`, but as a matter of timing, it needs to be there. I can't say that I've tried to instantiate the form as an object and see if it made any difference. The problem comes from the fact that the Form tied to the specific Workbook/Sheet that was active when it was called, and will reactivate it when unloaded. Maybe using `SetWindowLong hWnd, GWL_HWNDPARENT, hWndXL` to change the parent handle for the form is better, but so far I've only use it for keeping the form on top when switching WorkBooks/Sheets. – Profex Jun 12 '18 at 20:42
I just get the feeling that `Unload Me` unloads the (current) default instance, and then the next statement immediately re-loads it again (assuming executing a statement in an instance method requires, well, an instance), which feels... very, very wrong - at least to me. I just hate that [almost] everyone is just happy with `UserForm1.Show` ("default instance" link above links to my blog article specifically about this). – Mathieu Guindon Jun 12 '18 at 20:45

score 2 · Answer 2 · answered Mar 04 '15 at 19:54

I was able to reproduce the error in Excel 2013 as per Profex's answer. A workaround that solved the issue for me was to make my userform modeless.

userform1.show vbModeless

I understand that this workaround won't be helpful if you require a modal form, but hopefully it will save the next person some time.

Dima Reznikov · Answer 3 · 2015-10-05T23:30:55.560

The workaround for the above Excel 2013 worksheet activation through automation bug is below (c#):

public static void ActivateSheetAndWorkaroundExcel2013VBASheetActivationBug( Worksheet oSheet2Activate )
{
    if( oSheet2Activate.IsNull() )
        return;

    oSheet2Activate.Activate();

    // Excel 2013 has problems activating worksheet through automation
    // https://www.google.com/webhp?ie=utf-8&oe=utf-8#q=excel+2013+worksheet+activate+problem+
    // http://stackoverflow.com/questions/18726141/excel-2013-worksheet-activate
    // 
    // The only way to reset the Excel 2013 state is to hide/show the active window
    if( Application.Version == "15.0" )
    {
        Window oActiveWnd = Application.ActiveWindow;
        oActiveWnd.Visible = false;
        oActiveWnd.Visible = true;
        oActiveWnd.Activate();
    }
}

Call that helper method instead of directly calling oSheet.Activate(). You're welcome:-)

score 1 · Answer 4 · answered Sep 21 '15 at 23:32

1

I found a possible workaround for a similar situation. We have a complex Excel addin that uses both COM and XLL code. (No VBA) We encountered similar issues as above. (When activating a sheet from a modal dialog, the new cell data appeared on the previous sheet).

In our case, we discovered that the issue appeared only when our code evaluated a cell Range's ".HasFormula" property. There's no logical explanation. If our compiled .Net code evaluated that property, we would observe the bug. If the code ignored that property, we did not observe the bug. We also found that if we manually clicked back and forth between worksheets (tabs), the bug went away.

answered Sep 21 '15 at 23:32

DavB.cs

579
5
6

Exact same problem here regarding HasFormula. If I comment out that line, the problem described in the OP goes away. Nice detective work! – OfficeAddinDev Sep 25 '15 at 22:23
Also, this bug appears to have been fixed in Excel 2016. – OfficeAddinDev Sep 26 '15 at 10:15
1

Following up, this HasFormula (and HasArray) issue appears to be related to a bug in Microsoft update KB3085502 released on Sep 8, 2015. Description of the bug at the following link is similar to the problem described in the OP, but obviously cannot be related due to the timing difference: http://answers.microsoft.com/en-us/office/forum/office_2013_release-office_install/microsoft-update-excel-2013-kb3085502-32-bit/0cc7d498-92cb-4478-9554-77cff286c847?page=1 – OfficeAddinDev Oct 07 '15 at 19:43

Joe Laviano · Answer 5 · 2013-09-10T18:51:36.997

0

Sub Macro1()
Sheets("Sheet2").Select
End Sub

Sub Macro2()
Unload Me
Call Macro1
End Sub

This works. I could reproduce your error to an extent, but it may be the location of your macros? (modules vs sheets).

EDIT: I didn't see your comment at first, I have Excel2010 (which is why I couldn't really reproduce it). -Sorry

edited Sep 10 '13 at 18:51

answered Sep 10 '13 at 18:46

Joe Laviano

1,038
7
14

score 0 · Answer 6 · edited May 23 '17 at 11:48

0

Please avoid using .SELECT. You may want to see THIS?

Having said that, if you really want to activate the sheet then use .ACTIVATE

For example

Sheets("Sheet2").Activate

So using that in your code, it will look like this.

Sub Macro1()
    Sheets("Sheet2").Activate
End Sub

Sub Macro2()
    Macro1
    Unload Me
End Sub

edited May 23 '17 at 11:48

Community

1
1

answered Sep 10 '13 at 18:55

Siddharth Rout

147,039
17
206
250

Thanks, Siddharth, I didn't mentioned but I tried Activate as well, same result, doesn't work for 2013, works fine for older versions of Excel (tried with 2003, 2007 and 2010). – user2766088 Sep 11 '13 at 13:51

score 0 · Answer 7 · answered Feb 05 '18 at 23:41

I think Dima Reznikov has the right answer. I used his C# code and did it in VBA and it seems to fix the problem. My personal code also included the use of a Modal UserForm.

Example:

Sub Macro1()
    Dim ws As Worksheet
    Dim win As Window

    '... bunch of code here...

    ws.Activate
    Set win = Application.ActiveWindow
    win.Visible = False
    win.Visible = True
End Sub

Hopefully this helps someone else who is also confused by the C# code.

score -1 · Answer 8 · edited Sep 16 '15 at 20:42

I have the same problem, but my code is actually running in a Excel COM addin, which is slightly different from VBA.

My code worked in 2003,2010... using Sheets("Sheet2").Activate, but not 2013

But I fixed the problem by starting a timer, 100 ms after my event code (Button event).

Then this trimmer opens the file and uses Sheets("Sheet2").Activate. It is then select, just the same as it used to be in old versions.

score -1 · Answer 9 · edited Nov 29 '15 at 14:51

-1

In the worksheet code on worksheet_selectionchange event just put in me.activate.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Select
End Sub

edited Nov 29 '15 at 14:51

Bhargav Rao

50,140
28
121
140

answered Nov 29 '15 at 14:43

Peter Mooren

1

Excel 2013 worksheet activate

9 Answers9

Linked