PDA

View Full Version : Excel VBA Don't show Sampling - Output range will overwrite existing data



vanhunk
08-19-2018, 06:18 AM
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
08-19-2018, 06:40 AM
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
08-19-2018, 09:13 AM
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