Command Button misfire

[Bishop]

The below code creates a toolbar, adds a button to that toolbar and assigns a
sub to that button. When I name a specific workbook, say ("Bothell C&A PF
May wk 1 bm"), it works fine when I run it from within VBE. But when I try
to use a wild card, say ("*C&A PF*"), I get a run-time '9' error. Also, if I
try to use the actual button from a spreadsheet it says it can't find X
spreadsheet and then lists the name. But the name it lists is not even a
spreadsheet I have open or have used in a long time. I'm using 2007 so the
button is in Add-Ins. I would like to make this work from any spreadsheet.

Sub CatalystDumpToolBar()

Dim CDToolBar As CommandBar

Set CDToolBar = CommandBars.Add(temporary:=True)
With CDToolBar
.Name = "CDToolBar"
.Position = msoBarTop
.Visible = True
End With
End Sub

Sub CatalystToTally()

Dim wb As Workbook
Dim ws As Worksheet
Dim CDLastRow As Long 'Catalyst Dump
Dim EDLastRow As Long 'Exported Data

'This doesn't work
Workbooks("*C&A PF*.xls").Activate
'This does work... how can I use wildcards for this?
Workbooks("Bothell C&A PF May 09 wk1 bm.xls").Activate
CDLastRow = Workbooks("*C&A PF*.xlsm").Worksheets _
("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

For Each wb In Workbooks
'Test to see if wb's name is like "ExportedData*"
If wb.Name Like "ExportedData*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.ActiveSheet

With ws
.Rows("1:1").Delete Shift:=xlUp
EDLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("D").ColumnWidth = 13
.Columns("D").NumberFormat = "0"
.Rows("1:" & EDLastRow).Copy ThisWorkbook.Worksheets _
("Catalyst Dump").Rows(CDLastRow + 1)
End With
wb.Close savechanges:=False
End If
Next
End Sub

Sub AddCustomControl()

Dim CBar As CommandBar
Dim CTTally As CommandBarControl
Dim PFNum As CommandBarControl

Set CBar = CommandBars("CDToolBar")
Set CTTally = CBar.Controls.Add(Type:=msoControlButton)
Set PFNum = CBar.Controls.Add(Type:=msoControlButton)

With CTTally
.FaceId = 1763
.OnAction = "CatalystToTally"
End With

With PFNum
.FaceId = 643
.OnAction = "PFNumber"
End With

CBar.Visible = True

End Sub

 

[Jacob Skaria]

Bishop, If you are sure there is only one workbook by that name and want to
activate the workbook with a keyword paste the below function and call from
code..to activate..Try and feedback..

Call ActivateBookwithKeyword ("C&A PF")


Sub ActivateBookwithKeyword(strSearch As String)
For intTemp = 1 To Workbooks.Count
If InStr(1, Workbooks(intTemp).Name, strSearch, 1) <> 0 Then
Workbooks(intTemp).Activate
Exit Sub
End If
Next
End Sub

 

[JLGWhiz]

Probably going to need a For ...Each loop. Something like:

For Each wb In Application.Workbooks
If wb.Name Like "*C&A PF*.xls" Then
wb.Activate
End If
Next