Back to Excel Newsletter
Archives
Back to Excel Newsletter
Archives
In case you are wondering, there was no newsletter last Month (May) due to problems beyond our control.
DATA BARS
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.
PRIOR VERSIONS OF EXCEL
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.
ONLY DEAD FISH SWIM WITH TIDE
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
How to use Visual Basic for Applications (VBA) to change UserForms in Excel
How to Programmatically Create UserForms in Visual Basic for Applications
AGE ENTRY FORM
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.
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
.NameCombo.SetFocus
Else 'Don't replace
Exit Sub
End If
End If
End With
End Sub
Private Sub CloseButton_Click()
Unload Me
End Sub
Got any Questions? Free Excel Help
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help