Error 424 with inputbox method after OK

[Herman]

Hello,
What is wrong with this very simple code ?

Sub GetAdres()
Dim Last As Range, mes As String
mes = "Select any cell in one of the first 10 rows"
Set Last = Application.InputBox(Prompt:=mes, Type:=8)
MsgBox Last.Address
End Sub

The sub stops on the Set Last.. line after selecting a valid range
and clicking the OK button. Error message = "Error 424 - Object
Required".
I know about error trapping but I don't see any way to trap the error
and still get the required range.
Funny thing is that I used similar code in other subs where it works
fine.
Thanks for any help.
Herman

 

[Harald Staff]

Nothing. Works fine here -except for the lack of "Cancel" click trapping
(which returns "Object required" btw). Trap that one like this

Sub GetAdres()
Dim Last As Range, mes As String
mes = "Select any cell in one of the first 10 rows"
On Error Resume Next
Set Last = Application.InputBox(Prompt:=mes, Type:=8)
If Last Is Nothing Then Exit Sub
MsgBox Last.Address
End Sub

HTH. Best wishes Harald

 

[polack]

Application.InputBox Error 424 if scrolling on spreadsheet with conditional formats

In another section of this forum... I've posted my experience with this Error 424 dilema. My experience is that code that is otherwise perfectly straightforward and valid... generates error 424's if the user scrolls, on a spreadsheet with conditional formatting, while the inputbox dialog is up--that is, if the user uses the mouse wheel, uses the arrow keys, or uses the scroll bar buttons.

The insidious thing is that Excel calculates Conditional Formats in the background while scrolling--this calculation screws everything up . If you had no conditional format cells, then no calculation would happen and scrolling would not screw up the inputbox. You can do an Application.ScreenUpdating = False (then later do an Application.ScreenUpdating = True to turn it back on) to stop the calculation . This has the benefit of speeding up some macros because it doesn't constantly update the screen while large do-loops change data... but turning screenupdating off is not desired in this case because the user needs to be able to see and scroll to a new position in the spreadsheet .

The user may select a cell or range without scrolling and this input gets properly assigned with Set x=application.inputbox(blah blah, Type:=8) where x is Dim x As Object or Dim x As Range, and Type:=8 is required to select a range. As soon as you rerun the inputbox procedure WITH scrolling, the input is no longer accepted and causes an error 424 that cannot be differentiated from a cancel button selection ^2. (BTW, the use of "Set" is important here)

This subtle problem sucks and I've researched window focus, other variable types for 'x', other Type:='s, and other ideas to no avail. Short of disabling the simple and useful features of scrolling on a spreadsheet with conditional formatting... this code will always fail if the user scrolls while the inputbox is displayed.

Is there aybody out there who has ever been able to work around or otherwise fix this? We want to be able to have the user see the inputbox, SCROLL to the desired location on a spreadsheet with conditional formatting, choose a cell or cells, hit OK and have the information properly transfer to a variable object without an error 424.

Good luck!