Identify and delete the specified objects names

[newbie]

I used the following code that allows me to identify and delete a specific
object in the worksheet via a user interface. The code works this morning but
don't know why, when or how, this Runtime Error 1004: Application or
object-defined error. appears at the line every time the program tries to
delete the object. Someone suggested me to use clean registry but I have not
really tried it yet. Is there a way to avoid this problem? I hope to find a
simple way that can perform the same function as the following code? Your
advice or help will be greatly appreciated.

Thanks.

Public Sub Deleteonstrfr()
Dim Obj As Object

For Each Obj In Sheets("SLD").Shapes
If Left(Obj.Name, 7) = "onstrfr" Then
Obj.Delete '***Run-time Error found at here'
End If
Next Obj

End Sub

 

[Ryan H]

I didn't test this, but try this. Hope this helps! If so, click "YES" below.

Public Sub Deleteonstrfr()

Dim Shp As Shape

For Each Shp In Sheets("SLD").Shapes
If Left(Shp.Name, 7) = "onstrfr" Then
Shp.Delete
End If
Next Shp

End Sub

 

[newbie]

Thanks Ryan, I have used the code you provided but unfortunately, I still
encounter the same problem with the runtime error 1004 at Shp.Delete.

Look forward to your advice. Thanks again. Loy

 

[Ryan H]

This code ran fine for me. Is your sheet protected? Try unprotecting the
sheet and then reprotecting.

Public Sub Deleteonstrfr()

Dim Shp As Shape

For Each Shp In Sheets("SLD").Shapes
If Left(Shp.Name, 7) = "onstrfr" Then
Sheets("SLD").Unprotect '"password"
Shp.Delete
Sheets("SLD").Protect '"password"
End If
Next Shp

End Sub

 

[Rick Rothstein]

Tell us a little about this shape. What kind of shape is it? Where did it
come from? How did you give it the name it now has? Anything else about it
that you think might help us understand what your set up for it looks like?

 

[newbie]

Dear Rick,

sorry for my late response. Here is the sample code that I have used. The
runtime error 1004: application or object defined error occurs when the
program tried to execute WTGObjs.Delete function. The thing I felt strange
is that the sample piece of code that works for last few days but does not
work days after. Why is that? Is it some problems with the virtual memory or
registry?

Look forward to your advice.

Best wishes, Loy

P/S: The name of the worksheet is given in capital letter. I have a
wtgbuscombo box to select number of objects I need to insert into another
worksheet. Each time before the insertion, the program will delete the
previous objects from the target worksheet. The error happens when the
program identifies and delete the objects. I appreciate your advice. Many
thanks.


SHEET1(SYS)

Private Sub wtgbuscombo_Change()
Select Case wtgbuscombo.Text
Case "2"
WTGValue = 2
Case "4"
WTGValue = 4
Case "6"
WTGValue = 6
Case "8"
WTGValue = 8
'myValue = 0 'Disable the combo box automatically - future work
End Select
Call MyCallProcedure
End Sub


Private Sub MyCallProcedure()
Call DeleteWTGbus
Call Sheet2.WTGBusBar(WTGValue, WTGXConnect)
Call Sheet2.OffshoreTrfr(OffType, WTGValue, WTGXConnect)
End Sub


MODULE 1

Public Sub DeleteWTGbus()

Dim WTGsObj As Object

For Each WTGObjs In Sheets("SLD").Shapes
If Left(WTGObjs.Name, 7) = "offWTGs" Or Left(WTGObjs.Name, 7) =
"offtrfr" Then
WTGObjs.Delete <----" RUN TIME ERROR 1004"
End If
Next WTGObjs

End Sub



SHEET2(SLD)

Public Sub WTGBusBar(ByVal myValue As Integer, myType As Integer)

Application.ScreenUpdating = False

Select Case myType
Case 0
If myValue = 2 Then
Sheets("LIB").Shapes("LibWTGs").Copy

Sheets("SLD").Select
ActiveSheet.Paste
Selection.Name = "offWTGsbus1"
Selection.Left = 772
Selection.Top = 1070

