Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ================================================== ===== |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ================================================== ===== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel spin box - no Control tab in Format Control dialong box | Excel Worksheet Functions | |||
Dynamically Assign Objects to Form Objects. | Excel Programming | |||
Unable to remove Sheet objects in the Microsoft Excel Objects | Excel Programming | |||
editing control/form objects on a worksheet | Excel Programming | |||
Including control toolbox objects in tab order | Excel Programming |