Excel

Tidy up your workbook at any time and when you save it

Ease of Use

Easy

Version tested with

2000 

Submitted by:

mdmackillop

Description:

The code will make A1 the active cell in each sheet 

Discussion:

On opening sheets in a workbook, you are often taken to remote areas of a worksheet where the last user was working. This code moves the active cell focus to A1 on each sheet. The TidyAndReturn option can be used at any time to "tidy" all sheets, deselecting all ranges and returning you to your activecell location. Either code can be called from a BeforeClose Workbook or BeforeSave event to tidy up the workbook as required. Thanks to FireFytr for the IsChart routine. 

Code:

instructions for use

			

'These routines go in a standard module Option Explicit Sub TidyAndReturn() Dim cel As Range, sh As Worksheet, SRw As Long, SCol As Long, i As Long Application.ScreenUpdating = False Set sh = ActiveSheet Set cel = ActiveCell SRw = ActiveWindow.ScrollRow SCol = ActiveWindow.ScrollColumn For i = Sheets.Count To 1 Step -1 'Check to see if the worksheet is a chart If Not IsChart(Sheets(i).Name) Then Sheets(i).Select Range("A1").Select ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 End If Next sh.Select cel.Select ActiveWindow.ScrollRow = SRw ActiveWindow.ScrollColumn = SCol Application.ScreenUpdating = True End Sub Sub Tidy() Dim i As Long Application.ScreenUpdating = False For i = Sheets.Count To 1 Step -1 If Not IsChart(Sheets(i).Name) Then Sheets(i).Select Range("A1").Select ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 End If Next Application.ScreenUpdating = True End Sub Function IsChart(cName As String) As Boolean 'Thanks to Firefytr for this function Dim tmpChart As Chart On Error Resume Next Set tmpChart = Charts(cName) IsChart = IIf(tmpChart Is Nothing, False, True) End Function 'These routines go in ThisWorkBook module Private Sub Workbook_BeforeClose(Cancel As Boolean) Tidy End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) TidyAndReturn End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste Standard Module code into the right pane.
  7. Return to the Project Explorer and DoubleClick ThisWorkBook module
  8. Paste ThisWorkBook Module code into the right pane.
  9. Press Alt + Q to close the VBE.
  10. Save workbook before any other changes.
 

Test the code:

  1. Go through your workbook, selecting cells and areas at random
  2. From Excel, press Alt + F8 to open the macro dialog box.
  3. Select TidyAndReturn
  4. Click Run.
  5. All sheets except the open one will have A1 selected on returning to them
 

Sample File:

TidyUp.zip 11.05KB 

Approved by mdmackillop


This entry has been viewed 403 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express