Toolbar Error
Below is the code I have for creating a toolbar(CatalystDumpToolBar),
creating a button for the toolbar (AddCustomControl), and the macro(CatalystToTally) that runs when the button is clicked. Now this exact same code was working fine Friday. But when I came in this morning it does nothing. When I go through the macro step by step it never even looks at the ExportedData spreadsheet. Instead I get the following error: wb.Name = <Object variable or With block variable not set What could be causing this? 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 Integer Dim EDLastRow As Integer With Sheets("Catalyst Dump") CDLastRow = .Range("A" & Rows.Count).End(xlUp).Row .Columns("D").ColumnWidth = 13 End With 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 Set CBar = CommandBars("CDToolBar") Set CTTally = CBar.Controls.Add(Type:=msoControlButton) With CTTally .FaceId = 1763 .OnAction = "CatalystToTally" End With CBar.Visible = True End Sub |
Toolbar Error
I think I figured out the problem. This morning when I opened up my
spreadsheet I noticed a 2nd copy of my PERSONAL.xls spreadsheet opened up as well. I thought this a bit odd but closed it and moved on. Apparently, a 2nd Workbooks Collection opened up and that's what caused it. So when my code was cycling through all wb in Workbooks it was looking at the first Workbooks collection and not the new that was opened. I closed down all my spreadsheets and re-opened them. Works fine now. "Bishop" wrote: Below is the code I have for creating a toolbar(CatalystDumpToolBar), creating a button for the toolbar (AddCustomControl), and the macro(CatalystToTally) that runs when the button is clicked. Now this exact same code was working fine Friday. But when I came in this morning it does nothing. When I go through the macro step by step it never even looks at the ExportedData spreadsheet. Instead I get the following error: wb.Name = <Object variable or With block variable not set What could be causing this? 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 Integer Dim EDLastRow As Integer With Sheets("Catalyst Dump") CDLastRow = .Range("A" & Rows.Count).End(xlUp).Row .Columns("D").ColumnWidth = 13 End With 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 Set CBar = CommandBars("CDToolBar") Set CTTally = CBar.Controls.Add(Type:=msoControlButton) With CTTally .FaceId = 1763 .OnAction = "CatalystToTally" End With CBar.Visible = True End Sub |
All times are GMT +1. The time now is 11:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com