Convert column number to the column letter for message box

Dallie

New Member
Joined
Apr 12, 2018
Messages
13
Hi,

I have message box that I am trying to convert from a column number to column letter to make the code more user friendly as I was performing this on Column AC which is column 29 (much more confusing)

How would I go about converting the column number to column letter for this code?

Code:
c = Selection.column
If MsgBox("Check column " & c & "?", vbOKCancel) = vbOK Then

Thanks!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Code:
Function ColLtr(ByVal iCol As Long) As String
  ' shg 2012
  ' Good for any positive Long
  If iCol > 0 Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
End Function

E.g.

Code:
If MsgBox("Check column " & ColLtr(c) & "?", vbOKCancel) = vbOK Then
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Here is one way:
Code:
Dim addr As String
Dim c As String

addr = Selection.Address(0, 0)
c = Left(addr, Len(addr) - Len(Selection.Row))
MsgBox "Check column " & c
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
Here's a UDF - Paste into a Standard Module.

Select a Cell in your worksheet, say M1; then run the macro Foo...

Code:
Sub Foo()
c = ColLetter(Selection.Column)
MsgBox "Check column " & c & "?"
End Sub


Function ColLetter(ColNumber As Integer) As String
    ColLetter = Left(Cells(1, ColNumber).Address(False, False), _
        1 - (ColNumber > 26))
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,373
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top