MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return to original active sheet at end of macro


Posted by Leo on April 02, 2001 10:52 AM

I want to create a macro which selects all and cells in a workbook, changes formatting, and returns to the cell I was in before running the macro. I know to start with Sheet.Select and Cells.Select and how to accomplish the formatting I'm looking for; how do I end up where I started?
Any help?


Posted by Dave Hawley on April 02, 2001 11:00 AM

Hi Leo

Selecting in VBA should (and in most cases can) be avoided. Try this method


Sub ChangeAllformats()
Dim Sht As Worksheet
Dim ThisSheet As Worksheet

Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet

For Each Sht In ThisWorkbook.Worksheets
Sht.Cells.Style = "Currency"
Next Sht

Application.ScreenUpdating = True
ThisSheet.Select
Set ThisSheet = Nothing

End Sub

Dave

OzGrid Business Applications

Posted by Leo on April 02, 2001 11:57 AM

Sorry, Dave, I must be dense.....

.. but I can't figure which pieces of your code are to select all sheets/cells and which are to return to the original spot in the worksheet after the formatting changes are done. I guess what I want to do is insert the code for the formatting changes (e. g., turn off gridlines, erase borders, etc) between the starting and ending pieces of your code. I can't seem to make it work that way.

Posted by Dave Hawley on April 02, 2001 12:40 PM

Not at all !

Leo, paste this code into a module for easier reading, then run it from your Worksheet.

Sub ChangeAllformats()
'Dimension variables
Dim Sht As Worksheet
Dim ThisSheet As Worksheet

'Stop screen flickering
Application.ScreenUpdating = False
'Set the Worsheet Variable "ThisSheet" Activesheet
Set ThisSheet = ActiveSheet

'Loop through all cell and turn off gridline, _
remove borders.
For Each Sht In ThisWorkbook.Worksheets
With Sht
.Activate 'Need to activate to turn off Gridlines
.Cells.Borders.LineStyle = xlLineStyleNone
End With
ActiveWindow.DisplayGridlines = False
Next Sht
'End of loop

Application.ScreenUpdating = True

ThisSheet.Select 'Finish on the sheet we started
Set ThisSheet = Nothing 'release memory

End Sub


I have put comments in the code to try and help you follow it.


Dave


OzGrid Business Applications

Posted by Leo on April 02, 2001 12:57 PM

"Run Time" Error message

Dave:
Running the macro you supplied returned error message: "Run time error 1004:
Method 'Select' of Object '_Worksheet' failed."

I pasted directly from your post. Any thoughts?
I really appreciate the time you're taking.
Leo

Posted by Dave Hawley on April 02, 2001 1:21 PM

Re: "Run Time" Error message


When you get the error, click Debug nad tell me which line of the code is highlighted.

Dave

OzGrid Business Applications

Posted by Leo on April 02, 2001 1:51 PM

De-bugger

Highlighted line is: "ThisSheet.Select 'Finish on the sheet we started"

Posted by Dave Hawley on April 02, 2001 2:19 PM

Re: De-bugger


Leo. I'm unable to reproduce this error could I send you a small Workbook example ?

Dave

OzGrid Business Applications