Macro fails on 1 out of 4 computers

[CLR]

Hi All.........
I have a little Excel program that works fine on three different computers,
and mostly works on a fourth, but one of the macros fails on this line on
the fourth computer...

Set myCell = Application.InputBox(Prompt:="Please select a Vendor by" &
Chr(10) & Chr(13) & Chr(13) & " clicking on their NAME cell",
Type:=8).Cells(1)

All three are Windows XP and Excel 2k

Anybody know why, please?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Joel]

Try this instead

Set myCell = Application.InputBox( _
Prompt:="Please select a Vendor by" & _
Chr(10) & Chr(13) & Chr(13) & _
" clicking on their NAME cell", _
Type:=8)

Set myCell = myCell.Resize(1)

 

[Dave Peterson]

If the user hits cancel, then you'll get an error--unless you're checking for
that:

set mycell = nothing
on error resume next
Set myCell = Application.InputBox(Prompt:="Please select a Vendor by" _
& vblf & vblf & "clicking on their NAME cell", Type:=8).Cells(1)
on error goto 0

if mycell is nothing then
msgbox "user hit cancel"
else
msgbox "user selected: " & mycell.address(external:=true)
end if

=========
But I bet that's not the problem.

What fails?

 

[CLR]

Hi Dave..........sorry, the failure mode is
Macro stops and editor pops up
Error message says "Cant find project or library"
Cursor highlights the first "Chr" string in the line of code
The Reference window shows
"MISSING Microsoft Access 8.0 Object Library"

Note, on two of the computers that will run this macro, that library checks
in ok, on the third it does not appear at all.....all three of them will run
the macro just fine.

This morning, a second computer failed on that macro at the same point as
the other failure. only difference is this one uses XLXP, the rest all use
XL2k. So now I got three that work fine, and two that don't

I will try changing the code if necessary, but the problem appears systemic,
no?

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Ok Guys.........

Apparently this particular excel file, REQUIRES that "Microsoft Access 8.0
Object Library", as it does not show up in my Reference list when I start
Excel 2k, but is there as soon as I open this file. Other macros in that
file work fine on the errant computers. Neither of he errant computers have
Microsoft Access installed, while all of the :good" computers do have Access
installed......therefore the Library is available and all is well with them.

Must I have Access installed on each computer in order to be able to run
this file with this macro in it?..........

There is another macro in this file that opens an ACCESS file and imports
it. But that is a Monthly Maint macro and not needed to be run by individual
users.

Here's the complete macro.....the debugger highlights the first "Chr" in the
line starting with " Set myCell = Application.InputBox"

Sub SelectVendor()
Sheets("DATA").Select
Call ReturnToTop
Range("B12").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Dim myCell As Range
Set myCell = Nothing
On Error Resume Next 'cancel will cause an error
Set myCell = Application.InputBox(Prompt:="Please select a Vendor by" &
Chr(10) & Chr(13) & Chr(13) & " clicking on their NAME cell",
Type:=8).Cells(1)
On Error GoTo 0

If myCell Is Nothing Then
MsgBox "You didn't select a cell!"
Sheets("ReportCard").Select
Range("a1").Select
End
Else
MsgBox "You selected Vendor: " & Chr(13) & Chr(13) & Chr(10) &
myCell.Value 'Address(external:=True)
'MsgBox "You selected: " & myCell.Address(external:=True)
End If

myCell.Select

Selection.Offset(0, -1).Copy
Sheets("ReportCard").Select
Range("A1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub


Any advice would be appreciated......
Tks,
Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Is your code actually using anything from Access--in any macro?

If yes, then you'll have to install it on all the pcs.

But if no, you should be able to remove that reference (simply uncheck it), save
the workbook and redistribute a fresh copy.

You may want to ask the person with the problem to uncheck the reference (and
save his/her copy), then test the macro.

If it works, you can redistribute the macro when you need to.

 

[CLR]

Hi Dave.........
I just finished doing that and it worked. I'm not sure if the Library is
used anywhere else in this file or not....don't know how to tell without
running every macro and if they fail, well.....guess it was needed.

Anyway, thanks for the suggestion/solution.....much appreciate

Vaya con Dios,
Chuck, CABGx3