ActiveSheet.Paste
Selection.Name = "offWTGsbus2"
Selection.Left = 988
Selection.Top = 1070

ElseIf myValue = 4 Then
…….

Case 1
…….

End Select

Sheet1.Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[newbie]

Dear Rick,

Thanks for your reply. The shapes are drawn using an autoshape and grouped
as an object in a worksheet named as "LIB" (library). The idea is to identify
objects from the LIB and insert to the worksheet "SLD". I have a combo box
which allows me to select number of specified objects to be inserted. The
example of the code is attached.

The code worked fine for the first few days and then for no reason, this
runtime error 1004: application and object defined error appears. I didnt
change the code..someone told me that i need to clean the registry or empty
the temp files but i have tried that and it still doesnt work. your advice is
greatly appreciated.

MODULE 1
Public Sub DeleteWTGbus()

Dim WTGsObj As Object

For Each WTGObjs In Sheets("SLD").Shapes
If Left(WTGObjs.Name, 7) = "offWTGs" Or Left(WTGObjs.Name, 7) =
"offtrfr" Then
WTGObjs.Delete <----- "RUN TIME ERROR 1004"
End If
Next WTGObjs

End Sub


SHEET(SYS)

Private Sub wtgbuscombo_Change()

Select Case wtgbuscombo.Text
Case "2"
WTGValue = 2
Case "4"
WTGValue = 4
Case "6"
WTGValue = 6
Case "8"
WTGValue = 8
'myValue = 0 'Disable the combo box automatically - future work
End Select

Call MyCallProcedure

End Sub

Private Sub MyCallProcedure()

Call DeleteWTGbus
Call Sheet2.WTGBusBar(WTGValue)
Call Sheet2.OffshoreTrfr(OffType, WTGValue)
End Sub



SHEET2(SLD)

Public Sub OffshoreTrfr(ByVal myType As Integer, myWTGbusValue As Integer)

Application.ScreenUpdating = False
Select Case myWTGbusValue
Case 2
Select Case myType
Case 1

Sheets("LIB").Shapes("Liboff1x2wdgtrfr").Copy
Sheets("SLD").Select
Sheets("SLD").Paste
Application.CutCopyMode = False
Selection.Name = "offtrfr1x2wdg1"
Selection.Left = 825
Selection.Top = 799

Case 2
Sheets("LIB").Shapes("Liboff2x2wdgtrfr").Copy
Sheets("SLD").Select
Sheets("SLD").Paste
Application.CutCopyMode = False
Selection.Name = "offtrfr2x2wdg1"
Selection.Left = 797
Selection.Top = 799

Case 3
Sheets("LIB").Shapes("Liboff1x3wdgtrfr").Copy
Sheets("SLD").Select
Sheets("SLD").Paste
Application.CutCopyMode = False
Selection.Name = "offtrfr1x3wdg1"
Selection.Left = 826
Selection.Top = 799

End Select
Case 4
........
Case 6
........

Case 8
........

End Select

Sheet1.Activate
Application.ScreenUpdating = True

End Sub

 

[Ryan H]

In Module 1 you have WTGsObj declared as an Object, but you use WTGObjs in
your For Each...Loop. You need to change your variable delcaration to match
the variable you used in your loop. Also, I would highly recommend putting
Option Explicit at the top of all your modules when you are writing code. It
will instantly identfy problems like this in the furture which will prevent
many headaches. I'm not sure if this is your exact problem, but it can be a
start to finding the problem. Hope this helps! If so, let me know, click
"YES" below.


Module 1:

Option Explicit
__________________________________

Public Sub DeleteWTGbus()

Dim WTGObjs As Object

For Each WTGObjs In Sheets("SLD").Shapes
If Left(WTGObjs.Name, 7) = "offWTGs" Or Left(WTGObjs.Name, 7) =
"offtrfr" Then
WTGObjs.Delete '<----- "RUN TIME ERROR 1004"
End If
Next WTGObjs

End Sub

 

[newbie]

Thanks Ryan. After inserting the Option Explicit, i realise several mistakes
in my code that eventually fix the runtime error.

Best wishes, Loy