Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
object by variable name
I want to call an object by its codename with a variable, rather than by the
object group the variable name It would take way too much time and typing to explain why I want to do it this way....But the main one is; I want to be able to pull up the right sheet no matter what the user renames the sheet to. And yes...I do want to allow the user to do this. for example... for i = 1 to 10 sheetname = "sheet" & i thisworkbook.sheetname.blah.blah <do this next i of course, this method will not work...but has anybody a way to accomplish the same task, but in a different strategy? Much appreciated ricky |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
object by variable name
Ricky:
Look at the CodeName property for a Worksheet. It allows you to use the "design view" name of a worksheet to reference the sheet directly. It is useful but may require a different approach since I don't think you can use the CodeName itself to refer to a sheet within a collection object: i.e., Worksheets("MyCodeName") won't work like Worksheets("MyTabName") does. However, it does allow you to use the CodeName as if it were an object in VBA: MyCodeName.Activate. Because of this, the specific loop you envision may not be possible. However, you could do a For..Each loop on the Worksheets collection and then do a SelectCase on the CodeName within that loop. -- George Nicholson Remove 'Junk' from return address. "Ricky M. Medley" wrote in message ... I want to call an object by its codename with a variable, rather than by the object group the variable name It would take way too much time and typing to explain why I want to do it this way....But the main one is; I want to be able to pull up the right sheet no matter what the user renames the sheet to. And yes...I do want to allow the user to do this. for example... for i = 1 to 10 sheetname = "sheet" & i thisworkbook.sheetname.blah.blah <do this next i of course, this method will not work...but has anybody a way to accomplish the same task, but in a different strategy? Much appreciated ricky |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
object by variable name
The following worked for me, maybe this example is a step
in the direction you want to go. Before trying this, insert sheets until you have 10 on a new workbook: Private Sub tester() For i = 1 To 10 sheetname = "sheet" & i Sheets(sheetname).Activate Next i End Sub This code successfully cycles through all 10 sheets. Now just modify it for whatever you are doing. -IA -----Original Message----- I want to call an object by its codename with a variable, rather than by the object group the variable name It would take way too much time and typing to explain why I want to do it this way....But the main one is; I want to be able to pull up the right sheet no matter what the user renames the sheet to. And yes...I do want to allow the user to do this. for example... for i = 1 to 10 sheetname = "sheet" & i thisworkbook.sheetname.blah.blah <do this next i of course, this method will not work...but has anybody a way to accomplish the same task, but in a different strategy? Much appreciated ricky . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
object by variable name
yeah....your last suggestion sounds like a viable one. {go through each one
until if finds the right one}, but my code is so long already, and is starting to take time to execute...my goal is to keep every execution under 500 miliseconds. This would take two loops...one to go through 1-10, and one to go through each worksheet. Thanks though...I will try to see if time is under limits. ricky "George Nicholson" wrote in message ... <<snip Because of this, the specific loop you envision may not be possible. However, you could do a For..Each loop on the Worksheets collection and then do a SelectCase on the CodeName within that loop. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
object by variable name
Maybe, maybe not.
If you change your CodeNames so that they end in 01, 02, ... 10 you might be able to do what you want in one loop. For each wks in Worksheets strTail = Right(wks.Codename, 2) If IsNumeric(strTail) then Select Case Int(strTail) Case 1 to 10 'Do something to this wks that ends in a number between 01 & 10 Case Else 'Do nothing to this wks that ends in a number 10 EndSelect Else 'Do nothing to this wks that doesn't end in a number. End if Next wks The trick may be to adopt a naming convention that allows you to handle CodeNames efficiently in a loop like this. -- George Nicholson Remove 'Junk' from return address. "Ricky M. Medley" wrote in message ... yeah....your last suggestion sounds like a viable one. {go through each one until if finds the right one}, but my code is so long already, and is starting to take time to execute...my goal is to keep every execution under 500 miliseconds. This would take two loops...one to go through 1-10, and one to go through each worksheet. Thanks though...I will try to see if time is under limits. ricky "George Nicholson" wrote in message ... <<snip Because of this, the specific loop you envision may not be possible. However, you could do a For..Each loop on the Worksheets collection and then do a SelectCase on the CodeName within that loop. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
object variable or with block variable not set | Excel Discussion (Misc queries) | |||
Error 91 - Object variable with block variable not set | Excel Programming | |||
Object Variable or With Block variable not set? | Excel Programming |