Code runs perfect until adding excel restart

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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:
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.

Code:
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:

Code:
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,099
Office Version
2019, 2016, 2013
Platform
Windows
is closer getting enough time to work, maybe try a minute and then look to reduce that
 

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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

Moderator
Joined
Oct 23, 2004
Messages
10,099
Office Version
2019, 2016, 2013
Platform
Windows
so you did this >
Code:
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

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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
Code:
Activeworkbook.Save
:)
 

Forum statistics

Threads
1,089,202
Messages
5,406,814
Members
403,107
Latest member
kyriakos kyriakou

This Week's Hot Topics

Top