Autoanswer on popups. Excel VBA

[ArneM]

Hi

I hope you can help me out with this problem:

I'm running af script with the purpose of creating a Histogram. I'm using this code that (actually!) works:

Application.Run "ATPVBAEN.XLAM!Histogram", ActiveSheet.Range("$A$2:$A$5000") _
, ActiveSheet.Range("$E$1:$F$12"), ActiveSheet.Range("$C$2:$C$11"), False, _
False, False, False

When running this script, I get this popup:

'Histogram - Output range will overwrite existing data. Press OK to overwrite.'

To this question I can answer: 'OK' 'Cancel' 'Help'

The correct answer is: 'OK'.

I have tried serveral scripts to get rid of the prompt before and after the histogram-kode, e.g.:

Application.DisplayAlerts = False

Application.SendKeys "{~}", True / False / "{ }"

Below, I have listed other scripts to see if they works. They don't...

I have read a lot of Q&As in other forums regarding this subject - and many of them are using: Application.DisplayAlerts = False.

But no mather what, I still get the popup that needs to be answered with 'OK'.

Actually, I'm not quite sure if the popup is...:
* A Warning
* An Alert
* An Error
* An Event
* A Message Box with vbOK

... and needed to be handled in another way than I have tried as described above.

Do you have a workaround so I don't have to manually answer the same question over and over and...?

In advance thanks a lot for your help.

Kind regards,

Arne

-------------

I have also tried these codes:

'ExcelApp.DisplayAlerts = false
'Excel.Application.Application.DisplayAlerts = False
'DoCmd.SetWarnings False
'appXL.Application.DisplayAlerts = False
'Application.SendKeys "{~}", False
'Application.SendKeys "{~}", True
'Application.SendKeys "{ }", False
'Application.SendKeys "{ }", True
'On Error Resume Next
'Application.AlertBeforeOverwriting = False

'With Application
' .ScreenUpdating = False
' .DisplayAlerts = False
' .EnableEvents = False
' End With

'With Application
' .SendKeys "{ENTER}"
' End With

'Application.DisplayAlerts = False /// On Error Resume Next /// On Error GoTo 0 /// Application.DisplayAlerts = True

'On Error Resume Next /// 'Your code here! /// 'Reset Run Time Errors /// On Error GoTo 0

'If MsgBoxContain("Histogram", vbOKOnly, "OK") = vbOK Then /// Range("B2").ClearContents /// End If
' Application.EnableEvents = False

 

[Roxxien]

Hello ArneM and welcome on the forum.

Can I ask you to reply with using CODE tag around your code, it will really help our eyes.

[ Code] Put this without space [/ CODE]

EDIT : with some time I could read it ... In the end, the message box is saying that their is already some data in the "paste" zone of your histogram

 

[shg]

Your code could clear those ranges first.

Or, you could dump using the ATP, which are the worst documented functions in history, and just use a FREQUENCY formula.

 

[ArneM]

Hi Roxxien

Thanks a lot for your greeting. And also thanks for your quick review of my code. I'll remember to code tags next time.

You spotted the pasted content that's already is in or my code - do to the fact that I'm reusing the Excel-file from time to time. And therefore I have solved my problem by deleting the content in the output range, and then run the code for the histogram. That works!

Have a nice day!

Kind regards

ArneM

 

[ArneM]

Hi shg

Thanks for your quick review of my code.

You spotted the pasted content that's already is in or my code - do to the fact that I'm reusing the Excel-file from time to time. And therefore I have solved my problem by deleting the content in the output range, and then run the code for the histogram. That works!

Have a nice day!

Kind regards

ArneM