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
