Here's my problem with the Error 424 dilema when using Excel's Set X=Application.Inputbox(blah blah Type:=8).
My experience is that code that is otherwise perfectly straightforward, valid, and works on simple spreadsheets... 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 calculating screws everything up
. If you had no conditional formatted 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 before selecting the cell as input to the inputbox .
When the user selects a cell or range without scrolling, 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 :x . (BTW, the use of "Set" is important here when using Type:=8 :wink: )
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 1) have the user see the inputbox, 2) SCROLL in whatever way to the desired location on a spreadsheet with conditional formatting, 3) then choose a cell or cells, 4) hit OK and have the information properly transfer to a variable object without an error 424.
Good luck!
My experience is that code that is otherwise perfectly straightforward, valid, and works on simple spreadsheets... 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 calculating screws everything up
. If you had no conditional formatted 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 before selecting the cell as input to the inputbox .When the user selects a cell or range without scrolling, 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 :x . (BTW, the use of "Set" is important here when using Type:=8 :wink: )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 1) have the user see the inputbox, 2) SCROLL in whatever way to the desired location on a spreadsheet with conditional formatting, 3) then choose a cell or cells, 4) hit OK and have the information properly transfer to a variable object without an error 424.
Good luck!
