VBA, InputBox, Run-time error '424': Object required

[NathanW]

Hey guys,

I'm having an issue with my InputBox command, I have underlined the part that's returning the error.

....
SelectHeading:
On Error Resume Next
'Need to let the user select the proper heading.
Dim rng As Range
Set rng = Nothing
Set rng = Application.InputBox(prompt:="Select the heading you wish to use.", Default:=Cells(4, 2).Value, Type:=8)
If rng = False Then

Resume HeadingNotFound

Else

sHeading = rng.Value

Resume ErrorHandling

End If
.....
If the user hits cancel during the input box part, it returns the Run-time error '424': Object required. No error handling commands have let me get past it. Please please help!!

 

[mikerickson]

The problem with using InputBox like this is that the keyword Set isn't used.
Therefore Excel is using the default .Value property and rng will never be a range object.

Dim rng As Variant
rng = Application.InputBox("select a range", Type:=8)

MsgBox TypeName(rng)

I'm surprised that the code in post #7 isn't erring when it gets to the line
sHeading = rng.Value

 

[profdean]

I am getting Error 424 when I run this code. It is getting hung up on the Set statements. Here is the initial part of the VBA code:

Sub Scheme()
Dim I As Characters, CDL As Characters, SP As Characters, Flag As Characters, Flag2 As Characters
Dim AZ As Characters, FS As Integer, FA As Integer, SA As Integer, AR As Integer, SR As Integer
Dim AC As Integer, AT As Integer, AE As Integer, SC As Integer, ST As Integer
Dim Z As Integer, A As Integer, B As Integer, NN As Integer, NFS As Integer, L As Variant
Dim NFA As Integer, NAE As Integer, NSA As Integer, NAR As Integer, NAC As Integer
Dim NAT As Integer, TN As Integer, TFS As Integer, TFA As Integer, TAE As Integer
Dim TSA As Integer, TAR As Integer, TAC As Integer, TAT As Integer, RN As Integer
Dim RFA As Integer, RAE As Integer, RAR As Integer, RAC As Integer, RAT As Integer
Dim FRR As Integer, FTR As Integer, FNR As Integer, SNR As Integer, STR As Integer
Dim SRR As Integer, S1 As Integer, S2 As Integer, S3 As Integer, S4 As Integer, S As Integer

Worksheets("OC Curve Calcs").Range("B5").Select
Set AZ = Worksheets("OC Curve Calcs").Range("B5").Value ' Scheme evaluation? (Yes, No)
Set I = Worksheets("OC Curve Calcs").Range("B10").Value ' Inspection Level (S1, S2, S3, S4, I, II, III)
Set L = Worksheets("OC Curve Calcs").Range("B33").Value ' Lot Size (integer value)
Set Flag = Worksheets("OC Curve Calcs").Range("I1").Value ' Flag indicating that 100% inspection is necessary
Set Flag2 = Worksheets("OC Curve Calcs").Range("I2").Value ' Flag indicating that single sampling is necessary
Set AQL = Worksheets("OC Curve Calcs").Range("B30").Value ' AQL Value (.015, .025, ..., 65, 100)
Set SP = Worksheets("OC Curve Calcs").Range("B7").Value ' Single or Double Sampling Plan? (Single, Double)

I don't see any misspellings and I have tried to follow the books I have on VBA. Something is not right and I need other more experienced eyes to look at this. Any help will be greatly appreciated.

Dean