I have a commander bar that is attached to a Excel file. It use code to run a macro to open a file on our shared drive. the file it opens has an attached command bar.
When open file 1 from the shared drive it opens correctly--no problems.
When I open the file using using the button on the command bar I get an error. When the sheet opens it is suppose to goto Sheet3. but I get an error when opening using button on commnand bar.
The "Other Book" names are in column G and the hyperlink path is in column H of Sheet1.
Sample of link in column H of Sheet1 - S: 2 Test BookTest1.xls
Code follows:<pre>Sub GetHypers()
Dim rCell As Range
On Error Resume Next
Set cbp = Application.CommandBars("CC Book").Controls("Other Books")
'Delete existing ones.
For Each cbc In cbp.CommandBar.Controls
cbc.Delete
Next
With Workbooks(ThisWorkbook.Name).Sheets(1)
If .Range("G1")<> "" Then
For Each rCell In .Range("G1", .Range("G65536").End(xlUp))
strName = rCell
strActionName = rCell.Offset(0, 1)
cbp.Controls.Add().Caption = strName
cbp.Controls(strName).OnAction = "AssHyper"
Next rCell
End If
End With
End Sub
Sub AssHyper()
On Error Resume Next
With Application.CommandBars.ActionControl
strActionName = Workbooks(ThisWorkbook.Name).Sheets(1).Cells(.Index,8).Text
ThisWorkbook.FollowHyperlink (strActionName)
End With</pre>
This message was edited by em on 2002-09-27 05:56
This message was edited by em on 2002-09-27 05:59
This message was edited by em on 2002-10-18 09:51
When open file 1 from the shared drive it opens correctly--no problems.
When I open the file using using the button on the command bar I get an error. When the sheet opens it is suppose to goto Sheet3. but I get an error when opening using button on commnand bar.
The "Other Book" names are in column G and the hyperlink path is in column H of Sheet1.
Sample of link in column H of Sheet1 - S: 2 Test BookTest1.xls
Code follows:<pre>Sub GetHypers()
Dim rCell As Range
On Error Resume Next
Set cbp = Application.CommandBars("CC Book").Controls("Other Books")
'Delete existing ones.
For Each cbc In cbp.CommandBar.Controls
cbc.Delete
Next
With Workbooks(ThisWorkbook.Name).Sheets(1)
If .Range("G1")<> "" Then
For Each rCell In .Range("G1", .Range("G65536").End(xlUp))
strName = rCell
strActionName = rCell.Offset(0, 1)
cbp.Controls.Add().Caption = strName
cbp.Controls(strName).OnAction = "AssHyper"
Next rCell
End If
End With
End Sub
Sub AssHyper()
On Error Resume Next
With Application.CommandBars.ActionControl
strActionName = Workbooks(ThisWorkbook.Name).Sheets(1).Cells(.Index,8).Text
ThisWorkbook.FollowHyperlink (strActionName)
End With</pre>
This message was edited by em on 2002-09-27 05:56
This message was edited by em on 2002-09-27 05:59
This message was edited by em on 2002-10-18 09:51