LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 477
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
When entering data into a range of cells, select the entire range. Q Excel Discussion (Misc queries) 0 September 26th 07 04:36 AM
Select data to appear on 2nd sheet by date range... Cbreze Excel Discussion (Misc queries) 0 June 28th 07 12:10 AM
sheet protection - only selected range to be able to select/input data Corey Excel Worksheet Functions 7 February 13th 07 05:41 PM
select a range, copy it to a new sheet Dave F Excel Discussion (Misc queries) 1 September 22nd 06 08:06 PM
InputBox to select cell locations Michael M Excel Worksheet Functions 5 February 21st 06 05:18 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"