Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button misfire
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button misfire
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 -- If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button misfire
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 "Bishop" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change from a Command Button to a Form Button | Excel Programming | |||
Userform Command Button not available until another command buttonhas been used | Excel Programming | |||
Deselect Command Button by Selecting another Command Button | Excel Programming | |||
Wanting to Create A Command Button Command | Excel Programming | |||
VB's Command Button vs Form's Command Button | Excel Programming |