Excel VBA Don't show Sampling - Output range will overwrite existing data

[vanhunk]

Output range will overwrite existing data. Press OK to overwrite data in range :...

I am using the Sampling Analysis tool in Excel to select random numbers and wrote a macro to automate the updating of the selection. Unfortunately I have to press OK every-time to accept the overwriting of the previous selection.

I am trying to stop this message and to get the overwriting to happen without my intervention.

None of the methods or suggestions I could find solved this particular problem.

That includes:

Application.AlertBeforeOverwriting = False

or

Application.DisplayAlerts = False

or

Application.SendKeys "{ENTER}"

My code:

Sub RandSelectDA()

    With Application
        .Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range("EntryNo"), _
            ActiveSheet.Range("WinnerNo"), "R", 5
    End With

End Sub

See attached spreadsheet.

I would really appreciate a solution, if it exists, for this one.

Best Regards,
vanhunk

[Paul_Hossler]

1. Not tested since I don't have the XLAM. I know you tried DisplayALerts, but this is what could work

    With Application

        .DisplayAlerts = False

        .Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range("EntryNo"), _
            ActiveSheet.Range("WinnerNo"), "R", 5

        .DisplayAlerts = True

    End With

2. Try to .Clear the destination range first and then run the XLAM

Good luck

[vanhunk]

Thank you Paul:
Re 1.: The Application.DisplayAlerts = False does not work in this instance. The XLAM is part of Excel's Data Analysis tool.

Re 2.: I felt embarrassed that I didn't explore this option - and yes, it did work. From a technical point of view, as well as for a learning experience, it would still be great to know if there is actually a way to have the OK option automatically selected.

Regards
vanhunk