Can't Select Range off sheet from inputbox

[Jim May]

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

 

[Gord Dibben]

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

 

[Steve Yandl]

Jim,

Instead of the InputBox, I'd create a userform and place a RefEdit control
on the form. It's the last item in the toolbox for building user forms.

Steve

 

[Jim May]

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]

That would place it before the Exit Sub in your error trap.

If MyCells = "" you will Exit Sub without re-enabling ScreenUpdating

Some say you don't have to set back to True but..............?

For me your code errors out upon Cancel of the InputBox.

I would also add an On Error Resume Next

above the line With Sheets("Summary")

I guess the wsadd routine is there in case "Summary" did not exist but looks
like you removed any trap for that.


Gord