PDA

View Full Version : Get Column letter in a range help



Designer6
02-20-2008, 09:23 AM
Hi Guys,

I have a range of column starting from P to AV. These columns first hide then unhide base on the checkbox selection. Below is the code that find the checkbox caption then unhide the column that associates with it. What i would like to do next is to get the column letter like (P, Q, V, X etc..) that associates with each caption. I tried to use activecell.column, but it doesn't work. I also tried after: = activecell that didn't work also could anyone help.



Private Sub FindAndUnhide(ByRef cb As msforms.CheckBox, ByRef rng As Range)
Dim cell As Range

' Begin your Selection
If cb.Value = True Then
Set cell = rng.Find(What:=cb.Caption, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not cell Is Nothing Then

'Want to unhide a particular column base on
cell.EntireColumn.Hidden = False
End If
End If
End Sub

Bob Phillips
02-20-2008, 10:40 AM
This function returns the column letter based upon the column number



'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function