Frozen screen

[zelarra]

I'll tell you. I have the Excel that I attached, in which if I try to enter a value in any cell, it freezes and I have to close it.

Dropbox

www.dropbox.com

I thought it could be because I used OFFSET, but I used tables to avoid that problem, but it didn't solve it, so I don't know what could be happening.

I have detected that when changing data the screen freezes, and what you see in this capture happens to me. I am in the cell marked in red with yellow lines, I try to enter a value in that box and a grey box appears below (marked in red). If I accept with enter, it fills down the value that I have entered in that yellow-veined box but without moving from it.

Also, for some time now I have been having a "problem" when working with Excel: sometimes, and without knowing why, the selection of cells that you activate with F8 gets stuck or "on". No matter how many times I try to deactivate it by pressing F8 again, when I move between cells with the arrow keys, it keeps selecting the cells, so the only solution I have left is to close and reopen Excel.

This happens to me in books without macros (xlsx) as well as with macros (xlsm).

Can anyone help me?

Thanks.

 

[Micron]

My guess is that you have code that executes when you make a change or select a different cell. Put a break point on that/those code(s) and perform the action that causes the problem. Then step through with F8 and watch how the code is handled. I suspect that you're stuck in a loop.

 

[zelarra]

Hi, thank you very much for your reply.

I have recorded a video with what you propose, so you can see what happens.

Dropbox

www.dropbox.com

It is important for you to know that the book does not have any kind of code, and that everything that is executed is done from the personal macro book.

Here is the code that is in said personal macro book:

Option Explicit

Private WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()

    Set xlApp = Application

End Sub

Private Sub xlApp_AfterCalculate()

    On Error GoTo errLbl
    
err_exit:

Debug.Print "AfterCalculate: " & Application.ScreenUpdating

Exit Sub

errLbl: Debug.Print "AfterCalculate: " & Err.Number & ". " & Err.Description

End Sub

Private Sub xlApp_SheetCalculate(ByVal Sh As Object)

    On Error GoTo errLbl
    
err_exit:

Debug.Print "SheetCalculate: " & Application.ScreenUpdating

Exit Sub

errLbl: Debug.Print "SheetCalculate: " & Err.Number & ". " & Err.Description

End Sub

Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    On Error GoTo errLbl
    
err_exit:

Debug.Print "SheetChange: " & Application.ScreenUpdating

Exit Sub

errLbl: Debug.Print "SheetChange: " & Err.Number & ". " & Err.Description

End Sub

Private Sub xlApp_WorkbookOpen(ByVal WB As Workbook)

    On Error GoTo errLbl

    Dim NumberSheets As String
    Dim LenNumberSheets As Variant
    Dim SLen As Long
    Dim SheetName As String
    Dim strNameSheet As Object
    Dim Ws As Worksheet

    Application.ScreenUpdating = False

    Application.WindowState = xlMaximized

    Application.OnKey "%{F10}", ""

    Application.OnKey "%{F12}", ""

    Select Case Right(WB.FullName, Len(WB.FullName) - InStrRev(WB.FullName, "."))

        Case "xlsm"

            NumberSheets = CStr(ActiveWorkbook.Worksheets.Count)

            SLen = Len(NumberSheets)

            SheetName = "Hoja" & Right(String(SLen, "0") & "1", SLen)

            WB.VBProject.VBComponents(SheetName).Activate

            Application.Goto Range("A1")

            Range("A1").Select

        Case Else

            Exit Sub

    End Select

    Application.ScreenUpdating = True

err_exit:

Debug.Print "WorkbookOpen: " & Application.ScreenUpdating

Exit Sub

errLbl:

    Select Case Err.Number

        Case 9 'No existe la hoja

            Application.Goto Range("A1"), True

            Range("A1").Select

            Application.ScreenUpdating = True
        
        Case 50289 'Error en hojas protegidas

            Application.Goto Range("A1"), True

            Range("A1").Select

            Application.ScreenUpdating = True
        
        Case Else

            Debug.Print "WorkbookOpen: " & Err.Number & ". " & Err.Description

            Application.ScreenUpdating = True

            Exit Sub

    End Select

End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal WB As Workbook, Cancel As Boolean)

    Application.ScreenUpdating = False

    Application.ActiveWorkbook.Save

    Application.ScreenUpdating = True

End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

    On Error GoTo errLbl

    If IdSheetType = 1 Then Debug.Print "Funciona"

    If Sh.Type = -4167 Then Application.Goto Range("A1"), True

err_exit:

Debug.Print "SheetActivate: " & Application.ScreenUpdating

Exit Sub

errLbl: Debug.Print "SheetActivate: " & Err.Number & ". " & Err.Description

End Sub

 

[Micron]

Can't read the code or message box details but what I find odd is that the message box opens when the code is in break mode. It's as if you had 2 processes going on at the same time. That may be your issue. Do you know why the code does not halt everything when you have a break on it? I'm assuming that message box is not a system generated error message.

 

[zelarra]

@Micron I don't understand what you want to tell me. Could you try with other words? Thanks.

 

[Micron]

If you put a break point at the start of a procedure (sub or function, aka macro by Excel users) when the code halts at the break point nothing should happen after that. If you step through the code by repeatedly pressing F8, each code line should execute, one by one. Then whatever those line do should happen as they are executed. In your vid I saw the code halt at the break point, then a message box appeared. That should not happen. It looks to me like it was not an Excel error message but rather, has something to do with code in a workbook. AFAIK, vba cannot run more than one procedure at a time so that is a mystery. The only other idea I have about this is that some code is in a Personal.xls and some is in the workbook with your data, and there is a conflict. If that's not it, then sorry, I'm out of ideas at the moment.

 

[zelarra]

Hello, thank you very much for answering.

I will tell you the tests I have done: 1. If I convert the book to xlsx (that is, remove all the macros that the book might have), I still get the same error.

2. In both xlsx and xlsm, if I unfreeze the panels, I no longer get the error.

3. In xlsm, I have simplified and debugged the code using error control, and even with that it still fails.

Thank you very much.

 

[Micron]

I still get the same error.

As far as I can tell, you have never stated what the error number or message is but I have mentioned this twice. Perhaps you mean you still get the same result and there is no error message?

if I unfreeze the panels,

No idea what that means.

 

[zelarra]

Sorry it took me so long to respond.

Look, I haven't told you the error number because it doesn't give me one.

I'm attaching a screenshot with what it means to move and freeze panels. I think that, even though it's in Spanish, you'll be able to figure out what it is.

In the video, you see that in the same row, if the panels are frozen, the screen freezes and I have to close and reopen it for it to work. However, if I move the panels, it works perfectly.

Dropbox

www.dropbox.com

Thanks a lot.

 

[Micron]

I watched your vid but everything is too tiny to make any sense out of it. Maybe someone with a bigger monitor will be able to help you as I only have a laptop.
At least we cleared up that there is no error, just a problem. Good luck!