View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] nikkiws@kol.co.nz is offline
external usenet poster
 
Posts: 1
Default compile error automation error

Hi,

I have a spreadsheet with VBA code that works fine on Office 2003 and
2000 - was created on 2000.

Gets a compile error automation error when opened on 2001.

User has reported the error and I can't recreate it so can't give too
much info...
User also has multiple PC's with different versions of excel so am
assuming that I may need to fix for other versions also.

I have asked user to check references and am waiting for a reply but am
not convinced this will be the problem.

Google search turned up this...

This error is caused by the setup package for your application
installing
incompatible versions of OLE Automation system files. A knowledge base

article documenting this problem is in the final stages of publication.

The following is the text of that article:

Article goes on to list dll's that require re-installation of
re-distributable versions..blah blah blah.

Am wondering if anyone knows anything more...here is the code
NB: Errors on 2nd sub

Private Sub Workbook_Open()
ActiveSheet.Unprotect "ov1n3"
Application.Goto Reference:="Query_from_Excel_files"
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Protect "ov1n3", DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveWindow.SmallScroll Down:=-228
Range("A20").Select
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Intersect(Target, Range("Y2")) Is Nothing Then
GoTo Protect:
Else
ActiveSheet.Unprotect "ov1n3"
Application.Goto Reference:=" Emps_tkmp__a "
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:=" Emps_tkmp__B "
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:=" Emps_tkmp__C "
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:=" Emps_tkmp__D "
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:=" Emps_tkmp__GH "
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("A200:A20").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Range("U20:X200").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("AB20:AC200").Select
Selection.Locked = False
Selection.FormulaHidden = False

ActiveSheet.Protect "ov1n3", DrawingObjects:=True, Contents:=True,
Scenarios:=True
End If

Protect:
If Intersect(Target, Range("AD13:AF13")) Is Nothing Then
Exit Sub
Else
ActiveSheet.Protect "ov1n3"
End If


End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
If Intersect(Target, Range("AF10:AF11")) Is Nothing Then
GoTo Unprotect:
Else
If Range("AB17") 0 Then
Range("AF10:AF11").Value = "Error"
MsgBox ("Incomplete Timesheet - all employees must have hours/tally
and/or leave entered. Search TTL Value column for red background")
GoTo Unprotect:
Else
Range("AF10:AF11").Value = "OK"
End If
End If

Unprotect:
If Intersect(Target, Range("AD13:AF13")) Is Nothing Then
GoTo Calendar:
Else
Module2.Openpassword
End If

Calendar:
If Intersect(Target, Range("B6")) Is Nothing Then
Exit Sub
Else
Module1.OpenCalendar
End If


End Sub

Cheers
Nikki