Hello,
I'm am trying to find some way to determine if a particular tab falls before/after/between other tabs in a workbook. For example, if I have a tab "Top" and a tab "Bottom", I want to be able to determine if tab X is between them. Does anyone know if that is possible?
Thanks,
Eric
Something like this:
Sub TabTest() If Sheets("Top").Index > Sheets("X").Index And _ Sheets("X").Index > Sheets("Bottom").Index Then _ MsgBox "Yes, it's in between" End Sub
You might have to switch < and > depending on the results you expect.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
You mean in VBA? The Index property, 1 to Sheets.Count, is the order in which the tabs appear.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks for the replies. I assume it is not possible to do without using VBA?
Sans VBA , just look at the tab order.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Sorry, I should have specified; I am trying to do so programatically. I have a column in which I want to display whether the iniative referenced in each row is "in" or "out" of a plan, as determined by whether the referenced tab is between the "top" and "bottom" tab's I am using to designate what is in (and should be summed for the summary).
Thanks,
Eric
You can observe the tab order.I assume it is not possible to do without using VBA?
Programmatically in Excel means VBA.I am trying to do so programatically.
Last edited by shg; 02-12-2010 at 12:32 AM.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Sorry, I suppose the correct term would be dynamically....
Hi,
dynamically... is also... programatically ...
Ideally, post your workbook for an adequate solution ...
No, there are dynamic worksheet formulas. In this context I would read "dynamic" to mean "adjusts itself as the data changes".Originally Posted by JeanRage
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I took a stab at trying it in VBA and eventually got to the following:
This partly accomplishes what I am looking for, but it doesn't update the value in the cells that I have the formula in when I change the order of the tabs it is referencing. I've never used VBA before, so I'm assuming that it just may be that VBA defined functions don't auto-recalculate like regular functions.Function TabLoc(tabname As String) If Sheets("<-AbovePlan").Index > Sheets(tabname).Index And _ Sheets(tabname).Index > Sheets("AbovePlan->").Index Then _ TabLoc = "Above" Else _ If Sheets("<-AbovePlan").Index > Sheets(tabname).Index And _ Sheets(tabname).Index > Sheets("InPlan->").Index Then _ TabLoc = "In" Else TabLoc = "Out" End Function
So, I think what I am looking for ideally is a way accomplish the functionlity provided in the function above without VBA.
Thanks Again,
Eric
FWIW - AFAIK - there's no real way of doing this completely avoiding code... there's the old
GET.WORKBOOK(1)
route via a Name but that's using the old XLM function so it's not exactly code free but obviously doesn't warrant work in VBEditor and would be a "faff" to do what you want compared to the UDF
(I think morefunc.xll has a function too but that's an add-in so again does run on code per se)
Regards recalculation on moving sheets etc...
if you make the Function Volatile you should find it does what you want given the movement / insertion / deletion of a sheet would be a Volatile action, eg:
but if you're using this function a lot it will slow your file a tad...Function TabLoc(tabname As String) Application.Volatile .... End Function
NOTE: you must be running on Auto Calc mode though... else the Volatility will not trigger a Calculation - ie Calculation (as normal) would need to be invoked via manual intervention (eg F9)
For more info. on Volatility and all things "calculation" see the link in my sig.
Last edited by DonkeyOte; 02-12-2010 at 03:17 AM. Reason: added point re: link
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you for the suggestions. I tried both approaches and am getting a msg box saying the function is not valid when trying to use the get.workbook() function (I'm assuming I don't have the syntax right). I also added the Application.volatile code, but the VBA is not re-calculating unless I click into the cell with the formula and hit enter (everything else is auto recalculating).
Any ideas?
Thanks,
Eric
Eric, per the earlier caveat, are you running on Auto Calc Mode ?
To check:
Click Options on the Tools menu, and then click the Calculation tab.
Under Calculation, click the calculation mode that you want to use.
If not on Auto the Volatile statement won't make any difference I'm afraid.
IMHO the GET.WORKBOOK route isn't worth pursuing - I was just "shooting the breeze".
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi,
I did have autocalc on, but I think I was modifying the function in the wrong version of the function. I've never used VBA before, and I think I had managed to have multiple modules with similar functions. I fixed that and now it is working. Thank you for your all your help.
Eric
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks