Macro to delete created object [Solved/Closed]

- - Latest reply:  Trowa - Mar 9, 2010 at 09:17 AM
Hello,

With the following VB code I have created a wordart object:

Sub ShowMessage()
    ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, "Even geduld AUB.....", _
        "Arial Black", 48#, msoFalse, msoFalse, 102.75, 171.75).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 44
 End Sub


Now I would like to delete the created wordart object by creating another VB code.
I don't understand why the following code doesn't work:

Sub DeleteMessage()
ActiveSheet.Shapes.Delete
End Sub


What am I doing wrong?
Please advise.

Best regards,
Trowa
See more 

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
0
Thank you
ActiveSheet.Shapes is an array object. You want to say ActiveSheet.Shapes(0).Delete
Damn, it works on an empty sheet, but not on the sheet I want it to work on.

Here is my file:
http://www.speedyshare.com/files/21268189/Copy_of_PostPlanning.xls

When I click on the button on sheet "Alle opdrachten" range H1, all the green lines are moved to the next sheet (Voltooide opdrachten). Since this takes a few seconds I want to show a message that tells the user to wait a moment.
Therefore I created three codes in module 1.
1. to show the message. (ShowMessage)
2. move the green lines. (Voltooid)
3. delete the shown message. (DeleteMessage)
Then in the code for the button on sheet "Alle opdrachten" I call each code.

When I call Voltooid the code works.
When I call ShowMessage and DeleteMessage on an empty sheet it works.
But when I combine the codes it doesn't work anymore.

Please advise.

Best regards,
Trowa
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
> Trowa -
How about use a modular form. Unload the form when wait is over ?
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
> rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Here is more bad news for you. It does get deleted for me!!!. So why not you

One thing that I dont have is that the external ref that you have on H column like =IF(NETTO.WERKDAGEN(F4,G4,'Feestdagen data'!$A$3:$I$103)=0,"",NETTO.WERKDAGEN(F4,G4,'Feestdagen data'!$A$3:$I$103)). I get #names? error

But I still say mod form will be better, users will be stuck till the code is done execution.
> rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Strange that it works for you?
When I click the button the green lines will be removed, after that's done the message is displayed without being removed. Not exactly how I imagined it.

The reason you get a #names? error is because I use a dutch version of excel. "IF" get translated but apparently your excel version doesn't recognise the function NETTO.WERKDAGEN (NETWORKDAYS).

Anyway, I will try the mod form idea.
Will post back if I get stuck.

Thanks for your time,
Best regards,
Trowa
When I use a form to display a message, my initial code doesn't run untill I remove the form. This doesn't suit my needs.

Now I'm going for the the easiest solution. Create a hideable sheet with the message. Show the sheet before running my code and then hide it again.

While doing this I ran into an issue. My code got stuck on the following line:
Sheets("Alle opdrachten").Range("B4").Select

I changed it to:
Sheets("Alle opdrachten").Select
Range("B4").Select

And everything works fine again.
Can somebody tell me what the difference is between the two methods?

Best regards,
Trowa