• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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.
0
footswitch
Asked:
footswitch
  • 3
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now