Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter

Search Tips FREE Download!

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

1. DOWNLOAD THE FREE OZGRID TOOLBAR . NO SPYWARE, ADWARE OR MALWARE!
2. FREE LEVEL 1 EXCEL TRAINING!
3. THIS MONTHS SPECIAL!
   SQL TESTER : The SQL Tester is an interactive Query Tool (Excel add-in) for retrieving data and displaying it on the screen in an ad hoc basis from almost all kinds of databases. The output can be placed in worksheets and added to workbooks VBA-projects. Currently only $33.00!

Comparing the Same Workbook

A very handy feature of Excel is its ability to allow you to create a copy of a workbook and compare and work with the copy whilst simultaneously working with and viewing the original workbook.  There are two ways in Excel that you can do this.

Creating a New Window

The first thing that needs to be done is to create another instance of the workbook you wish to compare.  To do this, first open the workbook you wish to compare, then go to Window>New Window.  This will force Excel to create an identical copy of your file.  We will use Book1 as an example.  The naming convention will change to be Book1:1 and Book1:2 once you create the new copy via Window>New Window.

Compare Workbook Command         **Note that this is only available in later versions of ExcelNow in Book 1:1, go to Window>Compare Side by Side with Book1:2 and you will see both workbooks displayed one on top of the other with the Compare toolbar also showing on your screen.  Remember that any change you make in either copy of the same workbook will be shown in the other copy when you save.

The Compare Workbook toolbar has three buttons on it.  The first button (called Synchronous Scrolling if you wave your mouse over it) when pressed in will ensure that both copies will move simultaneously as you scroll through them.  The second button Reset Window Position will reset your workbooks to the position they were originally in before you Start ed to Compare.  The third button Close Side by Side closes down the Compare option, as does the command Window>Close Side by Side, as does double clicking the Maximise button on either copies, or double clicking the blue title bar of either copy.

The only limitation with the Compare command is that it limits you to comparing two copies of the same workbook only.

Arranging the Workbook

The Arrange option found under the Windows command works in a similar manner to the Compare command, except that it allows greater flexibility.  The Arrange command allows you to work with multiple copies of the same workbook.  Again using Book1 as an example, go to Window>New Window and create another copy of your workbook.  Do it again another three times.  You should now have Book1:1, Book 1:2, Book1:3, Book 1:4, Book1:5.  Now go to Window>Arrange  The Arrange Workbook dialog will pop up with four display options; Tiled, Horizontal, Vertical, Cascade.  Notice also the option Windows of Active Workbook at the bottom of the dialog.  Only check this if you have other workbooks open.  Select one of the four display options (the following screen shot shows the tiled option) and click OK.

You can also show and use the Compare Side by Side toolbar by going to View>Toolbars>Customise and checking Compare Side by Side and clicking Close.

A quick way to close all copies of the workbook at once is to hold down the Shift key on your keyboard and select the File option from the worksheet menu bar.  You will see that the option Close changes to Close All when the Shift key is held down.  Select Close All to close all copies of the workbook at once.  You will be prompted to save if you have made changes.

Sub UserInput()
Dim iReply As Integer

    iReply = MsgBox(Prompt:="Do you wish to run the 'update' Macro", _
            Buttons:=vbYesNoCancel, Title:="UPDATE MACRO")
            
    If iReply = vbYes Then
        Run "UpdateMacro"
    ElseIf iReply = vbNo Then
       'Do Other Stuff
    Else 'They cancelled (VbCancel)
        Exit Sub
    End If
    
End Sub

Sub GetUserName()
Dim strName As String

    strName = InputBox(Prompt:="You name please.", _
          Title:="ENTER YOUR NAME", Default:="Your Name here")
          
        If strName = "Your Name here" Or _
           strName = vbNullString Then
           Exit Sub
        Else
          Select Case strName
            Case "Bob"
                'Do Bobs stuff
            Case "Bill"
                'Do Bills stuff
            Case "Mary"
                'Do Marys stuff
            Case Else
                'Do other stuff
          End Select
        End If
  
End Sub

Sub RangeDataType()
Dim rRange As Range

    On Error Resume Next
        Application.DisplayAlerts = False
            Set rRange = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)
    On Error GoTo 0
        Application.DisplayAlerts = True

        If rRange Is Nothing Then
            Exit Sub
        Else
            rRange.Font.Bold = True
        End If
        
End Sub

Sub NumericDataType()
Dim lNum As Long

    On Error Resume Next
        Application.DisplayAlerts = False
            lNum = Application.InputBox _
             (Prompt:="Please enter you age.", _
                    Title:="HOW OLD ARE YOU", Type:=1)
    On Error GoTo 0
    Application.DisplayAlerts = True

        If lNum = 0 Then
            Exit Sub
        Else
            MsgBox "You are " & lNum & " years old."
        End If
        
End Sub

Sub Numeric_RangeDataType()
Dim vData

    On Error Resume Next
        Application.DisplayAlerts = False
        
            vData = Application.InputBox _
             (Prompt:="Please select a single cell housing the number, " _
             & "or enter the number directly.", _
             Title:="HOW OLD ARE YOU", Type:=1 + 8)
             
    On Error GoTo 0
        Application.DisplayAlerts = True

    If IsNumeric(vData) And vData <> 0 Then
        MsgBox "You are " & vData & " years old."
    Else
       Exit Sub
    End If
End Sub