Control objects in Excel
 

In one of the sheet I am developing, there is a range of
cells (D11:F24) which is used to hold user input data.
There are a number of VBA sub routines referred to this
range, which the range address is hardcoded in it.

If the range changes its position whether by cut and paste
or by inserting a new row before it, the address in the
script does not change and cause errors.

I realize it is a bad thing to do. I gave the range a name
and tried to refer to the name of the range in the VBA
script, but failed. Any suggestion on how to get this to
work? I guess, same as other language, it is a bad
practice to "hard code" cell address in the script.

Thanks,
pac


Here is one of the subs

================================================== ===
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Dim myRange As Range

Set myRange = Range("D11:F24") '<< Want to change
"D11:F24" to the name "inputRG"

Set myRange = Intersect(myRange,
myRange.Parent.UsedRange)
If Not Intersect(Target, myRange) Is Nothing Then
Application.EnableEvents = False
For Each i In myRange
If Not (i.Formula Like "=VLOOKUP($B*" _
And i.Formula Like "=ROUND((VLOOKUP$B*") Then
Sheet19.defaultMan.Value = False
Exit Sub
End If
Next i
Application.EnableEvents = True
End If
End Sub
================================================== =====

Control objects in Excel
 

Sorry: Wrong title. It shoud read "How to refer to a
range name in VBA".
Thnaks





packat wrote:
In one of the sheet I am developing, there is a range of
cells (D11:F24) which is used to hold user input data.
There are a number of VBA sub routines referred to this
range, which the range address is hardcoded in it.

If the range changes its position whether by cut and paste
or by inserting a new row before it, the address in the
script does not change and cause errors.

I realize it is a bad thing to do. I gave the range a
name
and tried to refer to the name of the range in the VBA
script, but failed. Any suggestion on how to get this to
work? I guess, same as other language, it is a bad
practice to "hard code" cell address in the script.

Thanks,
pac


Here is one of the subs

================================================== ===
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Dim myRange As Range

Set myRange = Range("D11:F24") '<< Want to change
"D11:F24" to the name "inputRG"

Set myRange = Intersect(myRange,
myRange.Parent.UsedRange)
If Not Intersect(Target, myRange) Is Nothing Then
Application.EnableEvents = False
For Each i In myRange
If Not (i.Formula Like "=VLOOKUP($B*" _
And i.Formula Like "=ROUND((VLOOKUP$B*") Then
Sheet19.defaultMan.Value = False
Exit Sub
End If
Next i
Application.EnableEvents = True
End If
End Sub
================================================== =====

Control objects in Excel
 

Instead of using myrng.parent.usedrange, you could use me. It represents the
sheet that contains the code (when you're under the worksheet module.

And I changed your "exit sub" to "exit for". You'll want to turn the event
handler back on.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Range
Dim myRange As Range

If Target.Cells.Count 1 Then Exit Sub

'<< Want to change "D11:F24" to the name "inputRG"
Set myRange = Me.Range("inputRG")

Set myRange = Intersect(myRange, Me.UsedRange)

If Not Intersect(Target, myRange) Is Nothing Then
Application.EnableEvents = False
For Each i In myRange
If Not (i.Formula Like "=VLOOKUP($B*" _
And i.Formula Like "=ROUND((VLOOKUP$B*") Then
sheet19.defaultMan.Value = False
Exit For
End If
Next i
End If
Application.EnableEvents = True

End Sub



packat wrote:

In one of the sheet I am developing, there is a range of
cells (D11:F24) which is used to hold user input data.
There are a number of VBA sub routines referred to this
range, which the range address is hardcoded in it.

If the range changes its position whether by cut and paste
or by inserting a new row before it, the address in the
script does not change and cause errors.

I realize it is a bad thing to do. I gave the range a name
and tried to refer to the name of the range in the VBA
script, but failed. Any suggestion on how to get this to
work? I guess, same as other language, it is a bad
practice to "hard code" cell address in the script.

Thanks,
pac

Here is one of the subs

================================================== ===
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Dim myRange As Range

Set myRange = Range("D11:F24") '<< Want to change
"D11:F24" to the name "inputRG"

Set myRange = Intersect(myRange,
myRange.Parent.UsedRange)
If Not Intersect(Target, myRange) Is Nothing Then
Application.EnableEvents = False
For Each i In myRange
If Not (i.Formula Like "=VLOOKUP($B*" _
And i.Formula Like "=ROUND((VLOOKUP$B*") Then
Sheet19.defaultMan.Value = False
Exit Sub
End If
Next i
Application.EnableEvents = True
End If
End Sub
================================================== =====



--

Dave Peterson