PDA

View Full Version : Solved: Error found in codes given in KB



arrun
10-06-2012, 05:27 AM
Hi all, I was trying to implement the codes from http://www.vbaexpress.com/kb/getarticle.php?kb_id=614, however got following error:

Compile error:
Variable not defined

Excel also highlights 'ActiveDocument'

Can somebody points me if I am missing something?

Thanks,

snb
10-06-2012, 05:41 AM
Change it into

Activeworkbook
or
thisworkbook.

Do not mix up Word & Excel.

arrun
10-06-2012, 05:56 AM
both are not working. If I put 'activeworkbook' then I am getting error as:

Run-time error '438'
Object doesn't support this property or method

And, with 'thisworkbook' case, the error is:
Compile error:
Method or data member not found

shrivallabha
10-06-2012, 06:33 AM
Post your requirement instead of using misleading title. You will get better response.

Perhaps this is what you want:
Public Sub DeleteTextBox()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = msoOLEControlObject Then 'ActiveX TextBox
shp.Delete
End If
Next shp
End Sub

arrun
10-06-2012, 06:57 AM
Thanks Shrivallabha, for your help. However I wanted to search a specific word in textbox and if it is there then only to delete that textbox.

The condition InStr(1, aShape.AlternativeText, "instructions", vbTextCompare) > 0' (as given by lucas) is not probably working.

However your trick to use 'ActiveSheet.Shapes' is working perfectly :)

Any idea on how to make it work?

Thanks,

shrivallabha
10-06-2012, 07:07 AM
....
The condition InStr(1, aShape.AlternativeText, "instructions", vbTextCompare) > 0' (as given by lucas) is not probably working.
.......
How is this Textbox created?
e.g. Insert Shape | Edit Text

Please explain the way you have them.

arrun
10-06-2012, 07:11 AM
[QUOTE=shrivallabha]How is this Textbox created?
e.g. Insert Shape | Edit Text

I am using excel-2010. Therefore textbox is created from the "Insert" ribbon, then "Text Box". Then I just add few Words within that newly created textbox

Is this information sufficient? Please let me know.

Thanks,

shrivallabha
10-06-2012, 07:22 AM
[quote=shrivallabha]How is this Textbox created?
e.g. Insert Shape | Edit Text

I am using excel-2010. Therefore textbox is created from the "Insert" ribbon, then "Text Box". Then I just add few Words within that newly created textbox

Is this information sufficient? Please let me know.

Thanks,
Public Sub DeleteTextBox()
Dim shp As Shape
On Error Resume Next
For Each shp In ActiveSheet.Shapes
If InStr(1, shp.TextFrame.Characters.Text, "Instructions", vbTextCompare) > 0 Then
shp.Delete
End If
Next shp
End Sub

snb
10-06-2012, 07:58 AM
Did you notice the KB is meant for Word ?

shrivallabha
10-06-2012, 08:11 AM
Did you notice the KB is meant for Word ?
Maybe 'MS-Word Application' would avoid confusion altogether.

arrun
10-06-2012, 08:24 AM
Thanks Shrivallabha, for your help. It is working perfectly. However I have one more question. Instead of deleting the Textbox if I want to color that entire text box with Red then what will be the right code? I have tried with following, however could not achieve:

If InStr(1, aShape.TextFrame.Characters.Text, Search_String, vbTextCompare) > 0 Then
aShape.ShapeRange.Fill.Color = "red"
End If

shrivallabha
10-07-2012, 03:03 AM
Maybe try:
aShape.ShapeRange.Fill.Color = vbRed

arrun
10-07-2012, 06:39 AM
nope........... this code is not working too!!! Any better idea?

Thanks,

shrivallabha
10-07-2012, 07:56 AM
nope........... this code is not working too!!! Any better idea?

Thanks,
I thought you had idea but was wrong :devil2:

Test:
aShape.Fill.ForeColor.RGB = 255