Application.InputBox Error 424 with conditional format used

polack

New Member
Joined
Dec 7, 2005
Messages
2
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... :confused: 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 :devilish: .

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! :cool:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,999
Messages
5,834,809
Members
430,323
Latest member
Regash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top