Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter January 2008

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

HALF PRICE SPECIALS


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 yournamed 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 aPivotTable  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 alsoassigned 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 FunctionEnd 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 WithEnd 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

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