Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Can't Select Range off sheet from inputbox
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 284
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 477
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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 01:42 AM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement