Securing a dynamic range

[mnementh]

I have a question about dynamic ranges. I've made an excel workbook to document and calculate the changes made in a procedure at my job. One of the requirements for the document is a user name field on the title page. We only want users who are authorized to perform the task the workbook documents to show up in the user name drop down box. I've done this through a data validation list box on the title page that draws the names from a static named range in a very hidden sheet in the workbook.

The issue I'm having is the ease of adding names. As it stands now, whomever adds a name to the list of authorized users has to expand the named range by one cell and then add the user name to the list.

What I'd like is to automate that with a button. The manager who adds the names to the list would just click on a button and be prompted to enter a user name. It would then populate to the list and expand the named range to accomodate it.

My question is this: If I use a dynamic range to expand the named range as much as it needs to be depending on how many authorized personel are going to appear in it, will users be able to add their own name outside of the control of the data validation list? I don't want them to, as we don't want them to "qualify" themselves on a procedure they haven't been trained on.

So, I'd like to make it easy for the admin of the document to add a name to the authorized list, but impossible (or at least very hard) for the users of the document to do so.

Thanks!
Daniel

[DonkeyOte]

You state that the source for the Validation List is on a veryhidden sheet meaning no one other than those able to unhide the sheet [means unknown] can update the list.

On that basis if you revise the Named Range such that it becomes Dynamic it should have no effect on those not able to view the veryhidden sheet.

If we assume for sake of ex. that Sheet1 is entry sheet and Sheet2 is the veryhidden sheet containing the listing with names listed in A2 onwards then you can create your DNR using:

Please Login or Register  to view this content.

Then, if we further assume (for sake of demo.) that the DV List is to be used in cell A1 on Sheet1 then:

Please Login or Register  to view this content.

As the Admins update the listing on Sheet2 (toggling visibility) then so the DV options in Sheet1!A1 will update accordingly.

Of course you could use VBA to add the names without the Admins having to go through the process of toggling visibility of Sheet2 but it's not clear if that's a primary concern.