Facelds

The following code gives you a table of the built-in button faces, as shown in Figure 15-6. There are more than 15,000 faces in Office 2007. Note that many FaceId values represent blank images, and that the same images appear repeatedly as the numbers increase.

a;

- — r

A '

0

e

0

E 1

& J

G

H

I

J-- K 1

a

1

11

jgjf.

-3Q

4

m

5B

SB

Tip

8H

90

io3i r

1

2

ii |E

12:=

13S

14

is®

16 4*>

170

10 □

19%

20®

3

21 Ä

22®

23

2*S

25

26 ■IS

27 £

28 (B

23 <ft

30 V

J

31 1

32 El

33 0

34

ffi

35 ™

36 E3

37'O

38 *

39 -»

4G #

&

41 +■

42 H

43 ¡ft

44&

45^?

46«

4-7*7

4BStf

49 f

50 1

6

si -a

52

53 flj

54

£5 =

56 6=

57

58

SS©

60 fi

7

51 ti

62 A

63 A'

64

65 El

56»

671

68®)

69 O

70 0

8

71 1

72 2

73 3

74 4

75 5

7B E

77 7

78 a

73 9

SO A

5

61 B

82 C

S3 D

84

E

85 F

65 G

67 H

88 I

69 J

90 K

10

91 L

92 M

93 N

94

O

95 P

96 O

97 R

98 S

99 T

100 U

V

101 V

102 W

103 X

104

y

105 Z

106 fii

107®

108'S?

109 ß.

110

a

111 V

112 Ü

113 B

114

i

115 U

1160}

117Ö

11S)t

119 11

120S

13

121 a

122=.

123 m

124

iff

1251®

125 ©

127®

12B«1

129 r»

130 Äs

14

131 □

132 *

133

134 ♦

135 ♦

136*

137 +

138 -

139|1|

140 ft

15

141«

142 S

143 m

144®

145 —

146 0

147 |

148 l

(49 p

150 H

IS

151

152 O

153

154 H

155 1

156 ►

157 H

158 S«

159 sh

160 Ss

1?

161 Sk

162 jttf

163®

164 Ej

165

160 ij

167

i.ssgi

169 Bi

170Q

«

171 tit

172®,

173«

174

P

175 HD

173®

177®

178 ■

179 FS

160 □

19

181 E3

182 ti

183®

184

185 [»

186 ^

187 f

ISBN'S

189 n

190 A

20

191 El

192 ^

193 w

194 £s

195 H

196 .il

197 <

198 Ok

199 ji

200 a

21

291 P

202

203 EÜ

204 41

205 s?

206 IS

207 su

208 ym

209%

210 ¿t

22

¿11»

212 ££

213

214

A*

215 0!

216^

217HSS

218 0

219ab|

220 P

23

221 a

222 a?

223 p

224 S

225 ®

226 E

227 =

228 ■

229 tW

230 □

24

231 0

232©

233 05

234

235 B

236(0

237 ffl

238 #

239^.

240 'a

25

241 ¿2.

242 IE:

243 \

244^,

245 ä_

24-6 ^

247 DJ

248'?]

24S,|

250 Ü

26

251 =

252 "«a

253 A

254 td

255 Al

256®,

257®,

258 £9

259 gä

260 ab1

27

261 M

262 ■ig

263 H

264 m

265®

266 W

267 S

268 0

269 0

270

23

271

272 Jl

273 8

274 O

275®

276©

277 or.

278 [E

279 H

260 OS

OA

.rnr- A =

H

> H US Level . All Levels FacelDs

JL°

Sffit-_S>

_

hi

Here is the code to list all the Facelds:

Sub

ListAllFaces()

Dim iFaceld As Integer 'Tracks current Faceld

Dim iColumn As Integer 'Tracks current column in worksheet

Dim iRow As Integer 'Tracks current row in worksheet

Dim ctl As CommandBarControl

Dim cbr As CommandBar

If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub

'Ignore errors and freeze screen

On Error GoTo Recover

Application.ScreenUpdating = False

'Create temporary command bar with single control button

'to hold control button face to be copied to worksheet

Set cbr = CommandBars.Add(Position:=msoBarFloating, _

MenuBar:=False, _

temporary:=True)

Set ctl = cbr.Controls.Add(Type:=msoControlButton, _

temporary:=True)

iRow = 1

Do

For iColumn = 1 To 10

iFaceId = iFaceId + 1

Application.StatusBar = "FaceID = " & iFaceId

'Set control button to current FaceId

ctl.FaceId = iFaceId

'Attempt to copy Face image to worksheet

ctl.CopyFace

ActiveSheet.Paste Cells(iRow, iColumn + 1)

Cells(iRow, iColumn).Value = iFaceId

Next iColumn

iRow = iRow + 1

Loop

Recover:

If Err.Number = 1004 Then Resume Next

Application.StatusBar = False

cbr.Delete

End

Sub

Note that when you run this macro, your computer may well freeze for a while. The CPU has to do a lot of hard work! You can follow the macro's progress by watching the status bar.

ListAllFaces creates a temporary toolbar, cbr, using the Add method of the CommandBars collection. The toolbar is declared:

□ Temporary, which means that it will be deleted when you exit Excel, if it has not already been deleted

□ Floating, rather than docked at an edge of the screen or a popup

□ Not to be a menu bar, which means that cbBar will be a toolbar

A temporary control is added to cbr, using the Add method of the Controls collection for the command bar, and assigned to ctl.

The Do...Loop continues looping until there are no more valid FaceId values. The Do...Loop increments iRow, which represents the row numbers in the worksheet. On every row, iColumn is incremented from 1 to 10. j represents the columns of the worksheet. The value of iRow is increased by 1 for every iteration of the code in the For...Next loop. iFaceId represents the FaceId. The FaceId property of ctl is assigned the value of iFaceId, and the resulting image is copied to the worksheet.

Some button images are blank and some are missing. The blank images are copied without error, but the missing images cause an error number 1004. When an error occurs, the code branches to the error trap at Recover:. If the error number is 1004, the code resumes executing at the statement after the one that caused the error, leaving an empty slot for the button image. Eventually the code gets to the last FaceId in Office. This causes error number -2147467259. At this point the code clears the status bar, removes the temporary command bar, and exits.

The information you have gathered with the last three exercises is not documented in any easily obtainable form by Microsoft. It is a valuable guide to the built-in button faces at your disposal. There is an add-in application, CBList.xla, available with the code that accompanies this book that makes it easy to generate these lists.

0 0

Post a comment