Absolute Cell reference in VB

[dampfleece]

Hello all,

Can anyone advise how to reference a cell in VB so that the macro continues to work even if rows / columns are inserted in the worksheet at a later date.

I am putting together a form for other users to complete and using code to ensure that certain rules are followed for defaults and fields for completion. However, just using the cell ref (C4) or ($C$4) seems to fail if I then insert a row above row 4.

For ref. an example from the code is:



If Not Application.Intersect(Target, Range("C4")) Is Nothing Then
Application.EnableEvents = False
If Range("C4") = "Capped Usage" Then
Rows("5:5").EntireRow.Hidden = False
Else
Rows("5:5").EntireRow.Hidden = True
Range("C17") = ""
End If
Application.EnableEvents = True
End If


Any help would be much appreciated - note that I am (very) new to VB and am mostly using trial and error from stuff copied from the net!

 

[Arithos]

Hello all,

Can anyone advise how to reference a cell in VB so that the macro continues to work even if rows / columns are inserted in the worksheet at a later date.

I am putting together a form for other users to complete and using code to ensure that certain rules are followed for defaults and fields for completion. However, just using the cell ref (C4) or ($C$4) seems to fail if I then insert a row above row 4.

For ref. an example from the code is:



If Not Application.Intersect(Target, Range("C4")) Is Nothing Then
Application.EnableEvents = False
If Range("C4") = "Capped Usage" Then
Rows("5:5").EntireRow.Hidden = False
Else
Rows("5:5").EntireRow.Hidden = True
Range("C17") = ""
End If
Application.EnableEvents = True
End If


Any help would be much appreciated - note that I am (very) new to VB and am mostly using trial and error from stuff copied from the net!

You are adding rows above row 4, and still want C4 to be the cell with the variable? Or will it be moved down?

What you can do is name the cell and row in question, and not use cell references such as "C4" but rather change it to a specific name of your choosing.

 

[Arithos]

Meaning you should make your code like this, and change the cells in question, and ROW to the names you want, I used fairly obvious ones.

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Target, Range("[COLOR=#ff0000]CellC4[/COLOR]")) Is Nothing Then
Application.EnableEvents = False
If Range("[COLOR=#ff0000]CellC4[/COLOR]") = "Capped Usage" Then
Range("[COLOR=#ff0000]Rownr5[/COLOR]").EntireRow.Hidden = False
Else
Range("[COLOR=#ff0000]Rownr5[/COLOR]").EntireRow.Hidden = True
Range("[COLOR=#ff0000]CellC17[/COLOR]") = ""
End If
Application.EnableEvents = True
End If


End Sub

You name them like this in the initial setup, if you add a row, the "Rownr5", will be row 6, but is still named "Rownr5" and the macro will still work.

If you wonder how to change the names of cells/rows, look at this: How do I create a named cell in Microsoft Excel?

 

[dampfleece]

Thank you,

This worked a treat. Took me a while to realise that I had to change Row to Range to cope with the cell group on the hidden command but after that it is just what I needed.

Thanks again!


Simon

 

[Arithos]

Thank you,

This worked a treat. Took me a while to realise that I had to change Row to Range to cope with the cell group on the hidden command but after that it is just what I needed.

Thanks again!


Simon

Great, and you're welcome