Getting Run time error '9' from macros in similar workbooks.

[garysallred]

Hi everyone,

I hope you all are having as beautiful a day as we are here in Southern Utah. I will try to explain my problem. I have created surveys in Excel wherein the questions are answered by choosing from drop-down lists. That part seems to work fine. However, in one section I have a series of twenty objects (arrows) which survey takers choose one of to indicate their level of satsifaction. Each arrow is assigned a short macro which enters a value such as "0", 0.5, 1, 1.5 etc. up to 10, into a specific cell. The problem is when I save the workbook to another name to eliminate re-doing sections of similar surveys, I get the Run time error '9' and the macro does not work any more. When I try to re-assign the macros, I still get the error. When I try to record new macros, I see a list of macros I have already recorded for other workbooks, which I think should work for the current one. How to I keep macros for different workbooks separate so they are not impacting each other? What, if anythng keeps me from assigning macros recorded in a different workbook?

Hope this makes sense. I would appreciate anyone who can help.

[tigeravatar]

garysallred,

runtime error 9 is "Subscript out of range." That means that a variable is attempting to reference an object that doesn't exist, or an array location that doesn't exist.

For example: if you have any workbook object variables that get assigned to the original workbook name, they will fail with that error because the file was saved with a different name.

However, it is difficult to say what the exact problem is without seeing your code or the workbook.

[garysallred]

Thank you for your response. I believe I figured out the problem. It appears that any macros run in a workbook or activated by objects in a workbook should be macros in the current workbook. This can be assured in the dialogue box which opens to view macros. I don't yet understand how it would work to run macros from "All workbooks', but I think it would be confusing unless the information in each workbook is the same.

[tamesh.d]

Thank you for your response. I believe I figured out the problem. It appears that any macros run in a workbook or activated by objects in a workbook should be macros in the current workbook. This can be assured in the dialogue box which opens to view macros. I don't yet understand how it would work to run macros from "All workbooks', but I think it would be confusing unless the information in each workbook is the same.

hi,

recently i identified the problem while we record macro and mostly we close the file before stop macro, but when we do not close the workbook related to macro and stop the recording, we find same error.
so you can record once again and then close the workbooks before stop recording macro, which is related to that particular macro recording will be cleared.

try this.

[johnbell394]

You can fix runtime error 9 easily from the tool. The manual process is very problematic so you can use third party tool to repair it. If you are getting runtime error 9 then use any tool to fix runtime error. You can also fix r6034 runtime error from the tool.