Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter June 2008

Add to Google Search Tips Excel Data Manipulation and Analysis

Excel Dashboard Reports & Excel Dashboard Charts 50% Off



Got any Questions? Free Excel Help

In case you are wondering, there was no newsletter last Month (May) due to problems beyond our control.



This month I thought we would take a look at how data bars work in both Excel 2007 and prior versions.  A data bar helps you see the value of a cell relative to other cells visually and is useful for comparing higher and lower values.  First, we will take a look at how they work in Excel 2007, very easy, it is now part of the Conditional Formatting feature, which has been greatly enhanced in Excel 2007.

EXCEL 2007

Get a clean worksheet in Excel and put the number 100 in cell A1 and the number 200 in cell A2.  Highlight both cells and with the fill handle, fill down to row 15. You should have the numbers 100 to 1500 in 100 increments.  Now highlight the range A1:A15 and go to Styles options on the Home tab and select Conditional Formatting>Data Bars then select the color you prefer.  You will see that cells A1:A15 now have shading, or data bars in them.  The length of the bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value.


To do the same thing in prior versions again place the number 100 in cell A1 and the number 200 in cell A2.  Highlight both cells and with the fill handle, fill down to row 15. You should have the numbers 100 to 1500 in 100 increments.  Now click in cell B1 and place in the following function =REPT("|",A1/16), and click Enter.  Now click back on cell B1 and fill the formula down to B15.

You will see in cells B1:B15 what look like data bars.

The function we used to get this effect is the REPT (repeat) function and will repeat text any given number of times.  You can use REPT to fill a cell with a number of instances of a text string, We used the | key, but you can in fact use any key as long as it is encased in quotation marks.  So the REPT function looks at cell A1, divides the number by 16, then repeats the specified key that number of times.  You can alter the number you are dividing by if you like to play around with the length of the bars.

If you want to finish it off nicely, you could format cells B1:B15 in a font color of your choice, and then highlight the range B1:F15 and select a suitable cell background.

2 Workbook Examples




Time and time again I see VBA code for data entry via UserForms made FAR more complicated and confusing that is necessary. Hopefully the example below will inspire you to PLAN before coding. By this I mean, planning is the ALL IMPORTANT part of any coding. Sadly, most simply make it up as they go along and unknowingly dig THEMSELVES into a deep dark hole. A good rule of Thumb is 80% of total time on planning and 20% of time on coding.

When planning, use Excel as it has been designed to be used and NOT what you THINK looks good or suits the users eyes.

See Efficient Spreadsheet Designs & Golden VBA Coding Rules

After that, read these links to get ideas on UserForm coding from Microsoft


This very simple example uses 1 UserForm called AgeEntryForm. 2 Labels named NameLabel and AgeLabel (Disabled as default). 1 ComboBox named NameCombo (RowSource set to Dynamic Named Range), 1 TextBox named AgeTextBox (Disabled as default) and 1 SpinButton named AgeSpinButton (Disabled as default). Also, 1 CommandButton named AddAgeButton (Disabled as default) and 1 CommandButton named CloseButton.

As you can see, I have used a naming convention that tells at at glance, what type of Control and what the Control does. I cannot tell you how handy this is when/if you need to modify/change anything at a later date.

You should also note that some Controls are disabled by default. This prevents, for the most part, any user leaving a field blank or giving erroneous data.

Ok, here is the code, in the general order of execution. There is also a download link below with 2 Workbook examples. One for 2007 and another for prior versions.

 2 Workbook Examples

Private Sub NameCombo_Change()
Dim bIsInList As Boolean

    With Me 'Only enable age textbox when name is chosen
        bIsInList = .NameCombo.ListIndex > -1
       .AgeLabel.Enabled = bIsInList
       .AgeTextBox.Enabled = bIsInList
       .AgeSpinButton.Enabled = bIsInList
            If bIsInList = True Then 'Name chosen so append to age textbox label caption
               .AgeLabel.Caption = vbNullString
               .AgeLabel.Caption = "Age of.." & .NameCombo
            End If
    End With
End Sub

Private Sub AgeSpinButton_Change()
'From 18 to 100
    Me.AgeTextBox = AgeSpinButton
End Sub

Private Sub AgeTextBox_Change()
   'Only enable add age button when number is in age textbox
    Me.AddAgeButton.Enabled = IsNumeric(AgeTextBox)
End Sub

Private Sub AddAgeButton_Click()
Dim lReply As Long, lAgeCheck As Long
Dim rAge As Range

    With Me
        'Set variable to cell corresponding to the name
         Set rAge = Range("Age").Cells(.NameCombo.ListIndex + 2, 1)

        'Check if age for name already exists
        If IsNumeric(rAge) Then
           'Pass existing age to variable
           lAgeCheck = rAge
            'Show message and confirm existing age should be overwritten
            lReply = MsgBox(.NameCombo & " already has an age of " & lAgeCheck & _
                vbNewLine & vbNewLine & "Replace with " & .AgeTextBox & "?", vbYesNo + vbQuestion, "REPLACE AGE?")
                    If lReply = vbYes Then ' Replace existing age
                       rAge = .AgeTextBox.Value
                              'Clear controls and set focus to Name ComboBox
                              .AgeTextBox = vbNullString
                              .NameCombo = vbNullString
                    Else 'Don't replace
                       Exit Sub
                    End If
        End If
    End With
End Sub

Private Sub CloseButton_Click()
    Unload Me
End Sub

 2 Workbook Examples

Got any Questions? Free Excel Help

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Excel Data Manipulation and Analysis

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help