Runtime Error 1004???

[Guest]

I have the following code in a module saved as an Add-in. I wanted this
add-in to be activated everytime an excel file is open. When I run this code
while a file is open in the VBA editor, the code works fine. But when I open
a file, the error "Worksheets of object global failed 1004" pops up. I
removed the "Worksheets("data").activate" line to see if it would run but
then the error msg reads "Columns of object global failed 1004". I then
removed the first call hide_cols() and the error msg reads "Cells of object
global failed 1004". I've been searching for clues on the web still cant get
it to work. Any suggestions or help would be greatly apprectiated thanks.

Sub Auto_open()

Worksheets("Data").Activate

Call Hide_cols
Call Format_cols
Call Filter_SM
Call Page_Setup


End Sub
Sub Hide_cols()

Dim i As Integer

For i = 26 To 1 Step -1
If (i <> 2) And (i <> 3) And (i <> 5) And (i <> 6) And (i <> 9) And (i <>
15) And (i <> 24) Then
Columns(i).Select
Selection.EntireColumn.Hidden = True
End If
Next i

End Sub

Sub Format_cols()

Cells(1, "X").Value = "SsC"
Cells(1, "O").Value = "SL"
Cells(1, "E").Value = "AWS"
Cells(1, "I").Value = "BOH"
Cells(1, "AA").Value = "Pickbin"
Cells(1, "AB").Value = "SGF"
Cells(1, "AC").Value = "Diff+/-"
Range("E1:AC1").HorizontalAlignment = xlHAlignCenter
Cells(1, "AC").Font.Bold = True


With Worksheets("Data")
.Columns("O").NumberFormat = "00-00-00"
.Columns("B").AutoFit
.Columns("E").AutoFit
.Columns("O").AutoFit
.Columns("X").AutoFit
.Columns("I").AutoFit
.Columns("AA:AC").ColumnWidth = 9
.Columns("C").ColumnWidth = 39.3
End With

End Sub

Sub Filter_SM()

Columns("F").AutoFilter Field:=1, Criteria1:=">=2"
Columns("F").Select
Selection.EntireColumn.Hidden = True

End Sub

Sub Page_Setup()

With Worksheets("Data").PageSetup
.CenterHeader = Format(Now(), "mmmm dd, yyyy")
.RightHeader = "page &p"
.CenterHorizontally = True
.Zoom = 125
.Orientation = xlLandscape
.PrintGridlines = True
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.51)
.BottomMargin = Application.InchesToPoints(0.35)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintTitleRows = ActiveSheet.Rows(1).Address

End With

End Sub

 

[Guest]

The code you have is actually a long way from doing what you want to do...
Let me know if this is what you want. Every time that a workbook is opened it
checks for a sheet called Data. If that sheet exists then it reformats that
sheet as per your existing macros?

Just so we are on the same page that can be done but there is no undo after
a macro runs so any sheet called "data" will be reformatted, whether doing so
is appropriate or not.

 

[Guest]

Yea thats exactly what i want it todo. Also I need to put in an "if"
statement for activeworksheet.path so the add-in only formats excel files in
a particular folder. Thanks for your reply....

 

[George Nicholson]

Using Cells, Range or Columns properties without specifying a worksheet is
probably the primary cause of your 1004 errors. Your code assumes that Excel
will "know" you mean the ActiveSheet. Object assumptions like that are the
primary cause of 1004's. In this case Cells, Range and Columns are all
properties of more than one object (Application, Worksheet and Range). Excel
needs to know which one you intend.

HTH,

 

[Guest]

Here is some code for you to try. It just fires a message box when the
approprite file has been opened. You can modify it to deal with your data
sheet... If you need help reply back...

Create a blank workbook. In the code window add a class module. Name the
Class module clsXLEvents. Place this code in the class module.

Option Explicit
Private WithEvents xlApp As Excel.Application
Private Const SHEETNAME As String = "Data"
Private Const APPPATH As String = "C:\Test"

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub


Private Sub xlApp_WorkbookOpen(ByVal WB As Workbook)
If SheetExists(SHEETNAME, WB) And WB.Path = APPPATH Then
MsgBox "Tada"
End If
End Sub

Private Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function


Now in the thisworkbook module add the following code...
Option Explicit
Dim OpenEvent As clsXLEvents

Private Sub Workbook_AddinInstall()
Set OpenEvent = New clsXLEvents
End Sub

Private Sub Workbook_AddinUninstall()
Set OpenEvent = Nothing
End Sub

Private Sub Workbook_Open()
Set OpenEvent = New clsXLEvents
End Sub

 

[Guest]

Thanks Jim works perfect!! As you can tell iam a beginner at VBA. I have a
couple books but show nothing on what you've done. Clearly Iam far from what
i wanna be capable with the VBA tool in excel. Can you recommend any reading
material for advanced VBA stuff. Thanks again!!