Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Obtaining Cell Address from Input Box (Excel XP)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    the address is:

    Rinput.address

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

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

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

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    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,

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    John,

    Your routine works perfectly. Thanks so much.

    Regards,

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    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?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    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,

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    Ah. I return much wiser from the VBA forum, thanks once again Andrew for <post#=185657>post 185657</post#>. 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
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtaining Cell Address from Input Box (Excel XP)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Obtaining Cell Address from Input Box (Excel XP)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •