My apologies for not getting back to you yesterday.
I'm a bit confused with your suggestion so perhaps I haven't explained clearly what I was after.
On the Access Log sheet I want something like a button that when pressed will show the contents of the one column range that is named Systems that is on a hidden sheet named Control. With the contents of the range shown I would like to be able to add an extra entry.
As I say I don't want to give the users access to the actual validation range as such just to see what is in the range and if need be add to it.
In any event my thanks for taking an interest in my question.
My understanding of your question is still the same, so probably my suggestion was not entirely clear to you.
If I understand correctly, you have a named range (Systems) on a hidden sheet (Control), and a cell (on sheet Access Log) with data validation using the named range. You don't want users to gain access to the Control sheet, but they may need to add additional systems to the named range.
My suggestion is that, if the user sees the drop-down from the Data Validation list he can select an 'Add...' option, which automatically adds a new system to the Systems range. Is that what you want?
Assuming it is, my solution is to amend the Systems range with a cell, where you type 'Add...'. In that way, the user will see Add as the first option in the drop-down list. The second step is, to create an Event, that does a check each time a change is made in the file, which is the code I mentioned above.
To elaborate on that Event, it checks whether the change is made on a cell with Data Validation on, and if the selected value is 'Add...'. If so, the Event prompts an input box where the user can add the new system. Then, the Event will add that system just below the last entry of the named range (Systems in this case), so it will appear the next time the user selects the drop-down list.
Just to make sure, the Systems range should be dynamic in order to automatically update after a new entry has been made. I would use the OFFSET method myself, see for instance:
Make your data validations dynamic! [quick tip] | Chandoo.org - Learn Microsoft Excel Online
I hope this was a somewhat more clear explanation
