N
nikkiws
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
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