Previous Topic Next Topic

Macro to delete created object

Solved/Closed
Trowa - Mar 4, 2010 at 09:10 AM
 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

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 4, 2010 at 09:27 AM
ActiveSheet.Shapes is an array object. You want to say ActiveSheet.Shapes(0).Delete
0
Trowa
Mar 5, 2010 at 08:03 AM
Thanks Rizvisia, although I had to replace the (0) by (1), I got the point.

Best regards,
Trowa
0
Trowa > Trowa
Mar 5, 2010 at 08:40 AM
Damn, it works on an empty sheet, but not on the sheet I want it to work on.

Here is my file:
https://authentification.site/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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > Trowa
Mar 5, 2010 at 09:56 AM
How about use a modular form. Unload the form when wait is over ?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Mar 5, 2010 at 06:36 PM
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.
0
Trowa > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Mar 8, 2010 at 08:50 AM
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
0