application.inputbox returning with unexpected error

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
143
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
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
143
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
143
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
143
I am entering a cell address by picking on a cell. copyto is dimensioned as Range.
 

Softedge

Board Regular
Joined
Dec 14, 2008
Messages
143
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
143
Is there anyone on line that can help with this?

Regards,
Softedge
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,561
Messages
5,512,071
Members
408,878
Latest member
stams

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top