Inputbox stopped accepting mouse selection in for each loop in excel vba because screenupdating changed in another sub--why?.

1
votes

Why would an input box stop accepting a selection by mouse after a call to a sub with screenupdating variable changes?

I have a large workbook in excel that calculates a budget from different components on different sheets. I'm using named ranges in all of my formulas, and as I build the workbook I often need to move things around on the sheet, and thus edit the references to my named ranges so I made a macro to run through my named ranges and let me click to update their references.

I've included three subs from my workbook code; sheet 1 just has some values in the named range cells, a formula ( = CNGFixedCost1 + CNGFixedCost2 + CNGFixedCost3), and an activex check box. When I run RangeNameManager() the inputbox stops accepting mouse selections, due to the screenupdating variable in the Worksheet_Calculate() sub, . I figured out how to resolve the problem while writing this up (remove the screenupdating changes), but I'm still curious as to why this happens. 

Option Explicit

'Name Ranges in workbook
Public Sub Workbook_Open()

Worksheets("Sheet1").Range("D3").Name = "CNGFixedCost1"
Worksheets("Sheet1").Range("D4").Name = "CNGFixedCost2"
Worksheets("Sheet1").Range("D5").Name = "CNGFixedCost3"


End Sub

'Update named ranges
Sub RangeNameManager()

    Dim nm As Name
    Dim nms As String
    Dim xTitleID As String
    Dim InputRng As Range
    Dim asnms As String

    On Error Resume Next

    asnms = CStr(ActiveSheet.Name)

    For Each nm In ActiveWorkbook.Names

    nms = CStr(nm.Name)



    If nm.RefersTo Like "*" & asnms & "*" Then

        Set InputRng = ActiveSheet.Range("A1")
        Set InputRng = Application.InputBox("The current range for" & nms & " is " & CStr(nm.RefersTo) & ". Select the new range.", InputRng.Address, Type:=8)

        nm.RefersTo = InputRng
    End If

    Next
    On Error GoTo 0

End Sub

' Update check box automatically 
Private Sub Worksheet_Calculate()

    Application.ScreenUpdating = False    '***Removed to resolve problem.***

    Dim errwksht As String

    errwksht = ActiveSheet.Name

    On Error GoTo ErrorHandler

        If Worksheets("Sheet1").Range("CNGFixedCost1").Value > 0 Then     
        Worksheets("Sheet1").CheckBox1.Value = False
    Else
        Worksheets("Sheet1").CheckBox1.Value = True
        End If

ErrorHandler:
        Exit Sub



    Application.ScreenUpdating = True     '***Removed to resolve problem.***
End Sub
2
excelvba

2 Answers

2
votes

ScreenUpdating is a property of the Application object. If you turn it to false, then the application cuts off connection with the user (it won't take input, and it won't update the display).

It's very useful if you want to make something run faster, however it shouldn't be used during times when you need user interaction.

0
votes

You're exiting the sub before turning screen updating back on, leaving the application in an unstable state. 

' Update check box automatically 
Private Sub Worksheet_Calculate()

    Application.ScreenUpdating = False    '***Removed to resolve problem.***

    '...

ErrorHandler:
        Exit Sub 'exits here

    Application.ScreenUpdating = True  ' so this NEVER executes
End Sub

This is easily fixed by resuming at your error handler, which would be better named CleanExit:. Here's how I would write it. 

Private Sub Worksheet_Calculate()
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False    '***Removed to resolve problem.***

    '...

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    ' Actually do some error handling
    Resume CleanExit

End Sub