OzGrid Excel and VBA Newsletter January 2008

EXCEL DEVELOPMENT AT A DISCOUNT PRICE, BUT NOT SKILLS
Professional Consultancy

AD FREE QUESTION FORUMS
Tired of seeing ads on the Ozgrid forum?

EXCEL TIPS AND TRICKS

Let's look at using the remaining Controls available from the Forms toolbar.
http://www.ozgrid.com/Excel/Excel-controls-forms-toolbox.htm
If you missed the last 2 issues, click below;
http://www.ozgrid.com/News/nov-2007.htm
http://www.ozgrid.com/News/dec-2007.htm

Firstly you will need to show the Forms toolbar. Go to View>Toolbars>Forms. Going from left-to-right the available controls are;

1) Label
2) Group Box
3) Button
4) Check Box
5) Option Button
6) List Box
7) Combo Box

Ok, picking up where we left off last month, let's look at the remaining Forms Controls.

8) Scroll Bar
9) Spinner

SCROLL BAR
This control is generally used in a similar way to the Spinner control (increment cell number), except you can visually see how much incrementing is left.

Add one to your Worksheet and by default the positioning is vertical. To make it horizontal left click and drag the bottom middle size handle up. Whichever positioning you choose it wont effect the use of the control.

Left click again and choose Format Control | Control.

Current Value: Zero by default and you set this to your starting point. Often 1 or Zero.

Minimum Value: Determines how low your scroll bar can increment starting from its Current Value.

Maximum Value: Determines how high your scroll bar can increment starting from its CurrentValue.

Increment Change: Determines how much each click of the Scroll button (left/up and right/down) arrows will increase/decrease with each click.

Page Change: Determines how much each click on the Scroll Bar slider will increase/decrease with each click.

Cell Link: Cell to increment by the above settings.

Download Example
Ok, let's look at a practical example. Let's say you have a table with both row and column headings in the range G2:J8. Where G2:J2 are your column headings and G2:G8 are your row headings. Select the entire table and go to Insert>Name>Create and ensure only Top row & Left column are checked, click OK. This will create your named rows & columns based on your row and column headings. If your left column and/or top row has spaces in them, Excel will create the named range by replacing the space with the underscore (_).

Now place 1 horizontal Scroll Bar and 1 vertical Scroll Bar and but the left arrow of the horizontal Scroll Bar up against the up arrow of the vertical Scroll Bar.

Settings for the horizontal Scroll Bar should be;
Current Value: 2
Minimum Value: 2
Maximum Value: 4
Increment Change: 1
Page Change: 2
Cell Link: $B$6

Settings for the vertical Scroll Bar should be;
Current Value: 2
Minimum Value: 2
Maximum Value: 7
Increment Change: 1
Page Change: 3
Cell Link: $C$6

On top of the horizontal Scroll Bar Enter;
=INDEX($G$2:$J$8,1,B6)
I'm using cell B5

To the left of the vertical Scroll Bar Enter;
=INDEX($G$2:$J$8,C6,1)
I'm using cell A11

Now we can use the Intersect operator (space) to locate the intersecting cell of our Row/Column headings returned by our INDEX Functions. Remember, we need to replace a space with the underscore, which the SUBSTITUTE Function does. Also, as our named ranges being returned with our INDEX Functions are residing in cells so we need to use INDIRECT to stop them being seen as text. So, anywhere you like Enter;

=INDIRECT(SUBSTITUTE($B$5," ", "_")) INDIRECT(SUBSTITUTE($A$11," ","_"))

SPINNER
Download Example
As mentioned above, the Spinner control works in the same way as the Scroll Bar control but without a Page Change setting.

An example of its use is only limited by your own imagination. In the example file I am using it to Toggle the source range of a PivotTable between 2 named ranges with the source named range of the PivotTable is set to a 3rd named range called Report which toggles between the 2. I have also assigned a simple macro to the Spinner so a manual Refresh of the PivotTable is not required

EXCEL VBA TIPS & TRICKS

This month I have picked out some code from a Forum question
Add Space To Each Uppercase Letter In Text.

Both of these are Custom Functions (UDFs)

The 1st one is from Andy Pope and the latter is from Jindon. Both are extremely helpful members who know their VBA.

Function SplitCamelText(TextToSplit As Variant, _
Optional Delimiter As Variant = " ") As Variant
     
   'Written by Andy Pope
   'http://www.andypope.info/
   'For Ozgrid
   'http://www.ozgrid.com/
   
    Dim lngIndex As Long
    Dim strText As String
    Dim blnPreviousLetterUpper As Boolean
     
    On Error GoTo Err_SplitCamelText
     
    strText = TextToSplit
    lngIndex = 2
    Do While lngIndex <= Len(strText)
        Select Case Mid(strText, lngIndex, 1)
        Case "A" To "Z"
            If Not blnPreviousLetterUpper Then
                strText = Left(strText, lngIndex - 1) _
                & Delimiter & Mid(strText, lngIndex)
            End If
            blnPreviousLetterUpper = True
        Case Else
            blnPreviousLetterUpper = False
        End Select
        lngIndex = lngIndex + 1
     Loop
     
    SplitCamelText = strText
    Exit Function
     
Err_SplitCamelText:
    SplitCamelText = XlCVError.xlErrValue
    Exit Function
End Function

Function seren(txt As String) As String
 
    'Written by Jindon
   'http://www.ozgrid.com/forum/member.php?u=6052
   'For Ozgrid
   'http://www.ozgrid.com/
    With CreateObject("vbscript.regexp")
        .Pattern = "([A-Z][^A-Z]+)"
        .Global = True
        seren = Trim(.Replace(txt, Chr(32) & "$1"))
    End With
End Function

If you enjoy these, you might also enjoy
Extracting Words From Text in Excel
Extract Word From a Text String
Extract Last Word

Until next month, keep Excelling

Got any Questions? Free Excel Help

Complete Excel Training Course Special!
We don't teach Excel from a manual, we teach Excel from experience!

Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

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

FREE Download!

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

FREE Excel Help