There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
 
Business Applications
Tag Cloud
audio blue blue screen boot bsod compaq computer cpu crash dell drivers dvd error excel firefox format freeze freezing hard drive hardware install internet internet explorer kb951748 lan laptop loss of internet malware memory motherboard network networking outlook outlook 2007 problem restart screen security slow sound trojan update virus vista windows windows xp winxp wireless zonealarm zone alarm
Search
Search in:
 
Advanced Search
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: Excel 2003 and Run-time Error


Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members. Enjoy!

Closed Thread
 
Thread Tools
computerman29642's Avatar
Computer Specs
Senior Member with 1,669 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
19-Aug-2008, 11:01 AM #1
Question Solved: Excel 2003 and Run-time Error
I have created a macro in Excel 2003. Once I tested the macro several times, I placed the macro in a blank workbook, and saved it as "Personal.xls". I saved the "Personal.xls" workbook in this file path...

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

I opened another blank Excel workbook, and hide the Personal.xls window.

When I try running the macro from a button on the toolbar, I get the following Run-time error....

Run-time error '9'
Subscript out of range

This is the line of code that gets highlighted when I debug the error..

Code:
Set ws = ThisWorkbook.Sheets (.Name)
Can anyone help me to resolve this error?
__________________
Computerman29642

"I can do all things through Christ which strengtheneth me."
(Philippians 4:13)
___________________________________________________

“Give, and it shall be given to you. For whatever measure
you deal out to others, it will be dealt to you in return.”
(Luke 6:38)
Aj_old's Avatar
Computer Specs
Senior Member with 624 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
19-Aug-2008, 11:05 AM #2
The Personal.xls does not allow such thing as Thisworkbook, your better save the file as an Excel Add-in and set a reference to it so it will be loaded each time you open excel, and it allows you to work with thisworkbook
computerman29642's Avatar
Computer Specs
Senior Member with 1,669 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
19-Aug-2008, 11:19 AM #3
I saved the blank workbook as an"Add-in". I have checked it in the References.

I tried to setup a button on the toolbar to run the macro, but the macro did not show up as a choice. How do I run it now?

Last edited by computerman29642 : 19-Aug-2008 11:30 AM.
Aj_old's Avatar
Computer Specs
Senior Member with 624 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
19-Aug-2008, 12:13 PM #4
computerman29642's Avatar
Computer Specs
Senior Member with 1,669 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
19-Aug-2008, 12:46 PM #5
So, If I am reading this line correctly, "ThisWorkbook will always refer to the Add-in, not the users Workbook. Use ActiveWorkbook instead." I am still unable to use 'This workbook'...correct?
Aj_old's Avatar
Computer Specs
Senior Member with 624 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
19-Aug-2008, 12:52 PM #6
What ".Name" refers to ?
computerman29642's Avatar
Computer Specs
Senior Member with 1,669 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
19-Aug-2008, 12:55 PM #7
I believe it is used to insert the actual name of the worksheet.

Once I changed the 'This Work' to "Active Workbook" the code started to work.
Aj_old's Avatar
Computer Specs
Senior Member with 624 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
19-Aug-2008, 12:58 PM #8
If you don't have a line like "with ..." before it then it is the problem!
computerman29642's Avatar
Computer Specs
Senior Member with 1,669 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
19-Aug-2008, 12:59 PM #9
Here is the entire section of code..

Code:
With ActiveSheet
        Set ws = ActiveWorkbook.Sheets(.Name)
    End With
Thanks AJ!

Last edited by computerman29642 : 19-Aug-2008 01:34 PM.
Aj_old's Avatar
Computer Specs
Senior Member with 624 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
20-Aug-2008, 02:23 AM #10
Quote:
Originally Posted by gamecockfan View Post
Here is the entire section of code..

Code:
With ActiveSheet
        Set ws = ActiveWorkbook.Sheets(.Name)
    End With
Thanks AJ!
why not use:
Code:
Set ws = ActiveSheet
It should work fine.
computerman29642's Avatar
Computer Specs
Senior Member with 1,669 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
20-Aug-2008, 09:02 AM #11
Thanks AJ!
Aj_old's Avatar
Computer Specs
Senior Member with 624 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
20-Aug-2008, 09:18 AM #12
You are welcome
Closed Thread

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.



Thread Tools


You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 03:18 PM.
Copyright © 1996 - 2008 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Powered by Cermak Technologies, Inc.