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

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

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

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

Tom,
DoEvents doesn't work.
It doesn't matter if the dialog box pops up or not. If all possible I'd
rather pass in "yes" so the dialog box doesn't show up at all.

Thanks

"Tom Ogilvy" wrote:

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

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

Sub Test2
Worksheets("Main").Range("B1").Select
Application.displayAlerts = True
End Sub

Should suppress the popup and answer Yes.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
Tom,
DoEvents doesn't work.
It doesn't matter if the dialog box pops up or not. If all possible
I'd
rather pass in "yes" so the dialog box doesn't show up at all.

Thanks

"Tom Ogilvy" wrote:

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

the message box still appears.


"Tom Ogilvy" wrote:

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

Sub Test2
Worksheets("Main").Range("B1").Select
Application.displayAlerts = True
End Sub

Should suppress the popup and answer Yes.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
Tom,
DoEvents doesn't work.
It doesn't matter if the dialog box pops up or not. If all possible
I'd
rather pass in "yes" so the dialog box doesn't show up at all.

Thanks

"Tom Ogilvy" wrote:

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

This worked for me.

Sub AAAATest1()
Application.DisplayAlerts = False
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime Now + TimeValue("00:00:01"), "Test2", , True
SendKeys "{DEL}~", False
End Sub
Sub Test2()
Worksheets("Main").Range("B1").Select
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
the message box still appears.


"Tom Ogilvy" wrote:

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

Sub Test2
Worksheets("Main").Range("B1").Select
Application.displayAlerts = True
End Sub

Should suppress the popup and answer Yes.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
Tom,
DoEvents doesn't work.
It doesn't matter if the dialog box pops up or not. If all
possible
I'd
rather pass in "yes" so the dialog box doesn't show up at all.

Thanks

"Tom Ogilvy" wrote:

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

Thanks alot for your help.
Yes, it does work, i left off the "~" on sendkeys so it didn't emulate
pressing enter.


"Tom Ogilvy" wrote:

This worked for me.

Sub AAAATest1()
Application.DisplayAlerts = False
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime Now + TimeValue("00:00:01"), "Test2", , True
SendKeys "{DEL}~", False
End Sub
Sub Test2()
Worksheets("Main").Range("B1").Select
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
the message box still appears.


"Tom Ogilvy" wrote:

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

Sub Test2
Worksheets("Main").Range("B1").Select
Application.displayAlerts = True
End Sub

Should suppress the popup and answer Yes.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
Tom,
DoEvents doesn't work.
It doesn't matter if the dialog box pops up or not. If all
possible
I'd
rather pass in "yes" so the dialog box doesn't show up at all.

Thanks

"Tom Ogilvy" wrote:

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

hmmm
the point of the enter is to drop the dialog without user intervention -
otherwise we are back to 4 or 5 posts ago. But whatever.

--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Thanks alot for your help.
Yes, it does work, i left off the "~" on sendkeys so it didn't emulate
pressing enter.


"Tom Ogilvy" wrote:

This worked for me.

Sub AAAATest1()
Application.DisplayAlerts = False
Worksheets("Main").Select
Worksheets("Main").Cells.Select
Application.OnTime Now + TimeValue("00:00:01"), "Test2", , True
SendKeys "{DEL}~", False
End Sub
Sub Test2()
Worksheets("Main").Range("B1").Select
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
the message box still appears.


"Tom Ogilvy" wrote:

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

Sub Test2
Worksheets("Main").Range("B1").Select
Application.displayAlerts = True
End Sub

Should suppress the popup and answer Yes.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
Tom,
DoEvents doesn't work.
It doesn't matter if the dialog box pops up or not. If all
possible
I'd
rather pass in "yes" so the dialog box doesn't show up at all.

Thanks

"Tom Ogilvy" wrote:

Screwed up the Ontime command. Should be

Application.OnTime now + TimeValue("00:00:02"),"Test2",,True

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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