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. ^_^

Unhide hidden sheets in Excel

I am doing an Excel task using Macro. I hide most of the worksheets at the start of the Excel file, until user did some selections to show/hide certain worksheets.

Now, I am asked to allow the users to unhide the hidden worksheets in the Excel file. I have searched around in the Excel file, but failed to get so. Then I realized, I have to change how I hide the worksheet in Macro codes.

This is how I used to hide worksheet:

ThisWorkbook.Worksheets(sheetIndex).Visible = xlSheetVeryHidden

Just change xlSheetVeryHidden to xlSheetHidden will do.

Using xlSheetVeryHidden can only unhide certain worksheets in Macro.

To unhide hidden worksheets:

1. Go to Format menu.

2. Click on Sheet, then Unhide.

3. You’ll get a list of hidden worksheets, just select the worksheet name and click on OK button to unhide it.

Convert Column Number to Letter and Vice Versa in Excel

Sometimes when I built the application for Excel, I’ll need to convert the Column number to Letter (say, 3 is C) or vice versa.

I’ve been searching around, and finally found the following methods which are quite useful and simple. They cater for column letter like “AB” too.

Function: Convert column number to letter ( 2 –> “B”)

   1: '------------------------------------------------------------------------------------ 

   2: '   Description:    To convert the column number to letter(e.g. 2 --> "B") 

   3: '   Parameters:     The column number (Long) 

   4: '   Return:         The column letter (String) 

   5: '------------------------------------------------------------------------------------ 

   6: Private Function m_ConvertColumnNumberToLetter(ByVal ColumnNumber As Long) As String 

   7:  

   8:     Dim s0 As String, s1 As String, S2 As String, s3 As String 

   9:     If ColumnNumber > 18278 Then s0 = Chr((Int((ColumnNumber - 18279) / 17576) Mod 26) + 65) 

  10:     If ColumnNumber > 702 Then s1 = Chr((Int((ColumnNumber - 703) / 676) Mod 26) + 65) 

  11:     If ColumnNumber > 26 Then S2 = Chr(m_ConvertColumnNumberToLetter & (Int((ColumnNumber - 27) / 26)) Mod 26 + 65) 

  12:     

  13:     s3 = Chr(((ColumnNumber - 1) Mod 26) + 65) 

  14:     m_ConvertColumnNumberToLetter = s0 & s1 & S2 & s3 

  15:  

  16: End Function 

Function: Convert column letter to number (“B” –> 2)

   1: '------------------------------------------------------------------------------------ 

   2: '   Description:    To convert the column number to letter(e.g. "B" --> 2) 

   3: '   Parameters:     The column letter (String) 

   4: '   Return:         The column number (Long) 

   5: '------------------------------------------------------------------------------------ 

   6: Private Function m_ConvertColumnLetterToNumber(ByVal ColumnLetter As String) As Long 

   7:  

   8:     Dim intCount                As Integer 

   9:     Dim intColumnLetterLength   As Integer 

  10:     m_ConvertColumnLetterToNumber = 0: intColumnLetterLength = Len(ColumnLetter) 

  11:     For intCount = 1 To intColumnLetterLength 

  12:         m_ConvertColumnLetterToNumber = m_ConvertColumnLetterToNumber * 26 + (Asc(UCase(Mid(ColumnLetter, intCount, 1))) - 64) 

  13:     Next intCount 

  14:  

  15: End Function