Thread: Toolbar Error
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Bishop Bishop is offline
external usenet poster
 
Posts: 208
Default 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