March 30, 2006

Just Back Up

My work computer is connected to a network, most company files located in a server. Although the server is backed up at frequent intervals, this won't help me much if it decides to stop working.

So I wrote this code to save files in both my computer and the server. Keep in mind that I don't want it to save in both computers each time, the files I use take a while to save, so I just want to save the backup every now and then.

Also, other people also refer to my files so I just want a prompt message to appear on my computer, not theirs. (The code goes into ThisWorkbook, a folder named "My Backup Files" already exists)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error Resume Next
    If SaveAsUI = False And Application.UserName = "Andrew" Then
            If MsgBox("Do you want to save a backup copy? ",vbYesNo, _
                ThisWorkbook.Name) = vbYes Then
                    ThisWorkbook.SaveCopyAs "C:" & Application.PathSeparator & _
                        "My Backup Files" & Application.PathSeparator & _
        End If
    End If
End Sub

I'm not really sure if it completely necessary to use Application.PathSeparator, most folk seem to just use a backwards slash but this might be safer with regards to international settings? (Sometime the slash mark shows as a Yen mark on my Japanese OS, it usually seems to change automatically according to the software I use)

Speaking of safe, both the original file and the copied file have their own backup files.

I've also used Environ("USERNAME") instead of Application.UserName and Environ("USERDOMAIN") which gives the computer name on the network. These work fine with my computer as I have Excel 2003, they cannot be used on earlier versions like 97 or 2000 (don't know about on 2002)  

Posted by andrewe at 19:19

March 12, 2006

Checkboxes Revisited

About a year ago I wrote a post about using Forms toolbar checkboxes. (If the Forms toolbar is not visible, right click any other toolbar and tick Forms, also bear in mind that Forms toolbar checkboxes are not the same as the ones from the Control Toolbox toolbar)

Although convenient, one thing lacking was a quick way to turn them On or Off. This is a way that makes use of the linked cell mentioned in my former post. (Just replace xlOn with xlOff to turn checkboxes off) Keep in mind the Selection code may affect other Forms toolbar objects that use linked cells.

