Show & Add to Validation Range

[lapta301]

Dear All

On a hidden sheet named Control I have a range named Systems that is used for Data Validation purposes on the only visible sheet named Access Log.

Is there a way on the Access Log sheet to have an option to show the items in the Systems range and if needed add an extra item. At present the range extends from A4 to A14.

I don’t really want to unhide the Control sheet as some of the users to put it mildly could create havoc.

As ever any help is most appreciated.

 

[JLeno]

There is a way to do it:

1. Add an extra cell to the Named range (preferably the first cell), which i called 'Add...'

2. Add this Event to your ThisWorkbook Object in VBE:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ValType As Long
Dim rg As Range
Dim resp
If Target.Cells.Count <> 1 Then Exit Sub
On Error Resume Next
ValType = Target.Validation.Type
On Error GoTo 0
If ValType = 3 And Target.Value = "Add..." Then
    
    Set rg = Range(Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1))
    resp = Application.InputBox("Please enter the name of the System you want to add.", _
        "Add new " & LCase(rg.Cells(1, 1).Offset(-1).Value))
    If CStr(resp) = "False" Then GoTo EndHandler
    rg.Cells(rg.Rows.Count).Offset(1).Value = resp
    Target.Value = resp
End If
EndHandler:
Set rg = Nothing
End Sub

3. Make sure the Named range is a dynamic range so it updates automatically.

 

[lapta301]

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.

 

[JLeno]

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

 

[lapta301]

Yes you certainly have it and the explanation is fine. Thank you

I'll have a look at the Chandoo link tonight and download the example file which I can't do here.

Again my sincere thanks.

 

[lapta301]

Hi Sorry to be a nuisance

Although I have not yet made the lookup range dynamic I have put your code into the ThisWorkbook and although I didn't expect it to work properly, with Add being the first entry in the lookup range the code did not do anything when the Add option was selected from the validation list.

Am I wrong in thinking that the option to Add would show the Input Box.

Again my thanks