Code runs perfect until adding excel restart

[andrewb90]

Hello all,

I have a sub that works nearly perfectly, except that excel seems to lag after running. My solution to the lag was always close and reopen excel. I finally added that to the sub (using call at the very end) yet now the original sub does not run in its entirety. I'm at a loss as to why.

Here's the code:

Sub popAvail()Application.ScreenUpdating = False
ActiveWorkbook.Save
Application.Run "module5.destructure"
Sheets("Scheduler").Visible = True
MsgBox "before cell2comment"
Application.Run "Module10.CellToComment"
Application.OnKey "{F2}", "module5.pophelp"
Call popcont
Application.Run "module5.destructure"
Sheets("Home").Visible = True
Sheets("Home").Select
Sheets("Home").Range("AK1").Value = "1" ' this value is in an if statement at open and close code.
MsgBox "number should be changed"
Application.DisplayAlerts = False
'Call closer




End Sub

I have the call closer commented out and my code runs fine. when not commented out it doesn't.

Sub closer()Application.OnTime Now + TimeValue("00:00:1"), "OpenMe"
ThisWorkbook.Close False 'new
End Sub
Sub OpenMe()
    Application.Run "module5.destructure"
    Application.Run "Module1.HideAllExceptScheduler"
    Application.Run "module5.structure"
    Application.DisplayAlerts = True
End Sub

The code that doesn't complete is:

Private Sub CellToComment()'Updateby20140509
'Note: Will not add comment if blank, will clear comment if notes deleted
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Sheets("Scheduler").Select
ActiveSheet.Unprotect "majinbuu"
'Set NoteRng = Range("S89:S100").SpecialCells(xlCellTypeVisible)
Set CmtRng = Sheets("Scheduler").Range("D89:D207").SpecialCells(xlCellTypeVisible)
'Set WorkRng = Application.Selection
'Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In CmtRng 'this is where it's gonna print
    'Rng.NoteText Text:=Rng.Value
    'Rng.NoteText Text:=Sheets("Master Availability").Range("S" & Rng.Row)
    If Sheets("Master Availability").Range("S" & Rng.Row) = "" Then
        Sheets("Scheduler").Range("D" & Rng.Row).ClearComments
    Else
        Rng.NoteText Text:=Sheets("Master Availability").Range("D" & Rng.Row) & ":" & vbNewLine & Sheets("Master Availability").Range("S" & Rng.Row)
    End If
    '.Offset(rowOffset:=0, columnOffset:=-1)
Next
Sheets("Scheduler").Select
MsgBox "done"
    ActiveSheet.Protect "password", _
    DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, UserInterfaceOnly:=True, _
        AllowFormattingRows:=True
End Sub

Any thoughts on what I can do would be greatly appreciated.

 

[mole999]

is closer getting enough time to work, maybe try a minute and then look to reduce that

 

[andrewb90]

But closer doesn't happen until after the cellTocomment code runs. That's the code that's not doing everything I need it to do.

 

[mole999]

so you did this >

Sub closer()
DoEvents
'Application.OnTime Now + TimeValue("00:00:1"), "OpenMe"
OpenMe
ThisWorkbook.Close False 'new
End Sub
Sub OpenMe()
    Application.Run "module5.destructure"
    Application.Run "Module1.HideAllExceptScheduler"
    Application.Run "module5.structure"
    Application.DisplayAlerts = True
End Sub

 

[andrewb90]

Excel didn't reopen when I tried that.

 

[andrewb90]

So to all those who may read this:
For all the modules of code you have, and all the ideas you come up with, sometimes the solution to everything is

Activeworkbook.Save