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

Wednesday, October 21, 2009

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