Excel 2013 Workbooks Breaking

[crider816]

I'd consider myself to have advanced visual basic knowledge and capabilities, however a project I've been working on for a month or so is in risk of being thrown away as my workbook is having issues every time I run a procedure that places formulas into cells. I run into a problem where if I then type in a value on the spreadsheet that I'm manipulating with VBA then the spreadsheet becomes unresponsive, as in I can't use Freeze Panes to view the spreadsheet without excel glitching out, or whenever I type a value into the worksheet it just disappears after I hit enter.

I have figured out how to get it to work again. And it's by stepping through a macro like this.

Sub applicationSaver()    Application.EnableEvents = False: Application.ScreenUpdating = False
    Application.EnableEvents = True: Application.ScreenUpdating = True
End Sub

I cant just run this code it only works when I step through it by hitting F8
Any help will be appreciated.

 

[crider816]

Here's an update, as long as I step into code the application works fine after. I just can't be stepping into/out of code repeatedly for this application.

So the question now is, to work around this bug, how do I replicate stepping into/out of visual basic code without forcing the user to do that.

Thanks guys!

 

[mole999]

I'm guessing somewhere in your code you have Application.EnableEvents = False and thats partly why you can only recover when this is run

Sub applicationSaver()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Sub applicationSaver2()
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

have you Option Explicit at the top of the module outside the sub, does the code compile

 

[crider816]

I use option explicit religiously, the code compiles it just pasted incorrectly into the forum post. I went ahead and tried both your examples you posted and again it only works when the code is stepped into. When I type into a cell the entire cell is greyed, out like it's selected or something, and then when I hit enter the numbers I just typed disappear and nothing happens. And I've gone and tested it with a different section of code that looks like this

Sub testIt()

Dim a as string
a = "Test"

End Sub

I guess I don't know what is happening when code is stepped into with VBA, because if I did I would be able to replicate it! (hopefully)

Thanks again for any help!

 

[mole999]

is the workbook shared or protected (as for not working the four instructions will run through and not be seen by the human eye. If you left click in the border next to a line of code you will get a dot, then when you are in the sub press F5 and it should just run to the dot, you can use escape to get out of that

 

[crider816]

It is a shared workbook however there are no protection settings on the workbook at this time. (or have there ever been, but I plan on putting protection on it later)

 

[mole999]

unshare it and try your code again