Two things you can try
Sub Test1()
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", False
DoEvents
Worksheets("Main").Range("B1").Select
End Sub

or

Sub Test1()
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime now + TimeValue("00:00:02"),"Test2"
SendKeys "{DEL}", False
End sub

Sub Test2
Worksheets("Main").Range("B1").Select
End Sub

I haven't tested either. The appearance of the dialog may affect
performance - do you really want the dialog to appear.

--
Regards,
Tom Ogilvy



"Jeff" wrote in message
...
Thanks Tom,
It only works with "false"
Here is another problem I have, when I tried to select cell("B1"), I
don't get the prompt.
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", False
Worksheets("Main").Range("B1").Select

Everything works fine if I comment out the last line. Is it a way to
put a delay (so I can click yes before set focus to B1?)

Thanks a lot for your help


"Tom Ogilvy" wrote:

I did a Get External Data to the Northwind.mdb and put that in sheet
Main.

then went to another sheet and ran this:

Sub AAAAA()
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", False
End Sub

I got the prompt you describe.

I didn't test it with True, so try changing to False. And have it as
the
last command in your macro.

--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Thanks for trying, it still doesn't work.

Here is my code:
Worksheets("Main").Select
Worksheets("Main").Cells.Select
SendKeys "{DEL}", True

as the codes finished, once again the contents are cleared, but the
add-in button is still gray out. One thing I noticed is when I hit
the
delete key manually, a msgbox pops up w/ the msg (that doesn't happen
when
I
use the sendkeys, clear, or clearcontents):
"The range you deleted is associated with a query that retrieves
data
from an external source. do you want to delete the query in addition
to
the
range? if you click no, ..."


Thanks
Jeff
"Tom Ogilvy" wrote:

Sounds like you need to try using SendKeys.

because if you record this action it uses ClearContents - and you
say
that
doesn't work.

--
Regards,
Tom Ogilvy




"Jeff" wrote in message
...
Hi,
I've tried to use "clearcontents" & "clear" but neither works
the
way I
wanted (I was using a 3rd party add-in, the add-in is disabled
unless
"delete" is used. The down side is that would shift all my
controls,
too
(putting them all into the upper left corner). However, if I
manually
select
the whole worksheet, and press the delete key, that seems to work
exactly
what I want (which is to clear all the contents, and yet leaving
all
the
controls where there are without shifting).

Please help
Jeff