zredbaron
02-08-2017, 10:54 AM
Hi.
I'm attempting to refer to three named ranges, and for whatever reason, one is successfully cast as a Range object, and the other two are cast as a Variant. I'm trying to cast them all as a Range!
Using the Name manager and not VBA, I have set up three named Ranges highlighted below.
18284
Under ThisWorkbook in VBA, I have the following code:
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RngRoster, RngCategory, RngCategoryList As Range
Dim RefRoster, RefCategory, RefCategoryList As String
On Error Resume Next
Set RngRoster = ThisWorkbook.Names("RangeRoster").RefersToRange
Set RngCategory = ThisWorkbook.Names("RangeCategory").RefersToRange
Set RngCategoryList = ThisWorkbook.Names("RangeCategoryList").RefersToRange
On Error GoTo 0
RefRoster = ActiveWorkbook.Names("RangeRoster")
RefCategory = ActiveWorkbook.Names("RangeCategory")
RefCategoryList = ActiveWorkbook.Names("RangeCategoryList")
If Not Intersect(Target, RngRoster) Is Nothing Then
MsgBox "A name was added or changed!"
ElseIf Not Intersect(Target, RngCategory) Is Nothing Then
MsgBox "A category was added or changed!"
ElseIf Not Intersect(Target, RngCategoryList) Is Nothing Then
MsgBox "A category LIST entry was added or changed!"
End If
End Sub
For whatever reason, the three Ranges are cast differently. The only difference I can see is that they are all referring to different tabs, one of which refers to a column of a named table.
Oddly, one of the plain ranges works and the other does not. I would have thought the table would be the odd man out, but I guess not.
Regardless, my behavior is that I get a run-time error 1004: Method 'Intersect' failed. It will break when the Intersect() function is passed either the RngRoster or RngCategory variables.
Here is my Watch List, which shows that I am casting these as different types. :(
18288
I did my best to provide detail on my issue. Thank you so much for your time! :D
I'm attempting to refer to three named ranges, and for whatever reason, one is successfully cast as a Range object, and the other two are cast as a Variant. I'm trying to cast them all as a Range!
Using the Name manager and not VBA, I have set up three named Ranges highlighted below.
18284
Under ThisWorkbook in VBA, I have the following code:
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RngRoster, RngCategory, RngCategoryList As Range
Dim RefRoster, RefCategory, RefCategoryList As String
On Error Resume Next
Set RngRoster = ThisWorkbook.Names("RangeRoster").RefersToRange
Set RngCategory = ThisWorkbook.Names("RangeCategory").RefersToRange
Set RngCategoryList = ThisWorkbook.Names("RangeCategoryList").RefersToRange
On Error GoTo 0
RefRoster = ActiveWorkbook.Names("RangeRoster")
RefCategory = ActiveWorkbook.Names("RangeCategory")
RefCategoryList = ActiveWorkbook.Names("RangeCategoryList")
If Not Intersect(Target, RngRoster) Is Nothing Then
MsgBox "A name was added or changed!"
ElseIf Not Intersect(Target, RngCategory) Is Nothing Then
MsgBox "A category was added or changed!"
ElseIf Not Intersect(Target, RngCategoryList) Is Nothing Then
MsgBox "A category LIST entry was added or changed!"
End If
End Sub
For whatever reason, the three Ranges are cast differently. The only difference I can see is that they are all referring to different tabs, one of which refers to a column of a named table.
Oddly, one of the plain ranges works and the other does not. I would have thought the table would be the odd man out, but I guess not.
Regardless, my behavior is that I get a run-time error 1004: Method 'Intersect' failed. It will break when the Intersect() function is passed either the RngRoster or RngCategory variables.
Here is my Watch List, which shows that I am casting these as different types. :(
18288
I did my best to provide detail on my issue. Thank you so much for your time! :D