application.inputbox returning with unexpected error

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
144
I have been fighting this one for a couple of days.

Can anyone tell me why this inputbox would return an error if the JDE_WO is not the same as the PRO_WO? Both are text. The JDE_WO is extracted from another workbook, the PRO_WO is from the active workbook. I save the value of the range between selections. From the cell that is selected the routine uses the offset function to find the value of PRO_WO.

Code:
       Do While JDE_WO <> PRO_WO And Year(Datefrom) <> Year(Dateto) And _
                      Month(Datefrom) <> Month(Dateto) And isJDE <> "JDE" And Answer = "No"
            Set copyto = Application.InputBox(Prompt:= _
                                "WO#:   " & JDE_WO & "          PO#:   " & POfrom & vbCr & vbCr & _
                                "CONTRACTOR / HVC:  " & Contfrom & vbCr & vbCr & _
                                "VALUE:     " & "$" & Str(costfrom), _
                                Title:="Select AMOUNT Destination Cell:", _
                                Default:=toaddress, Left:=650, Top:=300, Type:=8)
            With copyto
                costto = costfrom
                toaddress = .Address
                rowto = .Row
                colto = .Column
            End With
            
            Range(toaddress).Select
            
            'Check that the WO numbers compare
            PRO_WO = Range("E" & rowto).Offset(1, 0).Value
            If PRO_WO <> JDE_WO Then
                MsgBox "Work Order Number Mismatch" & vbCr & vbCr & "Select Cell with WO: " & JDE_WO
            Else
                'Check that a cell in the JDE column is selected
                isJDE = Range(toaddress).Offset(4 - rowto, 0).Value
                If isJDE <> "JDE" Then
                    MsgBox "Cell is not in the 'JDE' column:" & vbCr & vbCr & "Please try again."
                Else
                    'Check that the right month and year column is selected
                    Dateto = Range(toaddress).Offset(3 - rowto, 0).Value
                    If Year(Datefrom) <> Year(Dateto) Or Month(Datefrom) <> Month(Dateto) Then
                        MsgBox "You are trying to update the wrong month." & vbCr & vbCr & "Please try the correct month."
                    Else
                        'Check that the value is being entered against the right PO
                        POto = Range("F" & rowto).Value
                        If POfrom <> POto Then
                            Response = MsgBox("PO Number MisMatch" & vbCr & vbCr & _
                                                            "Is the PO# Correct? - YES or NO", vbYesNo)
                            If Response = vbYes Then
                                Answer = "Yes"
                            Else
                                Answer = "No"
                            End If
                        Else
                            Answer = "Yes"
                        End If
                    End If
                End If
            End If
        Loop
Regards,
Softedge
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,022
If the user presses Cancel, Application.InputBox will return False, which errors because of conflict with the Set keyword.
 
Last edited:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Softedge

Exactly which line returns the error? What exactly is the error received? (number and description please). You don't appear to have any error handling in the code so I assume you would get an error if the user pressed cancel on the inputbox dialog rather than selecting a range.
 

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
144
The code is causing an error when the input box closes. It does not process any code. The error is 91. I am pressing the OK button only, not the cancel button.

How would I enter some error trapping code into this?

Code:
Set copyto = Application.InputBox(Prompt:= _
"WO#:   " & JDE_WO & " PO#: " & POfrom & vbCr & vbCr & _
"CONTRACTOR / HVC: " & Contfrom & vbCr & vbCr & _
"VALUE: " & "$" & Str(costfrom), _
Title:="Select AMOUNT Destination Cell:", _
Default:=toaddress, Left:=650, Top:=300, Type:=8)
Regards,
Softedge
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

What are you entering into the Inputbox and what do you have variable "copyto" dimensioned as?
 

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
144
I see from elsewhere that error 91 is:


91 Object variable not set (versions 5.0 and 7.0)
91 Object variable or With block variable not set
(version 97)

When I accept the inputbox there is a valid cell address in the input area of the inputbox. It should not be causing an error, but is.

Regards,
Softedge
 

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
144
I am entering a cell address by picking on a cell. copyto is dimensioned as Range.
 

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
144
I am only ever selecting a single cell. Should I have the dimension set as something else and the inputbox returning a cell address? If so, how do I do this?

Regards,
Softedge
 

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
144
Is there anyone on line that can help with this?

Regards,
Softedge
 

Watch MrExcel Video

Forum statistics

Threads
1,133,277
Messages
5,657,799
Members
418,414
Latest member
ECMdusty

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