0

I have a button that is attempting to run a Regression Analysis using values from a hidden protected sheet, and spitting out the information on another hidden protected sheet. The issue I am having is that when I click the button I get a message with the text:

Because you selected only one cell and the surrounding cells are blank, Microsoft Excel cannot determine which cells to apply the autoformat to. Select the range of cells you want to format, and click AutoFormat again.

I receive three of these messages back to back and after clicking OK through them, the analysis successfully finishes and the code works as intended. So what exactly is this issue and why am I seeing it?

Sub Macro1()
' Macro1 Macro
    Dim n As Range
    Set n = Worksheets("Data Input & Summary").Range("D67")
    Application.ScreenUpdating = False
    ThisWorkbook.Unprotect Password:="PASSWORD"
    Worksheets("analysis 1").Unprotect Password:="PASSWORD"
    Worksheets("Regression").Unprotect Password:="PASSWORD"
    Worksheets("analysis 1").Visible = True
    Worksheets("analysis 1").Activate
     Application.Run "ATPVBAEN.XLAM!Regress", Worksheets("analysis 1").Range(Range("F6"), Range("F6").End(xlDown).Offset(-n)), _
        Worksheets("analysis 1").Range(Range("G6"), Range("G6").End(xlDown).Offset(-n)), False, False, 90, Worksheets("Regression").Range("$A$1") _
        , False, False, False, False, , False
    Range("K1").Select
    Worksheets("Data Input & Summary").Activate
    Worksheets("analysis 1").Protect Password:="PASSWORD"
    Worksheets("Regression").Protect Password:="PASSWORD"
    Worksheets("analysis 1").Visible = False
    Worksheets("Regression").Visible = False
    ThisWorkbook.Protect Password:="PASSWORD", Structure:=True, Windows:=False
    Application.ScreenUpdating = True
End Sub

I am also open to any known suggestions for eliminating the need for messing with all the workbook protection, I wasn't able to run an analysis from the main sheet with the information on another protected sheet so I figured this was the only workaround.

Community
  • 1
  • 1
Kevin P.
  • 907
  • 7
  • 18
  • 2
    Doubt the problem is here, it is in the `ATPVBAEN.XLAM!Regress` code. You will need to show that code also. – Scott Craner Oct 12 '17 at 13:07
  • 1
    Well, if it works as intended except for those messages, would a `Application.DisplayAlerts = False` at the start hide them? Not necessarily a great solution, but if you're on the clock, might work as a temporary solution until you can do a proper fix. Like @ScottCraner said, the root of the issue is almost certainly in the `Regress` code, not here. – barvobot Oct 12 '17 at 13:13
  • @ScottCraner with that in mind is there even a viable option to fix it? As far as I know the `ATPVBAEN.XLAM!Regress` is a built in code correct? – Kevin P. Oct 12 '17 at 13:48
  • @J. Fox This did seem to be a plausible solution although it still leaves the question. I suppose this works though, thank you. – Kevin P. Oct 12 '17 at 13:49
  • Not that I know, it is a sub in the `ATPVBAEN.XLAM` If you go into the vbe is should show on the left. – Scott Craner Oct 12 '17 at 13:50
  • see: https://stackoverflow.com/questions/30310431/regress-function-for-excel-vba for how to get to the code and verify that you are providing the correct variables. – Scott Craner Oct 12 '17 at 14:17

0 Answers0