用户界面
每项功能都在称作Ribbon功能区中且它们位置都是固定仅快速访问工具栏(QAT)和先前版本工具栏相似可用来添加或删除命令
因此在Excel 2007中创建自定义菜单并为菜单项指定宏不像在Excel 2003中那样容易本文汇总了John Walkenbach、John McLea和Ron de Bruin所介绍技术- - - -技术基础
—— 识别工具栏图像
如果使用Excel 97或以后
版本您知道它使用
些图像在它内置菜单和工具栏中您能够通过设置FaceID属性为个特定整数在自定义菜单和工具栏中使用这些内置图像然而问题是如何知道图像所对应整数下面
子过程创建了个带有开始250个FaceID图像(见下图1所示)自定义工具栏创建了工具栏的后将鼠标指针放在按钮上面来找到和图像相应FaceID值但单击工具栏按钮不会产生任何效果
子过程没有在OnAction属性中分配任何宏当然您能够通过改变IDStart和IDStop值来看到更多图像最后个FaceID图像显示数字3518(也有些空白图像)
图1:创建
个FaceID工具栏当鼠标放在某图像上时将显示相应数字下面是子过程代码:
Sub ShowFaceIDs
Dim NewToolbar As CommandBar
Dim NewButton As CommandBarButton
Dim i As Integer, IDStart As Integer, IDStop As Integer
'如果已存在FaceIds工具栏则删除
On Error Resume Next
Application.CommandBars("FaceIds").Delete
On Error GoTo 0
'添加个空工具栏
Set NewToolbar = Application.CommandBars.Add _
(Name:="FaceIds", temporary:=True)
NewToolbar.Visible = True
'可以改变下面值来看到区别FaceIDs
IDStart = 1
IDStop = 250
For i = IDStart To IDStop
Set NewButton = NewToolbar.Controls.Add _
(Type:=msoControlButton, ID:=2950)
NewButton.FaceId = i
NewButton.Caption = "FaceID = " & i
Next i
NewToolbar.Width = 600
End Sub
此外也可以使用VBA代码在工作表中列出所有FaceID图像和相对应整数该代码由John D. McLean编写代码清单如下:
'在工作表中显示所有工具栏按钮图像/图标由36行100列组成
'对应最左/右列和最顶/底行中数字相加即为该图标号
Sub DisplayButtonFacesInGrid
Const cbName = "JDMTestToolBar"
Dim cBar As CommandBar, cBut As CommandBarControl
Dim r As Long, c As Integer, count As Integer
Application.StatusBar = "Creating Button FaceIDs ......."
Workbooks.Add
'创建 4周数字
For r = 0 To 35
Cells(r + 2, 1).Value = 100 * r: Cells(r + 2, 102).Value = 100 * r
Next r
For c = 0 To 99
Cells(1, c + 2).Value = c: Cells(38, c + 2).Value = c
Next c
Range("A1:A38").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("CX1:CX38").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("B1:CW1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("B38:CW38").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("E5").Select
Selection.Value = "Please wait .............."
With Selection.Font
.Name = "Arial"
.Size = 24
.Bold = True
.ColorIndex = 3
End With
Range("E5:J10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
Application.ScreenUpdating = False
With Selection
.ClearContents
.UnMerge
End With
On Error Resume Next
CommandBars(cbName).Delete
On Error GoTo 0
Set cBar = CommandBars.Add '创建带有个按钮临时工具栏
With cBar
.Name = cbName
.Top = 0
.Left = 0
.Visible = True
End With
r = 2: c = 2: count = 0 '在单元格Cell(2,2)中FaceID号为0
Set cBut = CommandBars(cbName).Controls.Add(Type:=msoControlButton)
With cBut
Do '循环所有FaceIDs
.FaceId = count
Cells(r, c).Select '分配至按钮然后复制到工作表
.CopyFace
Selection.PasteSpecial
Cells(1, 1).Copy
c = c + 1
If c >= 102 Then '更新复制位置
c = 2
r = r + 1
End If
count = count + 1
Loop While count < 3519 '3519是最大FaceID号
End With
Rows("1:38").RowHeight = 24.6 '增大单元格尺寸
Columns("A:CX").ColumnWidth = 5.56
With ActiveSheet.DrawingObjects '增大按钮尺寸
.ShapeRange.ScaleWidth 2#, msoFalse, msoScaleFromTopLeft
.ShapeRange.ScaleHeight 2#, msoFalse, msoScaleFromTopLeft
.ShapeRange.IncrementLeft 8.4
.ShapeRange.IncrementTop 3#
End With
Range(Cells(2, 2), Cells(37, 101)).Select '格式化网格线和背景
With Selection
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
'恢复Excel设置
CommandBars(cbName).Delete
On Error GoTo 0
Range("A1").Select
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub运行上面代码后将新建个工作簿并在该工作簿内列出所有内置图标图像最左列、最右列、最顶部、最底部为相应数字将某图标对应最左(或右)列数字和最顶行(或最底行)数字相加即为该图标对应数字 源文档点此下载(FaceIDsInGrid Macro)(注:上面
代码运行较慢需耐心等待)注:本教程为fanjy原创
豆豆学园整理首发于http://blog.excelhome.net
最新评论