CODEWITHSUNDEEP
vbaexcel
N D
N D

Reputation: 1

Selecting and switching to veryhidden Sheet not working properly

I have been writing a few macros lately to navigate around sheets / change visibility / import-export data etc. I normally just embed the macros as buttons on the sheets. Normally this works well, however, I do keep experiencing an issue where with a macro, or a userform I unhide a veryhidden sheet, select it and exit the macro, or form.

I do this though via:

With Sheets("Sheet1")
    .visibile = xlsheetvisible
    .activate 
    .Range("A1").select
End With

When I then try to manipulate the sheet - e.g. type in a cell, delete data from cell, or insert / delete rows using the GUI rather than doing it via code, the operations happen on the original sheet with the button that called the form or macro. rather than the new one....

Is anyone else experiencing this? Am I doing something wrong?

Upvotes: 0

Views: 119

Answers (2)

N D
N D

Reputation: 1

Found the Solution!!!

Upon further investigation it seems like this is only broken in Office 2013. I tried it on Office 2010 and it worked fine.

The solution is to invoke the vbModeless command after Userform.show so that would be: Userform1.Show vbModeless

Not perfect, if you want the user to dismiss the Userform before going back to the worksheet, but hey -it's a workaround :)

Let's hope Office 2016 will fix the bug (I'll be upgrading later this month)

Upvotes: 0

Aditya Pansare
Aditya Pansare

Reputation: 1132

Give worksheet object for cells while referring the editing

for ex... sheets().range().paste

or activate the sheet which you want to manipulate before your code (which manipulates the sheet)

Upvotes: 1

Related Questions