user related macro problem

[Thanatos]

Here's a weird one for you...

i am using the macro below to get excel to hide all sheets but the one called "open"...

Sub savemini() 
 
ActiveWorkbook.Unprotect "letmein" 
 
  For Each sh In ThisWorkbook.Worksheets 
       If Not sh.Name = "open" Then sh.Visible = xlVeryHidden 
   Next sh 
 
    ActiveWorkbook.Save 
End Sub  

So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.

Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.

The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.


Anyone got an idea why this is happening?

It's nothing to do with security settings or locked sheets (have tried both of those already!)

[Leith Ross]

Hello Thanatos,

My guess is you are sharing this workbook. Having multiple users change the protection on various sheets is a disaster in the making. Each user should have their own workbook with only the sheets he or she needs.

Sincerely,
Leith Ross

[Thanatos]

Hello Thanatos,

My guess is you are sharing this workbook. Having multiple users change the protection on various sheets is a disaster in the making. Each user should have their own workbook with only the sheets he or she needs.

Sincerely,
Leith Ross

Firstly, apologies for the duff title! (got my warning! :D) any mods please feel free to change it to something more practicle like "user related macro problem" or something

Regarding the problem - no user is changing the protection on any sheet. There is just a simple macro that runs on open only allowing certain sheets (dependent on the user) to be visible. The workbook is not "shared" as such - e.g. only one user can use it at a time. However it is in a shared folder so everyone involved can have access to it. So none of the reasons you give should be effecting it. (Having seperate workbooks for 30 members of staff and having to maintain them all would be a logistical nightmare! :D)

[Andy Pope]

What error to the users get?


ensure the "OPEN" worksheet is visible before hiding all the others.

[royUK]

Thanatos, I have asked you to change your Thread Title. Please do so before I lock this post!

[Thanatos]

What error to the users get?


ensure the "OPEN" worksheet is visible before hiding all the others.

The message is "Run time error '1004' Unable to set the visible property of the worksheet class"


The open tab isnt showing when they run this... i will look into that - thanks! (although surely that would stop it working for everyone rather than a select few?)




Many thanks royUK for your help with changing the thread title!

[Thanatos]

Andy Pope - worked a treat - bloody obvious now I think about it! lol

Cheers all!

[Andy Pope]

do you know for certain that the file is in the same state for all users.
no chance they changed something?

[royUK]

You will need to re-protect the workbook. This code should unhide open if it is closed, & hide all others

Please Login or Register  to view this content.

[Thanatos]

do you know for certain that the file is in the same state for all users.
no chance they changed something?

Yeah, they have about 5 cells they can enter information in (how much work they did that day) and thats it. I made it idiot proof (famous last words)


And good point about re-protecting the work book - thx royuk