1

Question overview:
I am using Excel VBA histogram function from 'Analysis Toolpak' to generate approximately 25 histograms automatically. When Histogram graph is generated, it is placed on top of cells that have values in it, effectively hiding them (Which is OK with me). Therefore a following message is generated "Histogram - some data will be hidden by embedded chart(s)" with "OK" & "Help" buttons. I don't want to press "OK" 25 times whenever I am running this macro.

What I have tried:

  • Application.DisplayAlerts = False/True is not working out for me. I have tried placing this in variaty of location in my code
  • Application.ScreenUpdating = False/True
  • Also tried playing with SetWarning function

Code (1/25): 

Dim binrng As Range  
    Set binrng = Sheets("PSDreport").Range("P4:P64")  
Dim outputrng As Range  
    Set outputrng = Sheets("PSDreport").Range("Q3")  
Application.Run "Histogram", inprng, outputrng, binrng, False, False, True, False

My partial solution: 

With Application  
//CODE GOES HERE//   
.SendKeys "{ENTER}"  
End With

Problem with my current solution:
Note that all Histogram generating segments of code (1/25) are wrapped around the 'With'. For some reason the first Histogram generated still produces the pop-out (Not good). the remaining 24 successfully skip the pop-outs but the noise of the pop-outs is still produced (slight annoyance).

I am looking for a more elegant way to solve this problem

Community
  • 1
  • 1
Adam Pak
  • 11
  • 1
  • 1
    A few seconds formatting this question would make it more attractive to try and answer. Someone has kindly had a go and with another vote that should then appear. – QHarr May 18 '18 at 20:13
  • 1
    Unfortunately, DisplayAlerts only works with built-in Excel dialogs, not those from add-ins. – Jon Peltier May 23 '18 at 14:23

2 Answers2

0

I had this warning too when I tried to output my embedded chart on the same worksheet that I had my source data (your "imprng"). Once I moved my output range (outputrng) to a different worksheet, the warning stopped.

BrupieD
  • 56
  • 5
0

Bit late to the party but anyone else looking at this should try

With Application .SendKeys "{ENTER}"
//CODE GOES HERE//
End With

Andy Teague Camp
  • 1