GregB
01-26-2012, 01:24 PM
Hello Everyone,
Thanks to MdmacKillop, I was able to put a dynamic drop down list in a worksheet. The list allows the end user to add new values to an Excel 'Named Range' if needed.
BUT, I need to be able to reference a named range from a different worksheet. The workbook needs to contain a 'List Sheet' that contains named ranges to apply to several other worksheets within the workbook.
Here is the code that Mdmackillop wrote:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4
Set c = Range("ColorsList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData(1, Target)
Case 5
Set c = Range("MetalList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData(2, Target)
End Select
End Sub
Sub AddData(col, Target)
Dim lReply As Long
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Cells(Rows.Count, col).End(xlUp)(2) = Target
End If
End Sub
And it worked great! In the above code, the changes are applied to the columns that contain the named ranges. But I need the changes to be applied to the named ranges in a different worksheet.
So for a workbook that contains two worksheets, "UserSheet" and "ListSheet", and two named ranges "ColorsList" and "MetalList",
I tried this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4
Set c = Worksheets("ListSheet").Range("ColorsList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData("ColorsList", Target)
Case 5
Set c = Worksheets("ListSheet").Range("MetalList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData("MetalList", Target)
End Select
End Sub
Sub AddData(c, Target)
Dim lReply As Long
lReply = MsgBox("Add " & Target & " to list of choices?", vbYesNo + vbQuestion)
If lReply = vbYes Then
Worksheets("ListSheet").Range(c).Cells(Range(c).Rows.Count + 1, 1) = Target
End If
End Sub
How can I get it to work on Ranges in different worksheets?
PLEASE HELP!
Thanks,
Greg
Thanks to MdmacKillop, I was able to put a dynamic drop down list in a worksheet. The list allows the end user to add new values to an Excel 'Named Range' if needed.
BUT, I need to be able to reference a named range from a different worksheet. The workbook needs to contain a 'List Sheet' that contains named ranges to apply to several other worksheets within the workbook.
Here is the code that Mdmackillop wrote:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4
Set c = Range("ColorsList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData(1, Target)
Case 5
Set c = Range("MetalList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData(2, Target)
End Select
End Sub
Sub AddData(col, Target)
Dim lReply As Long
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Cells(Rows.Count, col).End(xlUp)(2) = Target
End If
End Sub
And it worked great! In the above code, the changes are applied to the columns that contain the named ranges. But I need the changes to be applied to the named ranges in a different worksheet.
So for a workbook that contains two worksheets, "UserSheet" and "ListSheet", and two named ranges "ColorsList" and "MetalList",
I tried this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4
Set c = Worksheets("ListSheet").Range("ColorsList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData("ColorsList", Target)
Case 5
Set c = Worksheets("ListSheet").Range("MetalList").Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData("MetalList", Target)
End Select
End Sub
Sub AddData(c, Target)
Dim lReply As Long
lReply = MsgBox("Add " & Target & " to list of choices?", vbYesNo + vbQuestion)
If lReply = vbYes Then
Worksheets("ListSheet").Range(c).Cells(Range(c).Rows.Count + 1, 1) = Target
End If
End Sub
How can I get it to work on Ranges in different worksheets?
PLEASE HELP!
Thanks,
Greg