Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro fails on 1 out of 4 computers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro fails on 1 out of 4 computers
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) "CLR" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro fails on 1 out of 4 computers
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro fails on 1 out of 4 computers
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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro fails on 1 out of 4 computers
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 "CLR" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro fails on 1 out of 4 computers
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 wrote: 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 "CLR" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro fails on 1 out of 4 computers
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 "Dave Peterson" wrote: 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 wrote: 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 "CLR" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro not working on other computers | Excel Discussion (Misc queries) | |||
My macro doesn't work with other people's computers? | Excel Programming | |||
Macro not working on other peoples computers | Excel Discussion (Misc queries) | |||
Macro works differently on different computers, same version of Ex | Excel Discussion (Misc queries) | |||
Problems with AutoFill xlDown macro on certain computers - DESPERA | Excel Programming |