-
Select column based on user input
-
Hello Wall31
Welcome to ExcelFox
I would use the standard VBA InputBox function for something very simple like you are asking for , rather than creating one,
This is the basic coding to get you started. You would normally want to possibly consider some extra lines to check that valid input is given by the user
( You can give the column as a Letter, such as your P , or a number. In both cases just give the character with no quotes, )
Alan
Code:
Sub SimpleInputBox() '
Rem 1 VBA input box function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function
Dim Anser As String
Let Anser = InputBox(Prompt:="Give Column number or column Letter", Title:="Select Column", Default:="A")
Rem 2 select column
ActiveSheet.Columns(Anser).Select
End Sub
-
That is exactly what I am after! Thanks so much!
-
You're welcome.
Just in passing interest I have a version here that demonstrates what I mentioned about extending the solution to check the data. The macro below makes some attempt at handling the case of the user giving bad data.
I have not checked it thoroughly yet, and might edit / correct it later.
I am just adding it for future reference, that’s all.
If the simpler solution does what you want then it is probably best to use that. Best is often to keep it as simple as possible….
Alan
Code:
Sub SimpleInputBox2() ' https://excelfox.com/forum/showthread.php/2560-Select-column-based-on-user-input?p=13744&viewfull=1#post13744
Rem 1 VBA input box function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function
Dim Anser As String
Let Anser = InputBox(Prompt:="Give Column number or column Letter", Title:="Select Column", Default:="A")
Let Anser = Trim(Anser) ' take away any spaces either side of the input character or characters
Rem 2 Some data checking
If StrPtr(Anser) = 0 Then Exit Sub ' the user clicked cancel https://excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox?p=8474&viewfull=1#post8474
If IsNumeric(Anser) Then ' this checks if it looks like a number
If Anser > ActiveSheet.Columns.Count Then MsgBox Prompt:="there aint that many columns in ya worksheet!": Exit Sub
Else ' we did not input just a number
If Len(Anser) > 3 And ActiveSheet.Columns.Count = 16384 Then MsgBox Prompt:="there aint that any columns with more than 3 characters in XL 2007 and higher": Exit Sub
If Len(Anser) > 2 And ActiveSheet.Columns.Count = 256 Then MsgBox Prompt:="there aint that any columns with more than 2 characters in XL 2003 and lower": Exit Sub
If Len(Anser) = 2 And (IsNumeric(Left(Anser, 1)) Or IsNumeric(Right(Anser, 1))) Then MsgBox Prompt:="you can't mix letters and numbers": Exit Sub
If Len(Anser) = 3 And (IsNumeric(Left(Anser, 1)) Or IsNumeric(Right(Anser, 1)) Or IsNumeric(Mid(Anser, 2, 1))) Then MsgBox Prompt:="you can't mix letters and numbers": Exit Sub
If ActiveSheet.Columns.Count = 256 And Len(Anser) = 2 And Not (UCase(Left(Anser, 1)) = "A" Or UCase(Left(Anser, 1)) = "B" Or UCase(Left(Anser, 1)) = "C" Or UCase(Left(Anser, 1)) = "D" Or UCase(Left(Anser, 1)) = "E" Or UCase(Left(Anser, 1)) = "F" Or UCase(Left(Anser, 1)) = "G" Or UCase(Left(Anser, 1)) = "H" Or UCase(Left(Anser, 1)) = "I") Then MsgBox Prompt:="First character must be A B C D E F G H or I": Exit Sub
If ActiveSheet.Columns.Count = 256 And Len(Anser) = 2 And (UCase(Right(Anser, 1)) = "W" Or UCase(Right(Anser, 1)) = "X" Or UCase(Right(Anser, 1)) = "Y" Or UCase(Right(Anser, 1)) = "Z") Then MsgBox Prompt:="you can't have second character above ""V"" ": Exit Sub ' For up to XL 2003 we can go up to column letterws IV
If ActiveSheet.Columns.Count = 16384 And Len(Anser) = 3 And (UCase(Left(Anser, 1)) = "Y" Or UCase(Left(Anser, 1)) = "Z") Then MsgBox Prompt:="First character must be not above X": Exit Sub ' For XL 2007 and higher we can go up to column XFD
If ActiveSheet.Columns.Count = 16384 And Len(Anser) = 3 And Not (UCase(Mid(Anser, 2, 1)) = "A" Or UCase(Mid(Anser, 2, 1)) = "B" Or UCase(Mid(Anser, 2, 1)) = "C" Or UCase(Mid(Anser, 2, 1)) = "D" Or UCase(Mid(Anser, 2, 1)) = "E" Or UCase(Mid(Anser, 2, 1)) = "F") Then MsgBox Prompt:="second character must be A B C D E or F ": Exit Sub
End If
Rem 3 select column
ActiveSheet.Columns(Anser).Select
End Sub
'Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
' Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
'End Function
-
Thank you so much for the effort you put into my query! I really appreciate! To be honest, I love your validation options because you never know how other people will actually use it!
Please stay safe and well!
