Can't Select Range off sheet from inputbox
 

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

Can't Select Range off sheet from inputbox
 

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

Can't Select Range off sheet from inputbox
 

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" wrote in message
...
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

Can't Select Range off sheet from inputbox
 

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

Can't Select Range off sheet from inputbox
 

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

On Sat, 29 Sep 2007 09:18:00 -0700, Jim May
wrote:

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