How To: Excel Macro, Enable Small Caps in TextFrame

Hello,
I'm trying to activate Small Caps in a Text Frame via Macro in Excel 2007.

If you put some text in a Text Frame, and then choose the Format Cell Font option (Ribbon - First Tab - Font Settings - sorry my Office isn't in English), you have the option to enable Small Caps.

I want to do that via Macro.

Thanks in advance. If you need further explanation, please comment.
LVL 2
footswitchAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jamie98Commented:
This seems to be what you are looking for(I've tested and it works in the newer versions of Excel)
http://groups.google.com/group/microsoft.public.excel.misc/msg/d23f9bf70c761d7b
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
footswitchAuthor Commented:
Thank you, but I'm familiar with that alternative.
I'm looking for the native option existing since Office 2007.
0
footswitchAuthor Commented:
Meanwhile, I wasn't able to find the native option anywhere.

So I converted the code in the link to the scripting language that I'm using, AutoIt.

Thank you for your assistance.

; AutoIt v3.3.6.1
; www.autoitscript.com

#include <Excel.au3>
$oExcel = _ExcelBookNew()

$oExcel.Cells(1,1).Select
$oExcel.Cells(1,1).Value = "Test for Small caps ImpleMenTation"
$oExcel.Cells(1,1).Font.Size = 20
$oExcel.Cells(1,1).Font.Bold = True

__SmallCaps($oExcel)

; tries to transform the text of the selection into smallcaps
Func __SmallCaps(ByRef $oExcelApplication)
	Local $lCap
	Local $sCap
	Local $testStr
	With $oExcelApplication.Selection
		If $oExcelApplication.IsText(.Value) Then
			$lCap = .Characters(1, 1).Font.Size ; get the font size of the first char
			; Small caps is 20% smaller than normal caps. First, set small font size for everything:
			.Font.Size = Round($lCap * 0.80, 0) ; rounded to nearest integer
			$testStr = .Value ; store the original string
			; Turn everything into Caps: (you need to do this before messing with individual font sizes)
			.Value = StringUpper(.Value)
			; if you want to capitalize only the first letter of every word, enable the following line:
			;$testStr = $oExcel.Proper($testStr)
			; Now, for every capital letter, set it to the original font size:
			For $i = 1 To StringLen($testStr)
				If StringMid($testStr, $i, 1) == StringUpper(StringMid($testStr, $i, 1)) Then
					.Characters($i, 1).Font.Size = $lCap
				EndIf
			Next
		EndIf
	EndWith
EndFunc


If MsgBox(4+32,"Close Excel?","Do you want to close Excel?") = 6 Then
	_ExcelBookClose($oExcel,0,0)
EndIf

Exit

Open in new window

0
footswitchAuthor Commented:
The point was Small Caps in a Text Box! I only remembered when I got to the actual project.

So here's a quick and dirty example of the function adapted for TextFrame Objects:

; AutoIt v3.3.6.1
; www.autoitscript.com

#include <Excel.au3>
Global Const $xlFreeFloating = 3

$oExcel = _ExcelBookNew()

; text will be positioned vertically, because of the first parameter value = 2
Local $oTextBox = $oExcel.ActiveSheet.Shapes.AddTextBox(2, 100,100,100,100)

With $oTextBox.TextFrame
	.Characters.Text = "Test for Small caps ImpleMenTation"
	.Characters.Font.Size = 20
EndWith

__ExcelTextBoxSmallCaps($oExcel, $oTextBox.TextFrame)

; UNPROTECTED AGAINST INVALID OBJECTS
; transforms the text of a TextBox's TextFrame Object into smallcaps
; if you want to capitalize only the first letter of every word, set $bool_proper with True
Func __ExcelTextBoxSmallCaps(ByRef $oExcel, $oExcelTextFrame, $bool_proper = False)
	Local $lCap
	Local $sCap
	Local $testStr
	With $oExcelTextFrame
		$lCap = .Characters(1, 1).Font.Size ; get the font size of the first char
		; Small caps is 20% smaller than normal caps. First, set small font size for everything:
		.Characters.Font.Size = Round($lCap * 0.80, 0) ; rounded to nearest integer
		$testStr = .Characters.Text ; store the original string
		; Turn everything into Caps: (you need to do this before messing with individual font sizes)
		.Characters.Text = StringUpper($testStr)
		If $bool_proper Then $testStr = $oExcel.Proper($testStr)
		; Now, for every capital letter, set it to the original font size:
		For $i = 1 To StringLen($testStr)
			If StringMid($testStr, $i, 1) == StringUpper(StringMid($testStr, $i, 1)) Then
				.Characters($i, 1).Font.Size = $lCap
			EndIf
		Next
		.MarginBottom = .MarginBottom ; force redrawing of text box
	EndWith
EndFunc

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.