B
Bishop
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
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