Gord,
Specifically, should the Application.ScreenUpdating = False
be AFTER my line:
myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address

It now works. Thanks !!

Jim May

"Gord Dibben" wrote:

Jim

Try moving Application.ScreenUpdating = False down a little farther.


myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address
If myCells = "" Then
'user hit cancel
Exit Sub
End If
Application.ScreenUpdating = False


Gord Dibben MS Excel MVP

On Fri, 28 Sep 2007 16:09:00 -0700, Jim May
wrote:

The below code works fine except, when the InputBox comes up, it is MODAL.. I
am unable to click outside the textbox (inputbox) and Highlight the desired
Cells (range). Earlier, I was able to, but not now. How can I fix this?

TIA,

Jim

Sub SumRange()
Dim myCells As String
Dim ws As Integer
Dim answer As Double
Dim myRange As Range
Dim lrow As Long
Application.ScreenUpdating = False
'On Error GoTo wsAdd
With Sheets("Summary")
.Select
.Cells.ClearContents
End With
Sheets("Sheet1").Activate

***PROBLEM WITH NEXT LINE ***

myCells = Application.InputBox("Enter Range to Sum", Type:=8).Address
If myCells = "" Then
'user hit cancel
Exit Sub
End If
Sheets("Summary").Activate
For ws = 1 To Sheets.Count - 1
Cells(ws, 1) = Sheets(ws).Name
Set myRange = Sheets(ws).Range(myCells)
answer = Application.WorksheetFunction.Sum(myRange)
Cells(ws, 2).Value = answer
Next
lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Range("B" & lrow).Formula = "=sum(B1:B" & (lrow - 1) & ")"

GoTo finish
wsAdd:
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Summary"
On Error GoTo 0
Resume Next
finish:
Application.ScreenUpdating = True
End Sub