Excel 2013 worksheet activate

[mce7171]

Hi,

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 some different data in both sheets, in same cells, to observe the issue better, and on Sheet1 have 2 buttons:

On click of Button1 activate Sheet2 worksheet using a Macro1 with 1 line in it:
ThisWorkbook.Sheets("Sheet2").Select

I can edit data in Sheet2 fine in this situation.

On click of Button2 call Macro2 which has 1 line:
UserForm1.Show

a UserForm1 pops up and on click of a 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!
I tried also with Activate method instead of Select, same result, works fine for older Excel version, doesn't work with 2013.

Clicking back from Sheet1 to Sheet2 worksheet activates the Sheet2 sheet properly.

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

Thank you

 

[James Snyder]

I have seen similar behavior in Excel 2010 and got around it by doing an activate of the sheet to be worked on before doing any sheet specific operations. In your case, the .activate would follow the Unload Me.

 

[mce7171]

Hi James,
This is exactly what I'm doing: Unload Me followed by Select or Activate (in the example through call to Macro1, just to show it's same code which works when called directly through Button1, not through the user form, actually in our application we call it directly after Unload Me as thisworkbook.sheets("Sheet2").activate), and that's it, no other actions are done to Sheet2, it expects user input, the Sheet2 gets activated, but then starting to type values in it updates cells in Sheet1! This works fine in 2003 to 2010 versions.
Thank you.

 

[James Snyder]

Have you tried the full path to the object including the workbook?

 

[mce7171]

I'm not sure what you mean, we use ThisWorkbook.Sheets("Sheet2").Select.
Again, the Sheet2 is "selected" (brought to view) but updating behaves very strangely, while you think you edit data in Sheet2, it actually updates values in Sheet1.
If you select a cell in Sheet2, and click on the Formula at the top, the cell refreshes with data of same cell from Sheet1, so you can clearly see that the input is done in Sheet1. Very strange. I think this is a bug in Excel 2013.

Thank you.

 

[James Snyder]

Microsoft would probably call it a "feature", but I have nothing else to offer. Maybe some forum heavyweights will weigh in...

 

[jtsuch]

Hi mce7171.

Could you sort out anything in this regard?
I also have this issue and it is super frustrating.
If you have various spreadsheets that Activate from buttons on a Menu in Excel 2013, I do not know what the alternative option should now be.

Regards,

James

 

[JimSnyder]

Nothing to add at this time. I am currently unemployed and not using VBA.

 

[ngatel]

Hi,

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 some different data in both sheets, in same cells, to observe the issue better, and on Sheet1 have 2 buttons:

On click of Button1 activate Sheet2 worksheet using a Macro1 with 1 line in it:
ThisWorkbook.Sheets("Sheet2").Select

I can edit data in Sheet2 fine in this situation.

On click of Button2 call Macro2 which has 1 line:
UserForm1.Show

a UserForm1 pops up and on click of a 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!
I tried also with Activate method instead of Select, same result, works fine for older Excel version, doesn't work with 2013.

Clicking back from Sheet1 to Sheet2 worksheet activates the Sheet2 sheet properly.

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

Thank you

I am having the same problem as the OP.

I have several computers running Excel 2003, 2007 and 2010. They all work. It won't work on 2013. Unfortunately I do not have access to a computer running 2013 and don't want to purchase it or install it due to many the many problems I have read about. Last week I was at a client's location 1,000 miles from home and spent quite a bit of time trying to find the solution.

When I activate a 2nd sheet via a user form, the 2nd sheet is now visible. But I think it is just the sheet's "window" is visible, because sheet #1, appears to actually be the active sheet; even though I have hidden the sheet and am looking at Sheet #2.

What happens is that when I type data into a cell in Sheet #2, I can see it in the formula bar but it disappears when I hit ENTER or TAB and the original data is remains in the cell. When I go back to Sheet #1, the data I typed in is now there.

I have two sheets in this workbook. Sheet #1 (MainMenu) is for all users. Sheet #2 (Maint_Menu) is only for the manager to update data. Originally the User Form selected Sheet #2, which had a button to unprotect it. When the button was clicked, it would not take the password, but it would take the password for sheet #1, which told me that either the passwords got scrambled or sheet #1, which was now hidden, was in fact still the active sheet. I believe that is what is happening.

Having read that there were password problems in 2013, I decided to change the user form to confirm a password using a TRUE/FALSE statement in Sheet #1. If the text typed into the user form is true, Sheet #2 is activated. I removed all protection from Sheet #2.

Here is the code. It is a mess right now because I have tried so many combinations. But it works perfectly on 2010.

-----------------

Private Sub frmPassword_Click()

Unload Me

' enter password into form which references the password in the Main Menu worksheet

If Sheets("MainMenu").Range("G145") = False Then
frmNoPassword.Show

' go to the Maintenance Menu if the password is correct

ElseIf Sheets("MainMenu").Range("G145") = True Then
Sheets("Maint_Menu").Visible = True


Sheets("Maint_Menu").Activate

Sheets("MainMenu").Visible = xlVeryHidden



Range("B4").Select

ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1


End If



' if password is correct, delete it from the sheet so the UserForm TxtBox Control is blank

Sheets("MainMenu").Range("G141").ClearContents


End Sub

----------------------------

Any help would be appreciated. This seems like such a simple problem that has me totally confused.

 

[fghlkj]

Try editing your Userform.show to:
Userform.show vbModeless

I was having a really tough time trying to figure out what was wrong, but this fixed it for me for some reason.