Verify Column Letter Selected
 

I have a inputbox that prompts the user to enter a column letter so that a
script can run on all data in that column. The problem I have is that user
some times fat fingers this and enters things like "A3" or "A3E" Is there
anyway to check to make sure what the user inputs is a valid column letter?

Verify Column Letter Selected
 

Don't use inputbox, use application.inputbox

dim myRng as range
set myrng = nothing
on error resume next
set myrng _
= application.inputbox(Prompt:="select a single cell", type:=8).cells(1)
on error goto 0

if myrng is nothing then
'user hit cancel, what should happen?
else
set myrng = myrng.entirecolumn
or maybe...
set myrng = intersect(myrng.parent.usedrange, myrng.entirecolumn)
'do something
end if



jutlaux wrote:

I have a inputbox that prompts the user to enter a column letter so that a
script can run on all data in that column. The problem I have is that user
some times fat fingers this and enters things like "A3" or "A3E" Is there
anyway to check to make sure what the user inputs is a valid column letter?

--

Dave Peterson

Verify Column Letter Selected
 

Either use Application.Inputbox Type:=8 so that the user can select the range.

OR

Get the column as numbers and refer it as
Cells(row,column)

OR

If you are looking at last filled column use the below which will return
the last used column in row 1

lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

--
If this post helps click Yes
---------------
Jacob Skaria


"jutlaux" wrote:

I have a inputbox that prompts the user to enter a column letter so that a
script can run on all data in that column. The problem I have is that user
some times fat fingers this and enters things like "A3" or "A3E" Is there
anyway to check to make sure what the user inputs is a valid column letter?

Verify Column Letter Selected
 

If you are looking at last filled column use the below which will return
the last used column in row 1

lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Or use this code which will return the last filled in column no matter what
row it occurs in...

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Either use Application.Inputbox Type:=8 so that the user can select the
range.

OR

Get the column as numbers and refer it as
Cells(row,column)

OR

If you are looking at last filled column use the below which will return
the last used column in row 1

lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

--
If this post helps click Yes
---------------
Jacob Skaria


"jutlaux" wrote:

I have a inputbox that prompts the user to enter a column letter so that
a
script can run on all data in that column. The problem I have is that
user
some times fat fingers this and enters things like "A3" or "A3E" Is there
anyway to check to make sure what the user inputs is a valid column
letter?