PDA

View Full Version : Input Box Run time Error 1424



Hamond
10-04-2010, 06:20 AM
Hi,

I'm am sometimes getting an Run time error 1424 (30% of the time) with the code below. The user simply select a cell in a column and the message box displays the column number.

Debugging takes me to the line:

Set datarng = Application.InputBox("Select data column Source", "data selection", Type:=8)

Anyone know why could sometimes work but not other times?

Thanks,

Hamond


Sub xxxxx()
Dim datarng As Range
Dim col As Integer
Set datarng = Application.InputBox("Select data column Source", "data selection", Type:=8)
col = datarng.Column
MsgBox col
End Sub

Kenneth Hobs
10-04-2010, 07:04 AM
Are you sure that the error was not 424? You need to handle the cancel condition where no range was selected or typed.
Sub Test()
Dim myRange As Range
On Error Resume Next
Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
On Error GoTo 0
If myRange Is Nothing Then
MsgBox "No range was selected or typed."
Else: MsgBox "The address of the range selected was " & myRange.Address & "."
End If
End Sub

Hamond
10-04-2010, 07:16 AM
Hello Keneath

Sorry yes the error was 424, Object Required. I tried running your code and a couple of times the message box returns "No range was selected or typed" even though a specific cell was selected in the input box.

It appears the error tends to mostly occur when I scroll several columns away from the active cell/column at the point when the macro was run.

Hamond

Hamond
10-05-2010, 05:10 AM
All,

Further to my previous post, anyone got any ideas what could be causing my problem and how I could overcome?

Thanks,

Hamond

Kenneth Hobs
10-05-2010, 07:12 AM
And this does it on a new xlsm?

If not, then you have something else going on. You would have to post an xlsm that causes the problem to troubleshoot.

Hamond
10-06-2010, 04:23 AM
Hi Kenneth,

I tried copying the data from the orginal workbook into a new workbook (attached) and running the code but am still getting the same error.

Just to recap, when I select a nearby column, it seems to work but when I select a distant column the macro generates the error.

In the workbook I have included two macros. Test is the original code posted that returns the msgbox and another macro called extract series which is actually the end result of what I am trying to do. Namely this is to get a user to select a column and copy that column along with the other columns specfied in the code into a new workbook.

If there is a better way to do this and hence bypass this current problem then I am open to ideas!

Thanks,

Hamond