All Excel Templates Super Special $189.00! New Templates Added!
We Recommend SmartDraw for Flowcharts NewsletterSubscriber Specials
SQL Tester - The only tool you need to connect to all kind of databases to retrieve data direct into MS Excel in an easy and smooth way. It also gives the possibility to generate VBA-procedures with one button-click, which can be inserted into end-users / client-solutions
File Conversion Software | Code Printing Software |All Software
What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks What's New at OzGrid
We are still madly working on updating the Level 2 training lessons. Unfortunately, although a very important aspect of our business, it is not the only aspect of our business and other areas have needed our attention this month. However, I am just about finished the Level 2 course, so I only have the Level 3 course to do. Dave will be updating the VBA for Excel courses himself and he hopes to commence this soon.
The new improved Level 1 course is still selling really well, The Training courses are being offered in two modes, as discount downloadable training and now via our new Training Forum.Click here : to see the details. Once the Training Lessons are completed, our next project will be to create an on-line exam for students to take after completion of the lessons, if they wish. This will probably be in the form of a multiple choice assessment, but we are not 100% sure yet.
If you are a Forum member, you are able to take the training in our new Training Forum at a discount. Click here : to do this and see our prices. Level 1 now available.
Our book,Excel Hacks - 100 Industrial Strength Tips and Tools is selling better than we had hoped. O'Rielly have asked us to do an update to the book in 12 months or so, which will be great. We will keep you posted.
Dave is currently developing another software product which will be available for sale via the website. more on this next month.
We also have an association with Amazon and have set-up a store here All proceeds fro this store will be used to enhance the question Forum and keep it totally free. See the "Books, Software, CD's, DVD's etc" link near the top of all Forum pages.
Finally, with the anniversary of the Twin Towers tragedy tomorrow and the bombing near the Australian Embassy in Jakarta yesterday, our thoughts are with all those who visit our website who have had innocent family and friends touched by these terrible events.
Until next month...
This month we will look at a couple of those under-utilized features. One of these is Working with Excel AutoShapes, Comments and Pictures. To access AutoShapes you will need the Drawing toolbar visible, View>Toolbars>Drawing.
When you add a Cell Comment to any cell you can leave, yourself, or other users a message. Sort of like sticky notes. To add a Cell Comment, select any cell and then right click and choose Insert>Comment. You then simply type your text. The default text (if any) is determined via Tools>Options-General-User name:
Another good use for Cell Comments is to add pictures to them. After adding a Cell Comment, right click in the cell and choose Edit comment. Now double click the dotted outline of the Comment where the size handles are, and then click the "Colors and Lines" page tab. From this page click the drop arrow from the "Color" box and then "Fill Effects" and finally "Picture" then "Select Picture". A good one to add is a picture of a chart!
Cell Comments, 'as are' are quite plain. However, we do not have to stick with the default shape that Excel gives us. Select any cell with a Cell Comment and again choose Edit Comment, then click the outer border once. Now, from the Drawing toolbar (View>Toolbars>Drawing) click Draw and then "Change AutoShape". You will see you have quite a few to choose from.
After picking the shape wanted we can even add some shadow and/or 3-D effects. Select any cell with a Cell Comment and again choose Edit Comment, then click the outer border once. Now, from the Drawing toolbar make a selection from the last 2 far right options, Shadow Style and 3-D Style.
When/if you add an AutoShape to a Worksheet you can also link the text that is displayed to a cell on a Worksheet. Add an AutoShape to a Worksheet and then ensure you have the AutoShape selected, so that you can see the Size Handles. Now click in the Formula bar and push = then click the cell you wish to link to and push Enter.
The one draw back with this is, the AutoShape does not take on the formatting of the cell it's linked to. To do this we can use a Picture Link. Format any cell in anyway you wish, use some color so you can see the formatting. Now select this cell, or cells, and Copy. Now select any other cell on any Worksheet and then holding down the *Shift* Key go to Edit>Paste Picture Link. Now any changes to the link cell will be reflected in the Linked Picture. If you want a static picture simply choose "Paste Picture" and not "Paste Picture Link".
This month in the VBA section I thought we would look at making ourseleves a custom Find form. What we will do, to make it different to the normal find, is allow the user to specify more than one item to locate. For example, if you have a large table of data (say A1:H1000), you may wish to find a specific row in that table where 3, or more items, occur on the one row. The number OF items can be greater or smaller than 3, but for this example we will use 3.
To create this custom find you will need.
A) 1 UserForm.
B) 3 ComboBoxes. Named ComboBox1, ComboBox2 and ComboBox3. Place these vertically on the left side of UserForm with ComboBox1 at the top and ComboBox3 and the bottom. Set the Enabled Property of ComboBox2 and ComboBox3 to False.
C) 5 Labels. Named Label1 (positioned above Combobox1), Label2 (positioned above Combobox2), Label3 (positioned above Combobox3), Label4 positioned above Label1 and Label 4 anywhere for now. Change the Caption Property of Label4 to read "Select up to 3 fields". Change the Caption Property of Label5 to read "Matching Rows. Double click to go there".
D) 2 CommandButtons. CommandButton1 and CommandButton2. Change the Caption Property of CommandButton1 to "Find" and the Caption Property of CommandButton2 to "Close". Postion both of these to the top right of the UserForm.
E) 1 ListBox. Named ListBox1. Postion this below the 2 CommandButtons and place label5 above it. Make ListBox1 the same width as Label5.
That is all we need for creating the form now it's time to add the all important code. Double click the UserForm and add all the code shown below.
Option Explicit
'Module Level Variables
Dim rRange As Range
Dim strFind1 As String
Dim strFind2 As String
Dim strFind3 As String
Private Sub ComboBox1_Change()
'Pass chosen value to String variable strFind1
strFind1 = ComboBox1
'Enable ComboBox2 only if value is chosen
ComboBox2.Enabled = Not strFind1 = vbNullString
End Sub
Private Sub ComboBox2_Change()
'Pass chosen value to String variable strFind1
strFind2 = ComboBox2
'Enable ComboBox3 only if value is chosen
ComboBox3.Enabled = Not strFind2 = vbNullString
End Sub
Private Sub ComboBox3_Change()
'Pass chosen value to String variable strFind1
strFind3 = ComboBox3
End Sub
Private Sub CommandButton1_Click()
'Procedure level variables
Dim lCount As Long
Dim lOccur As Long
Dim rCell As Range
Dim rCell2 As Range
Dim rCell3 As Range
Dim bFound As Boolean
'At least one value, from ComboBox1 must be chosen
If strFind1 & strFind2 & strFind3 = vbNullString Then
MsgBox "No items to find chosen", vbCritical
Exit Sub'Go no further
ElseIf strFind1 = vbNullString Then
MsgBox "A value from " & Label1.Caption _
& " must be chosen", vbCritical
Exit Sub 'Go no further
End If
'Clear any old entries
On Error Resume Next
ListBox1.Clear
On Error GoTo 0
'If String variable are empty pass the wildcard character
If strFind2 = vbNullString Then strFind2 = "*"
If strFind3 = vbNullString Then strFind3 = "*"
'Set range variable to first cell in table.
Set rCell = rRange.Cells(1, 1)
'Pass the number of times strFind1 occurs
lOccur = WorksheetFunction.CountIf(rRange.Columns(1), strFind1)
'Loop only as many times as strFind1 occurs
For lCount = 1 To lOccur
'Set the range variable to the found cell. This is then also _
used to Start the next Find from (After:=rCell)
Set rCell = rRange.Columns(1).Find(What:=strFind1, After:=rCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'Check each find to see if strFind2 and strFind3 occur _
on the same row.
If rCell(1, 2) Like strFind2 And rCell(1, 3) Like strFind3 Then
bFound = True 'Used to not show message box for no value found.
'Add the address of the found cell and the cell on the _
same row but 2 columns to the right.
ListBox1.AddItem rCell.Address & ":" & rCell(1, 3).Address
End If
Next lCount
If bFound = False Then 'No match
MsgBox "Sorry, no matches", vbOKOnly
End If
End Sub
Private Sub CommandButton2_Click()
'Close UserForm
Unload Me
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Check for range addresses
If ListBox1.ListCount = 0 Then Exit Sub
'GoTo doubled clicked address
Application.Goto Range(ListBox1.Text), True
End Sub
Private Sub UserForm_Initialize()
'Procedure level module
Dim lRows As Long
'Set Module level range variable to CurrentRegion _
of the Selection
Set rRange = Selection.CurrentRegion
If rRange.Rows.Count < 2 Then ' Only 1 row
MsgBox "Please select any cell in your table first", vbCritical
Unload Me 'Close Userform
Exit Sub
Else
With rRange
'Set Label Captions to the Table headings
Label1.Caption = .Cells(1, 1)
Label2.Caption = .Cells(1, 2)
Label3.Caption = .Cells(1, 3)
'Set RowSource of ComboBoxes to the appropriate columns _
inside the table
ComboBox1.RowSource = .Columns(1).Offset(1, 0).Address
ComboBox2.RowSource = .Columns(2).Offset(1, 0).Address
ComboBox3.RowSource = .Columns(3).Offset(1, 0).Address
End With
End If
End Sub
Private Sub UserForm_Terminate()
'Destroy Module level variables
Set rRange = Nothing
strFind1 = vbNullString
strFind2 = vbNullString
strFind3 = vbNullString
End Sub
Now go to Insert>Module and add this code
Sub ShowForm()
On Error Resume Next
UserForm1.Show
On Error GoTo 0
End Sub
Now all you need is a Table of data on any Worksheet. Select any single cell in the table and run the ShowForm code.
ADVERTISEMENTS
Artificial neural network software for stock markets!
MAIN SOFTWARE CATEGORIES