Obtaining Cell Address from Input Box (Excel XP)

Topic

New Reply

Please forgive me if I am asking a question that has been posted before; I did a search and couldn’t find anything that directly related to my issue.

Basically what I am trying to do is to capture a specific address from an Excel Input Box (as opposed to the VBA type). Here is the code I am using:

Set RInput = Application.InputBox _
(Prompt:=”Enter the Row Input Cell:”, _
Title:=”Row Input Cell”, _
Type:=8)

What I want RInput to return is the cell address the user either types or clicks (it would be a single cell). Instead it is returning the contents of the cell.

What am I doing wrong?

Reply | Quote

Replies

the address is:

Rinput.address

Note: It is not JUST a single cell. They could highlight a RANGE of cells.

Steve

Reply | Quote

Thank you very much. I just could not see it.

Reply | Quote

Steve,

Your suggestion didn’t work. I get “Assignment to Constant nor Permitted” errors. When I comment out the Dim (I had dimmed RInput as Range), that error goes away, but then I get a run time error that says “Object required”.

Any ideas?

Thanks,

Reply | Quote

range.Address is a string, so:

Dim rInput As Range
Dim sAddress As String
Set rInput = Application.InputBox _
(Prompt:=”Enter the Row Input Cell:”, _
Title:=”Row Input Cell”, Type:=8)
sAddress = rInput.Address
MsgBox sAddress

you’ll have to discard the part of the string after the “:” to get the first selected cell or row or column number if a multicell range is selected.

Reply | Quote

John,

Your routine works perfectly. Thanks so much.

Regards,

Reply | Quote

No problem, and here’s the line to trim to the first cell in the range:

If InStr(2, sAddress, “:”) Then sAddress = Left(sAddress, InStr(2, sAddress, “:”) – 1)

(I was trying to use

If InStr(2, sAddress, “:”) Then sAddress = Split(sAddress, “:” , 1,vbTextCompare)

but I get type mismatch, can someone tell ME what is wrong with the latter line?)

Reply | Quote

I’ll try your suggestion tomorrow as I have to run now. I thank you again for your help and your insights.

I’ll let you know how it all comes out.

Regards,

Reply | Quote

Ah. I return much wiser from the VBA forum, thanks once again Andrew for post 185657. Split takes a string but returns an array of strings, so it’s necessary to specify that only the first element is returned:

sAddress = Split(sAddress, “:”)(0) ‘is all that’s needed

Reply | Quote

Not to ask a stupid question, but why do you want the address when you already have the range? It would probably be better to use the range in whatever subsequent code that you write. –Sam

Reply | Quote

Or use rInput.Cells(1,1).Address

Gives you the first cell of the first area a user has selected, whereas:

rInput.Areas(2).Cells(2,1) gives the cell on the second row of the first column of the second area the user selected.

Reply | Quote