Friday, October 23, 2009

How to check the target’s range name?

I am working on the Excel file, for show and hide certain ranges based on the selection. Say if user selects “Yes”, then I show certain range. Otherwise, I’ll hide that range.

I do the coding in the Worksheet_Change event. I check if the target is the relevant cell/range, then only I perform the function accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)


    ' Option 1 Selected

    If Target.Address = "$I$21" Then SelectOption1


End Sub

But, this is hardcoded, whenever the cell address has been changed, say insert a new row or remove a row, then I21 is no longer the cell I’m referring.

I just found a way to solve this “hardcode” problem – Define range name. But how can I check if the range name is my defined one? As I’ve tried Target.Address and Target.Name, both return me the cell address.

Finally, I’ve found this:

If ThisWorkbook.Worksheets("SheetName").Range(Target.Address).Name.Name = "DefinedRangeName" Then SelectOption1

Range(Target.Address).Name.Name will return the defined range name. By using this, I do not need to hardcode the cell address anymore, and it saves lots of maintenance work in future. ^_^

No comments:

Post a Comment