Safe Way To Have Users Edit List Of Items In Named Range

**Spencer** · 03-25-2011, 07:18 PM

I have a list of Units (Each, Lump Sum, Miles, Months, Square Feet, Etc.) in a named range on worksheet that is very hidden. Users will choose those units in various drop down lists throughout the workbook through data validation.

Right now this is set up so that list is static and can't be edited by the end users (my co-workers). Is there a safe method to allow users to add, edit, or delete items on that list, without making the worksheet the list is on visible?

Any ideas would be appreciated.

**Leith Ross** · 03-25-2011, 07:56 PM

Hello Spencer,

Not sure I follow your logic about allowing the user to edit the ranges when the ranges are on a very hidden worksheet. The purpose of a very hidden worksheet is to prevent the users from either seeing or altering the data.

I would create a UserForm to edit the ranges. The UserForm could be easily be called from a shape or Forms Command Button on the worksheet or displayed using a hot key. This will allow to safely control the user's actions.

**Spencer** · 03-26-2011, 12:30 AM

Leith, thanks for pointing me in the right direction. I had not known about or used user forms before.

I found a pretty good tutorial on user forms here: http://www.contextures.com/xlUserForm01.html

That tutorial helped me to create the very basic user form that I have attached. The userform I created will allow me to add new items to the Named Range list. I even figured out how to resort the list after I added the new value.

Is there a way I can modify this user form, or create a different one, that would allow me to edit or delete items already in the Named Range list?

I've done quite a bit of Internet Searches and nothing has pointed me in the right direction yet.

Edit: I forgot to ask if anyone knows how I can activate the text box in the user form after I set it's value to "" as seen in the snippet of code below? I have to hit the tab key on my keyboard to activate the text box again and it would be nice if I could somehow take care of that in the code instead, so the user could immediately start typing.

Please Login or Register  to view this content.

Thanks again for your help,
Spence

**Spencer** · 03-26-2011, 10:21 AM

I think I have most of this figured out. I have attached a new example showing what I came up with.

The input box that appears after I hit the edit button has both an OK and a Cancel button, even though my code says the input box should be vbOKOnly. If anyone can tell me what I need to do to get rid of that Cancel button, I would appreciate it.

Here is a relevant snippet of my code :

Please Login or Register  to view this content.

Thanks, Spence

**snb** · 03-26-2011, 11:25 AM

I'd prefer an alternative, simpler approach:
Designate a certain cell as inputcell for the named range.
After entering some text in it, doubleclicking will add the text to the (very hidden) named range. Cfr. the attachment.