PDA

View Full Version : Solved: Reference a Range in a different Worksheet



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

Bob Phillips
01-26-2012, 05:10 PM
Just move the code to the same worksheet that contains the lists.

GregB
01-27-2012, 08:00 AM
Just move the code to the same worksheet that contains the lists.

Xld, I thank you for the response! I tried to use the code in the other 'list' sheet but that didn't work. I tried using the code in both sheets simultaneously, but that didn't work either. Any ideas?

Thanks,
Greg

GregB
01-27-2012, 08:35 AM
Xld, I thank you for the response! I tried to use the code in the other 'list' sheet but that didn't work. I tried using the code in both sheets simultaneously, but that didn't work either. Any ideas?

Thanks,
Greg

I'm getting a runtime error 1004: I'll investigate that and see what I come up with...

Bob Phillips
01-27-2012, 09:29 AM
NOt sure why you have a 1004, but I did notice the code referenced columns D & E, whilst the List sheet had data in A & B. Perhaps that is the problem.

GregB
01-27-2012, 10:07 AM
I tried placing the lists in columns D and E and that did not work. I would like to show you the place where the error occurs; a comment is below it.

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
''''''the line above is where I am getting the error''''''
End If
End Sub

Bob Phillips
01-27-2012, 10:31 AM
I had to change the formulas for ColorList and MetalLisy as dragging them did not update correctly, but when I did and I added an item to ColorList, it never got to that code as it found the value in the list (obviously I just added it!).

GregB
01-30-2012, 08:53 AM
Ok, I think I got! It looks like the way I was referencing the cells in the subroutine is the problem.
Instead of this code:
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
''''''the line above is where I am getting the error''''''
End If
End Sub
It should be
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(Worksheets("ListSheet").Range(c).Rows.Count + 1, 1) = Target
End If
End Sub

where the worksheet is being reference again within the cell() function.

Keep your fingers crossed and I thank everyone for their help and efforts so far.:yes

mdmackillop
01-30-2012, 12:06 PM
Setting the target ranges first simplifies things
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim CL As Range
Dim ML As Range

If Target.Cells.Count > 1 Then Exit Sub

Set CL = Worksheets("ListSheet").Range("ColorsList")
Set ML = Worksheets("ListSheet").Range("MetalList")

Select Case Target.Column
Case 4
Set c = CL.Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData(CL, Target)
Case 5
Set c = ML.Find(Target, lookat:=xlWhole)
If c Is Nothing Then Call AddData(ML, Target)
End Select

End Sub


Sub AddData(r As Range, Target As Range)
Dim lReply As Long
lReply = MsgBox("Add " & Target & " to list of choices?", vbYesNo + vbQuestion)
If lReply = vbYes Then
r(r.Count + 1) = Target
End If
End Sub

GregB
02-03-2012, 01:56 PM
r(r.Count + 1) = Target
---> Cool!

One More thing, how do I get you guys to give this thread a status of 'Solved'? Thanks again, and I will definitely be donating.:clap:

Bob Phillips
02-03-2012, 05:32 PM
At the top of the discussions on the right, there is a dropdown labelled Thread Tools. You should have the option to mark it solved.