Excel开发学习笔记:根据工作表worksheet内容控制按钮的状态
开发环境基于VSTO,具体配置:visual studio 2010,VB .Net,excel 2007,文档级别的定制程序。
在Ribbon工具栏中有2个功能按钮,它们是否可用取决于worksheet(sheet1)中的内容。
button1需要在sheet1中存在某个关键字(比如“默认勾选”)的时候可用,在sheet1初始化和内容变更事件中添加代码,下面的Ribbon1.Button1是我项目中的工具栏和按钮名称
Public Function checkRow() As Boolean
Dim findCheck As Excel.Range = findLastCell("默认勾选") 'findLastCell的定义见之前的blog
If (findCheck Is Nothing) Then
Return False
Else
Return True
End If
End Function
Private Sub Sheet1_Change(Target As Microsoft.Office.Interop.Excel.Range) Handles Me.Change
Globals.Ribbons.Ribbon1.Button1.Enabled = checkKpiRow()
End Sub
Private Sub Sheet1_Startup() Handles Me.Startup
Globals.Ribbons.Ribbon1.Button1.Enabled = checkKpiRow()
End Sub
Dim findCheck As Excel.Range = findLastCell("默认勾选") 'findLastCell的定义见之前的blog
If (findCheck Is Nothing) Then
Return False
Else
Return True
End If
End Function
Private Sub Sheet1_Change(Target As Microsoft.Office.Interop.Excel.Range) Handles Me.Change
Globals.Ribbons.Ribbon1.Button1.Enabled = checkKpiRow()
End Sub
Private Sub Sheet1_Startup() Handles Me.Startup
Globals.Ribbons.Ribbon1.Button1.Enabled = checkKpiRow()
End Sub
button2需要当用户选中sheet1中某个关键字单元格(比如“名称”)的时候可用,在sheet1初始化、SelectionChange事件、工作表激活事件中添加代码
Private Function isSelectNameCell() As Boolean
With Application
If (.ActiveCell.Value Is Nothing) Then
Return False
End If
If (.ActiveCell.Value.ToString = "名称") Then
Return True
End If
Return False
End With
End Function
Private Sub Sheet1_Startup() Handles Me.Startup
Globals.Ribbons.Ribbon1.Button2.Enabled = isSelectNameCell()
End Sub
Private Sub Sheet1_SelectionChange(Target As Microsoft.Office.Interop.Excel.Range) Handles Me.SelectionChange
Globals.Ribbons.Ribbon1.Button2.Enabled = isSelectNameCell()
End Sub
Private Sub Sheet1_ActivateEvent() Handles Me.ActivateEvent
Globals.Ribbons.Ribbon1.Button2.Enabled = isSelectNameCell()
End Sub
With Application
If (.ActiveCell.Value Is Nothing) Then
Return False
End If
If (.ActiveCell.Value.ToString = "名称") Then
Return True
End If
Return False
End With
End Function
Private Sub Sheet1_Startup() Handles Me.Startup
Globals.Ribbons.Ribbon1.Button2.Enabled = isSelectNameCell()
End Sub
Private Sub Sheet1_SelectionChange(Target As Microsoft.Office.Interop.Excel.Range) Handles Me.SelectionChange
Globals.Ribbons.Ribbon1.Button2.Enabled = isSelectNameCell()
End Sub
Private Sub Sheet1_ActivateEvent() Handles Me.ActivateEvent
Globals.Ribbons.Ribbon1.Button2.Enabled = isSelectNameCell()
End Sub