For Selections
Sub TurnOnCheckboxesSelection()
    On Error Resume Next
    Dim cb As Shape, Cell As Range
    For Each cb In ActiveSheet.Shapes
        Set Cell = Intersect(Selection, _
        If Not Cell Is Nothing Then _
            cb.ControlFormat.Value = xlOn
        Set Cell = Nothing
End Sub

For Worksheets
Sub TurnOnCheckboxesSheet()
    On Error Resume Next
    ActiveSheet.CheckBoxes.Value = xlOn
End Sub

For Workbooks
Sub TurnOnCheckboxesBook()
    On Error Resume Next
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.CheckBoxes.Value = xlOn
End Sub



For the Selection code, you can change it a little to delete checkboxes in a Selecton using cb.Delete (instead of cb.ControlFormat.Value = xlOn).

Moving Selected Checkboxes
Another thing I've been asked is what to do if you want to move the checkboxes to another range, the problem being that the linked cell does not move with the checkboxes themselves...

Push the Select Objects button on the Drawing toolbar (looks like a mouse pointer) to select the checkboxes, while also pushing the Alt key to aligned precisely within cells, the top left corner of the checkboxes should be up against the top left corner of the cells. When finished, push Esc and run this code...(remove the optional Conditonal Formatting part if not required, if used make sure only the cells with Checkboxes are selected)

Sub TurnOnCheckboxesSelection()
    On Error Resume Next
    Dim cb As Shape, Cell As Range
    For Each cb In ActiveSheet.Shapes
        Set Cell = Intersect(Selection, _
        If Not Cell Is Nothing Then _
            cb.ControlFormat.Value = xlOn
        Set Cell = Nothing
    ' This part is optional
    ' *********************
        For Each Cell In Selection
        With Cell
            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=" & Cell.Address & "=TRUE"
            .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
            .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
            .Font.ColorIndex = 2 'cell background color = White
        End With
    ' *********************
End Sub

Problem solved ;-)  
Posted by andrewe at 17:16

March 05, 2006

New Book Navigator

What's new?

  • Scroll Bars Position Retained
    Even though previous versions navigated to the right place, the scroll bars were not in the same position as when Highlight or Bookmarks were set. No more. If the top left cell is for example, AD37, this is recorded along with selection(s) location (Highlights and Bookmarks). The result is that you will always move to exactly the same position each time.

  • Bookmarks Retained
    Bookmarks were previous deleted when exiting Excel. Now they can be used each time you use Excel. Simply overwrite them to change their ranges.

  • Increased Range
    Highlights can now refer to more than one cell, Bookmarks can refer to more than one area. In the case of multiple cells, pushing Show Details will show all cells with selected ranges as well as the value of the first cell, (top left cell of the range only).

  • Scroll Sheets
    This is handy for scrolling to other sheets in a workbook and keeping exactly the same place. (Two new buttons have been added to the right side of the Book Navigator toolbar)

    As with the Highlight and Bookmarks, the scroll bars retain their position as you move to other sheets. Multiple selections can also be used.

    Tip: Before scrolling, you can push a row tab to select an entire row - you may find it helpful in highlighting the selection when you move to the next sheet.

    Save time, save your eyes. The download link is here.

    New Cell Spotter also available
    As promised, the new version of Cell Spotter is available here. Best to refer to the Read Me file that's included to understand the new features :-)  
  • Posted by andrewe at 17:49

    March 01, 2006

    Visibility In Excel Part 3 User Friendly Rows

    Excel is vertical-centric (Okay, I made this word up)

    Don't believe me? The user interface is mostly at the top or bottom - Main menu at the top, Autofilter at the top, Name Box and Formula Bar at the top, Status Bar at the bottom, Sheet tabs at the bottom...why is it so? I guess the reason is that it is a lot easier to work "vertically" than "horizontally", even mouse wheels are designed to work this way.

    Which leads to a problem with rows. Looking at several rows with data, it's easy to lose track of which row you are supposed to be looking at. So here are a few tips to make life easier.

    1. Increase the row height
    If this does not interfere with how your files are set up, changing row heights to somewhere between 18 and 24 points will help reduce eyestrain.

    Before (14.25 points)

    After (18.00 points)

    Easier to see with just a minor change in row height.

    2. Row Shading
    Changing the color of every other row helps too. You can just change the Fill color, or use Conditional Formatting. (This has the advantage of chaning the row color if you decide to insert more rows later on)

    Here's a link to one of my old posts.

    My preferred formats are




    mainly because they are simple to remember...

    The difference?

    Getting better again.

    3. Automatic row highlight
    This is some code I wrote a week or so ago. Place it in the appropiate sheet module and change the top left cell and bottom right cell of your range to suit where indicated.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Range("A1") = False Then Exit Sub ' Optional
        On Error Resume Next
        Dim myRange As Range
        Dim myTopLeftCell As Range
        Dim myBottomRightCell As Range

        ' Set your Top Left Cell and Bottom Right Cell, (Range Names can be used also)
        ' ****************************************************************
        Set myTopLeftCell = Range("B2")
        Set myBottomRightCell = Range("H20")
        ' ****************************************************************

        If Target.Row >= myTopLeftCell.Row And _
            Target.Offset(Selection.Rows.Count - 1).Row <= myBottomRightCell.Row And _
            Target.Column >= myTopLeftCell.Column And _
            Target.Offset(, Selection.Columns.Count - 1).Column <= myBottomRightCell.Column Then
            Set myRange = Selection
            If ActiveSheet.Shapes("hSelection") Is Nothing Then
                ActiveSheet.Shapes.AddShape(msoShapeRectangle, myTopLeftCell.Left, Selection.Top, _
                        myBottomRightCell.Offset(, 1).Left - _
                        myTopLeftCell.Left, Selection.Height).Select
                With Selection
                    With .ShapeRange
                        .Fill.Visible = msoFalse
                        .Line.ForeColor.SchemeColor = 12 ' Change Color Here
                        .Line.Weight = 2.25 ' Change Line Weight (Thickness) Here
                        .ZOrder msoSendToBack
                        .Shadow.Visible = msoFalse
                    End With
                    .Name = "hSelection"
                    .PrintObject = False
                End With
                With ActiveSheet.Shapes("hSelection")
                    .Left = myTopLeftCell.Left
                    .Top = Selection.Top
                    .Width = myBottomRightCell.Offset(, 1).Left - myTopLeftCell.Left
                    .Height = Selection.Height
                    .ShapeRange.Shadow.Visible = msoFalse
                End With
            End If
        End If
        Set myTopLeftCell = Nothing
        Set myBottomRightCell = Nothing
        Set myRange = Nothing
    End Sub

    Like my previous Data Validation code, it assumes that there is a Forms Toolbar checkbox in Cell A1 (Turn it the checkbox on and off to stop the code from running, it tends to interfere with Undo)

    Here's some code to quickly insert a Checkbox.

    Sub AddCheckbox()
        On Error Resume Next
        ActiveSheet.CheckBoxes.Add(0, 0, 0, 0).Select
        With Selection
            .Characters.Text = ""
            .Value = xlOn
            .LinkedCell = "A1"
        End With
        Selection.Font.ColorIndex = 2
    End Sub

    With the Checkbox turned on, you are ready to go. Select any cell within the range and the row is highlighted accordingly.

    Here's a pic.

    I've also been working on a new version of my Cell Spotter to work in a similar way. Just now I'm testing it at work, I'll upload it when it's ready :-)  
    Posted by andrewe at 21:49