InputBox that Let user Choose 2 Columns Separately

[austinandrei]

Hi,
Is there a function in inputbox that would let user choose 2 columns separately for comparative analysis? I can have 2 inputbox method that will pop up simultaneously but I want it just one inputbox that will prompt and can be editable until user will click on OK button.

Or it can be a userform that has the textbox working like an inputbox. But I don't know how to make the textbox control in userform into an inputbox without prompting another inputbox.
Thanks!

 

[austinandrei]

Basically, I want a userform to work like an InputBox Type 8

Application.InputBox(prompt := "Sample", type := 8)

Because if this is possible, then I know I can create to InputBox function in one userform. Or if there is an existing method in Application.InputBox that can have 2 boxes to choose separate columns or cells.

I want the user to choose or select the column. not manually type or input the column.

 

[austinandrei]

Ok, I thought of an alternative which is using a command button in Userform. so when I click on the commandbutton,it will trigger an inputbox type:8 function but it won't appear, it will just let the user choose a cell or a column and at the same time, the userform itself will be invisible until the user chooses a column or cell. But I still don't know how to do it, please help.

 

[Jaafar Tribak]

Unless I a m misunderstanding something, can you not select multiple ranges with the Application.Inputbox Method by holding the Ctl key down while performing the mouse selections ?

 

[austinandrei]

hi jaafar, the reason of separate columns is comparative analysis.so it would be the first column divided by the second column chosen. then after that, the indirect formula will be triggered by the columns chosen.
I just used a vbmodeless userform to as an alternative as I cannot see a way using just one input box.my inside codes on userform is below:

Private Sub CommandButton1_Click()
TextBox1 = Replace(Replace(ActiveCell.Address, "$" & ActiveCell.Row, ""), "$", "")
UserForm1.Show vbModeless
End Sub
Private Sub CommandButton2_Click()
TextBox2 = Replace(Replace(ActiveCell.Address, "$" & ActiveCell.Row, ""), "$", "")
UserForm1.Show vbModeless
End Sub
Private Sub CommandButton3_Click()
[C1] = TextBox1
[C2] = TextBox2
Unload Me
End Sub
Private Sub CommandButton4_Click()
End
End Sub

I used the replace function to get the Column letter of the activecell whether a column or a single cell is chosen which is to be used on below formula:
=IFERROR((INDIRECT(C$1&ROW($B5))/INDIRECT(C$2&ROW($B5)))-1,0)
This works for me as an alternative.