Need to get column number from InputBox selection

[NathanW]

Hey guys,

I'm having some trouble with an input box command. I'm creating a program that transfers data from one worksheet to another (within the same workbook) and the data is sorted by column under their respective column headings. For example, the first worksheet may contain a column labeled 'equipment name' with a list of equipment names below it. This program will store the whole column in an array and search for the heading 'equipment name' in the second worksheet, then it will transfer the equipment names to the second worksheet under the heading 'equipment name', which already exists in the new worksheet. However, if it can't find the column heading in the new worksheet, then it should ask the user if they want to insert a new column and use this new column to place the data in (with the appropriate heading). I would like to allow the user to select a cell on the screen at this point, and the column should then be inserted to the left of the cell they choose. Here's my issue, if the user chooses 'cancel' on the inputbox, the "Run-time error '424': Object required" error pops up. Here is my code with the part that gets the error, underlined.

Msg = "Please select where you would like to place this heading. "
Msg = Msg & "The column will be placed between the cell you select "
Msg = Msg & "and the cell to its left."

Dim rng2 As Range

Set rng2 = Application.InputBox(prompt:=Msg, Type:=8)

iColNumber = rng2.Column
ActiveSheet.Cells(4, iColNumber).Activate
ActiveCell.EntireColumn.Insert (xlRight)
Cells(4, iColNumber) = sHeading

'sHeading is the name of the column heading

Resume ErrorHandling

I was having the same issue with another input box in my code just the other day and was able to solve it by dimensioning the variable (rng) as a variant, setting it equal to Nothing, then using

rng = Application.InputBox(prompt:="prompt", Type:=8)
If rng = False Then
'Do something
Else
'Do something else
End If

This would be able to solve the issue I am having now, but if I have it dimensioned as a variant, I won't be able to use iColNumber = rng2.Column to get the proper column number. So, the way I see it is I have two options:
1. Figure out how to handle the error I am having now so I don't have to change the way I get the column number, or
2. Redimension rng2 as a variant and figure out a new way to get the column number of the cell the user selects.

There may even be a third option that I dont know about, but I am up for any suggestions!! I hope this wasn't too much of a headache to read and I would appreciate any help I can get!

 

[daverunt]

OK. Try these changes with rng2 dimmed as a range

Dim rng2 As Range

Set rng2 = Nothing
On Error Resume Next
Set rng2 = Application.InputBox(prompt:=Msg, Type:=8)
On Error GoTo 0
 

If rng2 = False Then Resume HeadingNotFound
 
iColNumber = rng2.Column

 

[daverunt]

Ignore that same problem with Cancel.

 

[NathanW]

Isn't that strange? I don't understand why it's getting hung up even after the 'On Error Resume Next' statement...

 

[daverunt]

Hi,

Apparently you can't have that error handling procedure inside another running Error handler. You can only have one handler running at any one time. Hence the reason it is being ignored.
Error Handling In VBA

 

[NathanW]

Hm, interesting. I suppose that would explain it then. But then shouldn't I be able to use 'On Error GoTo 0' just before using the 'On Error Resume Next'? Or does the 'On Error GoTo 0' classify as an error handling procedure within an error handling procedure? My brain hurts...

 

[daverunt]

I thought you could use On Error GoTo 0 to reset error handling but it doesn't appear to work in this case.
The On Error Resume Next does work if you try a sub with only the code I put in my first post so it does point to the error handling as the cause. I think you need somebody who better understands error handling to look at it and respond.

 

[NathanW]

So what I've decided to do at this point is take the value of the selection (with rng2 dimensioned as a variant) and use that value in a Cells.Find.Activate command, then using iColNumber = ActiveCell.Column to get the column number. It's not perfect, but it will work well enough for my purposes I believe. If anyone can figure out a better way of obtaining the column number (i.e. some way that doesnt involve searching for a word) I would love to hear it!

Thanks again for the help and support!