limit sheet names in connected workbooks

[jomili]

Weird request, thought I'd throw it out there. Three users have their individual workbooks to work on community projects. First step in the process is to name the tab, for instance "Project 1", "Project 2", etc. Looking for a way, if user Bob has started "Project 14", user Sally can't name one of her tabs "Project 14". Any ideas how to arrange that?

[JLGWhiz]

Have one person resoponsible for the names.

[jomili]

That was my suggestion as well. For some reason that wasn't well received. I had thought of using INDIRECT, but it doesn't work on closed workbooks, so I'm at a loss so far.

[protonLeah]

Require all tabs append user name/initials
Project 1.Bob
Project 1.Sally

[jomili]

Ben,
I guess I should have clarified. The idea is to not have two people working on the same project. Your solution wouldn't prevent two users from working the same project.

I've got a start on an idea, but I'm hitting a roadblock. My basic idea is to have a VLookup to the other workbooks on the same sheet name. If the sheet doesn't exist it throws an error, but if it doesn't throw an error I can pop up a msgbox to say that the sheet already exists. My problem is that before it throws an error Excel pops up a little box to encourage the user to pick a proper name. How do I defeat that box? I thought "DisplayAlerts" would handle it, but no.

Please Login or Register  to view this content.

[JLGWhiz]

I think it would help if you gave a clear explanation of how the employees get their project assignments currently. Are they using a shared workbook? There is a logic link missing somewhere in your issue. I know you said they have their individual workbooks, but where do their workbooks derive from. There has to be a central point of work distribution somewhere and that is where you control the porject assignment. It does not make sense that an employee can just randomly select what they want to work on.

[jomili]

No shared workbook. There is a master workbook that is updated from the 3 individual workbooks. The projects come through email to any of 6 different people, or through snail mail to any of the 6, or both. There are 15K users, any of which can send an email to the group of 6, and often they send them both ways. 3 people process the requests, each with their individual workbook. The only thing constant is the tab naming convention, Project 1, Project 2, etc. This is not MY process, so I can suggest ways to improve it but ultimately I'm trying to work with what they have now.

[JLGWhiz]

Good luck, I won't touch it. It is definitely an organizational systemic problem more than an automation problem. If I worked there, I would not suggest a change, I would demand a change.