Input Box error when scrolling

[jurgenC!]

hi all,

rather frustrated as i am unable to determine where i am going wrong on
following code:

Sub insertRow()
Dim rRange As Range
Application.ScreenUpdating = True
output = 666
Prompt = "Select Destination:"
Title = "Insert Row"

'Display the Input Box
On Error Resume Next
Set rRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8)
On Error GoTo 0

If rRange Is Nothing Then
MsgBox "Cancelled"
Else
rRange.Range("A1") = output
End If
End Sub

the above code will only execute correctly if the destination cell is
visible on the screen.

however, if the destination cell is not visible on the screen and i
therefore either scroll or enter the destination by key - the
"Cancelled" message box is displayed (instead of entering "666" into the
destination cell) after clicking 'OK'.

in despair i added the 'ScreenUpdating' command hoping this would
rectify the error, but to no avail.

any help is mostly appreciated as to how i would be able to scroll
through a worksheet while the Input Box awaits an entry and after
selecting the destination cell, this address to be stored.

cheers....


...jurgenC

 

[Tom Ogilvy]

Code works fine for me in the situations you describe. Xl2000, Windows 2000

 

[jurgenC!]

hi Tom,

thanks for your time to investigate and respons.
at least code wise it is ok (and i am not going bananas)...


...i am running excel 2003 on Win XP pro.

anyone out there with the same set up having difficulties?

cheers...


...jurgenC!

 

[jurgenC!]

hi all,

follow up info (and rather embarrassing as i should have tested before)
- the code does work under above set up on a new sheet (even on new
sheet within workbook), but does not work if executed from specific
sheet i am writing the code for.

not too sure what the cause could be, so far i checked if it had
anything to do with:

Freeze/ Unfreeze Pane of the specific worksheet
Conditional Formatting of the specific worksheet

but no answer as of yet

cheers...


...jurgenC!