Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to verify selected data from drop-down list? | Excel Worksheet Functions | |||
How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) |