Excel 2007 : VB module changes values on all sheets every time it runs

[aff219]

I have the following module:

Please Login or Register  to view this content.

this is designed to give me a total count of worksheets in a workbook.

The problem is that I have multiple workbooks using this command, and I've realized now that when I use it on one workbook, it takes those values and places them in ALL workbooks, which is causing inaccurate results to be displayed everywhere it's used EXCEPT whatever sheet I'm currently working on (as it does the formula for that sheet)

for example:
I create workbook 1. when done, it has 5 worksheets total. I put in the formula where I want it and it displays (accurately) "5"

I then create workbook 2 while workbook 1 is still open. Workbook 2 has 3 sheets. When done creating and filling it with info, I put the formula where I want it in workbook 2. It calculates "3".

the issue, is it takes that "3" and plugs it into both workbook 1 and workbook 2's cells that contain the shtcnt() function.

The end result is worksheet 2 has the correct information, but workbook 1 now displays "3" instead of "5"

How would i stop it from doing this? The only Idea I have is to create a "shtcnt1()" "shtcnt2" etc etc for every worksheet I create, but that's not very practical

[aff219]

no one? =/

[foxguy]

aff219;
Your function uses "ActiveWorkbook". There is only 1 ActiveWorkbook at a time. So ALL open workbooks will get the exact same answer from that function at any time. If you go back to workbook1 and cause it to calculate, you will then see that workbook 2 has 5.

You need to change your function to :

Please Login or Register  to view this content.

Then in your worksheet enter "=ShtCnt(A1)" (any cell address will do).

[aff219]

aff219;
Your function uses "ActiveWorkbook". There is only 1 ActiveWorkbook at a time. So ALL open workbooks will get the exact same answer from that function at any time. If you go back to workbook1 and cause it to calculate, you will then see that workbook 2 has 5.

You need to change your function to :

Please Login or Register  to view this content.

Then in your worksheet enter "=ShtCnt(A1)" (any cell address will do).

thanks, might i ask what the A1 part does?

*edit*

when i use it I seem to get #VALUE! error

[foxguy]

The A1 is just any cell address. You could use Z69 or anything else.
I don't know how to pass a workbook to the Function. So I pass a range (any cell) and let the Function figure out what workbook it is in.

[aff219]

The A1 is just any cell address. You could use Z69 or anything else.
I don't know how to pass a workbook to the Function. So I pass a range (any cell) and let the Function figure out what workbook it is in.

anyoen able to help with the VALUE problem?

[foxguy]

It works fine on my computer.
Do you know how to debug a macro?

If you don't, make a new workbook and put the function in it and try to use it in any worksheet. Make sure the error occurs then upload the workbook and I'll take a look.

[aff219]

It works fine on my computer.
Do you know how to debug a macro?

If you don't, make a new workbook and put the function in it and try to use it in any worksheet. Make sure the error occurs then upload the workbook and I'll take a look.

Thanks for the help. I'm an idiot lol =/

having me make a new worksheet with the formula made me realize my mistake, i only plugged the new code into one of the worksheets (I currently have 7) so only one was working properly.

thanks

[foxguy]

If you put the code into a Standard module instead of a Sheet module it will be available to all worksheets. Code in a Sheet module is only available to that theet.