Back to Excel Newsletter Archives Back to Excel Newsletter Archives
EXCEL DEVELOPMENT AT A DISCOUNT PRICE, BUT NOT SKILLS
Professional Consultancy
AD FREE QUESTION FORUMS
Tired of seeing ads on the Ozgrid forum?
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
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 FunctionIf 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! 
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!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help