我是这样做的: '在ThisWorkbook模块中: Private Sub Workbook_Open() Dim b As Shape For Each b In ThisWorkbook.ActiveSheet.Shapes b.OnAction = "test" Next End Sub '在“模块1”中: Public Sub test() MsgBox TypeName(Selection) End Sub 可是在test中的selection不是用户点击的shape。怎么解决?
Private Sub Workbook_Open() Dim b As Shape For Each b In ThisWorkbook.ActiveSheet.Shapes b.OnAction = "'ThisWorkbook.Test """ & b.Name & """'" Next End SubSub Test(objName) Dim sht As Worksheet Set sht = ActiveSheet With sht.Shapes(objName) If MsgBox("是否打开对象 """ & objName & """", vbYesNo) = vbYes Then .OLEFormat.Verb (xlVerbPrimary) End If End With End Sub
想了个DDE的办法供你参考 打开工程的属性,将“生成”选项卡将应用程序标题设为“MyApp”:Option Explicit Dim oExcel As Excel.Application Dim oWorksheet As WorksheetPrivate Const LINK_TOPIC_STR As String = "FORM_DDE_SINK" Private Sub Command1_Click() Set oExcel = New Application oExcel.Visible = True Set oWorksheet = oExcel.Workbooks.Open(App.Path & "\book1.xls").Sheets(1) End SubPrivate Sub Form_LinkExecute(CmdStr As String, Cancel As Integer) On Error Resume Next Set oWorksheet = oExcel.ActiveSheet With oWorksheet.Shapes(CmdStr) MsgBox "你点击了对象" & CmdStr End With End SubPrivate Sub Form_Load() With Me .LinkMode = 0 .LinkTopic = LINK_TOPIC_STR .LinkMode = 1 End With End SubExcel里面的宏改成这样:Private Const APP_NAME_STR As String = "MyApp" Private Const LINK_TOPIC_STR As String = "FORM_DDE_SINK"Private Sub Workbook_Open() Dim b As Shape For Each b In ThisWorkbook.ActiveSheet.Shapes b.OnAction = "'ThisWorkbook.Test """ & b.Name & """'" Next End SubSub Test(objName As String) SendDDEMessage objName Exit Sub Dim sht As Worksheet Set sht = ActiveSheet With sht.Shapes(objName) If MsgBox("是否打开对象 """ & objName & """", vbYesNo) = vbYes Then .OLEFormat.Verb (xlVerbPrimary) End If End With End SubSub SendDDEMessage(objName As String) On Error Resume Next Dim lChannel As Long lChannel = Application.DDEInitiate(APP_NAME_STR, LINK_TOPIC_STR) If lChannel <> 0 Then Application.DDEExecute lChannel, objName Application.DDETerminate lChannel End If End Sub
另外,在excel上点击shape在vba里触发什么事件了?请楼上高手赐教
'在ThisWorkbook模块中:
Private Sub Workbook_Open()
Dim b As Shape
For Each b In ThisWorkbook.ActiveSheet.Shapes
b.OnAction = "test"
Next
End Sub '在“模块1”中:
Public Sub test()
MsgBox TypeName(Selection)
End Sub 可是在test中的selection不是用户点击的shape。怎么解决?
Dim b As Shape
For Each b In ThisWorkbook.ActiveSheet.Shapes
b.OnAction = "'ThisWorkbook.Test """ & b.Name & """'"
Next
End SubSub Test(objName)
Dim sht As Worksheet
Set sht = ActiveSheet
With sht.Shapes(objName)
If MsgBox("是否打开对象 """ & objName & """", vbYesNo) = vbYes Then
.OLEFormat.Verb (xlVerbPrimary)
End If
End With
End Sub
打开工程的属性,将“生成”选项卡将应用程序标题设为“MyApp”:Option Explicit
Dim oExcel As Excel.Application
Dim oWorksheet As WorksheetPrivate Const LINK_TOPIC_STR As String = "FORM_DDE_SINK"
Private Sub Command1_Click()
Set oExcel = New Application
oExcel.Visible = True
Set oWorksheet = oExcel.Workbooks.Open(App.Path & "\book1.xls").Sheets(1)
End SubPrivate Sub Form_LinkExecute(CmdStr As String, Cancel As Integer)
On Error Resume Next
Set oWorksheet = oExcel.ActiveSheet
With oWorksheet.Shapes(CmdStr)
MsgBox "你点击了对象" & CmdStr
End With
End SubPrivate Sub Form_Load()
With Me
.LinkMode = 0
.LinkTopic = LINK_TOPIC_STR
.LinkMode = 1
End With
End SubExcel里面的宏改成这样:Private Const APP_NAME_STR As String = "MyApp"
Private Const LINK_TOPIC_STR As String = "FORM_DDE_SINK"Private Sub Workbook_Open()
Dim b As Shape
For Each b In ThisWorkbook.ActiveSheet.Shapes
b.OnAction = "'ThisWorkbook.Test """ & b.Name & """'"
Next
End SubSub Test(objName As String)
SendDDEMessage objName
Exit Sub
Dim sht As Worksheet
Set sht = ActiveSheet
With sht.Shapes(objName)
If MsgBox("是否打开对象 """ & objName & """", vbYesNo) = vbYes Then
.OLEFormat.Verb (xlVerbPrimary)
End If
End With
End SubSub SendDDEMessage(objName As String)
On Error Resume Next
Dim lChannel As Long
lChannel = Application.DDEInitiate(APP_NAME_STR, LINK_TOPIC_STR)
If lChannel <> 0 Then
Application.DDEExecute lChannel, objName
Application.DDETerminate lChannel
End If
End Sub
我现在解决的方法是:在用户机器上应用加载宏的方式(就是在xlstart中建立xla文件),把指定宏的内容写到加载宏中,让每个shape指定宏都调用xla里的子程序。这样虽然不是很好,但是能解决我目前的问题。
如果不用xla文件都写在vb中,那就完美了,请问高手有没有更好的办法。
Dim sCodes As String
sCodes = ""
sCodes = sCodes & vbCrLf & "Sub Test(objName As String)"
sCodes = sCodes & vbCrLf & " SendDDEMessage objName"
sCodes = sCodes & vbCrLf & "End Sub"
sCodes = sCodes & vbCrLf & ""
sCodes = sCodes & vbCrLf & "Sub SendDDEMessage(objName As String)"
sCodes = sCodes & vbCrLf & " On Error Resume Next"
sCodes = sCodes & vbCrLf & " Dim lChannel As Long"
sCodes = sCodes & vbCrLf & " lChannel = Application.DDEInitiate(""" & APP_NAME_STR & """, """ & LINK_TOPIC_STR & """)"
sCodes = sCodes & vbCrLf & " If lChannel <> 0 Then"
sCodes = sCodes & vbCrLf & " Application.DDEExecute lChannel, objName"
sCodes = sCodes & vbCrLf & " Application.DDETerminate lChannel"
sCodes = sCodes & vbCrLf & " End If"
sCodes = sCodes & vbCrLf & "End Sub" With Wb.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CountOfLines + 1, sCodes
End With
End Sub