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
|