I have a "set-up" macro that sets the print area, hides a number of columns, and filters certain remaining colums to suit the particular report being generated, and halts to allow the user to preview the filtered data..
As a part of the set up, the macro also sets the properties of an autoshape from "no fill, no line" (ie hidden), to "red fill, black line" (ie visible), and changes cell g10 font properties to display a message, which was sitting cunningly ?? hidden by being formatted white font. The now visible auto shape has been pre-assigned a further macro ready to re-set the filters, unhide the columns etc, (and then re-hide itself) ready for the next report. [Having written this down here, it seems (and probably is) horribly primitive, but bear in mind I'm a civil engineer who writes VB code by keystroking and fine tuning, not a programmer, and at least it half works !!]
Everything is fine until I try and share the workbook, across an NT network.
The sharing opens up OK, but when a user trys the macro, he/she gets the following message displayed ....
Runtime error '-2147467259 (80004005)':
Method of 'Select' of object 'Shape' failed.
And at that point the macro stops executing, but because it is in shared mode, VB editor de-bugger is not available to trace the error.
Is there any way around this..........
VB context help suggests a code line "On error resume" put in somewhere before the object properties fiddling, (or something like that....but I don't know how to syntax it.
Any assistance ????
I have appended the relevant macro code below.
TextInput = InputBox("Please enter Project Leaders initials", " 3 Initials Required")
Sheets("CURRENT JOBS").Range("D15") = TextInput
Sheets("INDATA").Select
Selection.AutoFilter Field:=126, Criteria1:="TRUE"
Selection.AutoFilter Field:=106, Criteria1:="TRUE"
Selection.AutoFilter Field:=78, Criteria1:="FALSE"
Application.Goto Reference:="PROJLEADERHIDE"
Selection.EntireColumn.Hidden = True
Range("G10").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Shapes("Oval 1364").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Range("C10").Select
End Sub
Thanks for your interest
Beach
As a part of the set up, the macro also sets the properties of an autoshape from "no fill, no line" (ie hidden), to "red fill, black line" (ie visible), and changes cell g10 font properties to display a message, which was sitting cunningly ?? hidden by being formatted white font. The now visible auto shape has been pre-assigned a further macro ready to re-set the filters, unhide the columns etc, (and then re-hide itself) ready for the next report. [Having written this down here, it seems (and probably is) horribly primitive, but bear in mind I'm a civil engineer who writes VB code by keystroking and fine tuning, not a programmer, and at least it half works !!]
Everything is fine until I try and share the workbook, across an NT network.
The sharing opens up OK, but when a user trys the macro, he/she gets the following message displayed ....
Runtime error '-2147467259 (80004005)':
Method of 'Select' of object 'Shape' failed.
And at that point the macro stops executing, but because it is in shared mode, VB editor de-bugger is not available to trace the error.
Is there any way around this..........
VB context help suggests a code line "On error resume" put in somewhere before the object properties fiddling, (or something like that....but I don't know how to syntax it.
Any assistance ????
I have appended the relevant macro code below.
TextInput = InputBox("Please enter Project Leaders initials", " 3 Initials Required")
Sheets("CURRENT JOBS").Range("D15") = TextInput
Sheets("INDATA").Select
Selection.AutoFilter Field:=126, Criteria1:="TRUE"
Selection.AutoFilter Field:=106, Criteria1:="TRUE"
Selection.AutoFilter Field:=78, Criteria1:="FALSE"
Application.Goto Reference:="PROJLEADERHIDE"
Selection.EntireColumn.Hidden = True
Range("G10").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Shapes("Oval 1364").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Range("C10").Select
End Sub
Thanks for your interest
Beach