Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position independent code
Greetings.
I wrote a few event handlers that do calculations when some cells are modified. For example: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then Application.EnableEvents = False Range("B6").Value = Range("C6").Value / Range("B13").Value Application.EnableEvents = True Else Application.EnableEvents = False Range("C6").Value = Range("B6").Value * Range("B13").Value Application.EnableEvents = True End If End Sub But then I insert rows to my sheets add/remove/reposition some stuff and all my VBA functions fail since Cell Addresses are hard-coded. How can I reference cells by name, or some sort of a dynamic reference (like Excel formulas that get automagically updated when you move things around)? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position independent code
give those cells a range name and use that instead of the address
-- Gary wrote in message ups.com... Greetings. I wrote a few event handlers that do calculations when some cells are modified. For example: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then Application.EnableEvents = False Range("B6").Value = Range("C6").Value / Range("B13").Value Application.EnableEvents = True Else Application.EnableEvents = False Range("C6").Value = Range("B6").Value * Range("B13").Value Application.EnableEvents = True End If End Sub But then I insert rows to my sheets add/remove/reposition some stuff and all my VBA functions fail since Cell Addresses are hard-coded. How can I reference cells by name, or some sort of a dynamic reference (like Excel formulas that get automagically updated when you move things around)? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position independent code
Please give an example, what is a range name?
Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position independent code
A range name is what it says on the box, a range that has been given a name
(handle) that can be used in formulae, and/or VBA. To create a name, select the range in question goto InsertNameDefine... add the name, say myRange, to the Names in Workbook (overtyping anything pre-loaded) the RefersTo box will alraedy be loaded with the range address OK out The code would then be, assuming that if C6 becomes C7, then B6 becomes B7, B13 becomes B14, etc. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("myRange")) Is Nothing Then Application.EnableEvents = False Target.Offset(0,-1).Value = Target.Value / Target.Offset(7,-1).Value Application.EnableEvents = True Else Application.EnableEvents = False Target.Value = Target.Offset(0,-1).Value * Target.Offset(7,-1).Value Application.EnableEvents = True End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message ups.com... Please give an example, what is a range name? Thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position independent code
in place of $C$6, create a range name. you can select the cell and either,
1 type a name into the address box next to the formula bar (it will show C6) and press enter, or 2. click insert/name/define and type a name and press add give it a unique name like Grand_Total then in your code use Range("Grand_Total") instead of Range($C$6) do this for the cells that will move -- Gary wrote in message ups.com... Greetings. I wrote a few event handlers that do calculations when some cells are modified. For example: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then Application.EnableEvents = False Range("B6").Value = Range("C6").Value / Range("B13").Value Application.EnableEvents = True Else Application.EnableEvents = False Range("C6").Value = Range("B6").Value * Range("B13").Value Application.EnableEvents = True End If End Sub But then I insert rows to my sheets add/remove/reposition some stuff and all my VBA functions fail since Cell Addresses are hard-coded. How can I reference cells by name, or some sort of a dynamic reference (like Excel formulas that get automagically updated when you move things around)? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
color code series by an independent column of data | Charts and Charting in Excel | |||
Position of CellCursor on Screen (absolute position) | Excel Programming | |||
Run a .xla as a independent application | Excel Programming | |||
Line position in code | Excel Programming | |||
Line position in code | Excel Programming |