Results 1 to 6 of 6
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    full address from InputBox (Excel 2000, SP2)

    I'm desparate <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> I am using the InputBox function to return a selected range. I need to get the "full" address not just the cell. I find that it only returns the cell's address even if the selected cell is on a different sheet or in a different workbook.

    <pre>Set rng = Application.InputBox("Locate the cell for this variable's result.", _
    "[" & varName & "] Results Cell", "NONE", Type:=8)
    </pre>

    This works fine but 'rng' always contains something like $C$5 whereas I need the full address (workbook + sheet + cell). If the selection was made on Sheet5 then it should return "Sheet5!$C$5" and if it was in another workbook it would be "[Book2]Sheet5!$C$5".

    My application needs to know exactly what cell the user pointed to. How can I do this with the returned 'rng'? I've looked thru help and tried using rng.parent.name but that doesn't completely solve the problem since it returns the worksheet not the workbook too.

    Thnx, Deb <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: full address from InputBox (Excel 2000, SP2)

    I ended up solving it by using Range.Parent.Name after all. It's a two step process to make sure that I get the workbook name if the chosen cell is not in the current workbook.

    <pre>Private Function FullAddress(rng As Range) As String


    If ThisWorkbook.Name <> rng.Parent.Parent.Name Then
    ' the selected cell is in a different workbook
    FullAddress = "[" & rng.Parent.Parent.Name & "]"
    End If

    FullAddress = FullAddress & rng.Parent.Name & "!" & rng.Address

    End Function
    </pre>

    This is called after set rng = Application.InputBox( blah blah blah, Type:=8). I had to add the special characters myself (the [ and ! ) to identify the workbook and sheet.

    Ah, what a relief! I still think there is an easier solution but this works for now.
    Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: full address from InputBox (Excel 2000, SP2)

    how about creating your own form for user input and add the refEdit control. This way the user could either manually type the range value or click the refEdit control button and use the mouse to drag a marque around their range selection. If the user uses the mouse to make the selection the full 'Path' of the range is returned to the textbox of this control.

    If you don't have this control in your toolbox, right click in the tool box and select "Additional controls..." look for the Microsoft RefEdit.dll and add it to your toolbox.

    Later,
    Bruce

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: full address from InputBox (Excel 2000, SP2)

    Hmm, yes I've heard about that control, never used it though. I did solve my problem by prepending the returned address with parent.parent.name as I said in my reply HOWEVER I will look at RefEdit again.

    Do you know if it's one of those controls that requires some .dll to be loaded (one that's not normally included with Excel)? I need to completely avoid any type of installation/setup procedure for my end users so I only use the basic controls included with Excel.

    Thanks for the idea,
    Deb <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: full address from InputBox (Excel 2000, SP2)

    Deb,

    The RefEdit control comes with Excel, and by default should be on the Controls Toolbar in the VBA environment - It is the last control on my system.

    I have used it, but would advise against using it in a Frame control, especially with XL97.

    Andrew C.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: full address from InputBox (Excel 2000, SP2)

    Not sure how you might incorporate this into your code, but in Excel 2000, you can use the following to return the full pathname, filename, sheetname, and cell address:

    <pre>=CELL("filename")&CELL("address")
    </pre>


Posting Permissions

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