Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inputbox data range

    Hi all,
    Please tell me, is it possible to program an inputbox(es)where you can enter a data range (from - to). ie: name = Aaaaa through Bzzzzz.

    Thanks in advance
    Rene

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

    Re: Inputbox data range

    You can do it on a VBA UserForm by use of the RefEdit control. This enables the user to select a range by pointing and clicking, much like the function wizard etc.

    Hope that can help.

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox data range

    Hi Andrew,
    Thanks for that, but is it not possible for a user to type a range on a user form if they work with 1000s of records? I think it will be a hassle to point and click on a very large database.

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

    Re: Inputbox data range

    You don't have to point and click, sorry. Of course you can just type in the range if that is what you want.

    Andrew

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox data range

    Thanks for a solution yet again.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox data range

    Just want to add that there is also the Excel inputbox, which can be used very easy as a method of an object:

    object.inputbox(prompt, title, default, left, top, helpfile, context, type)

    e.g. MyValue = Application.InputBox(Prompt:=Message, Title:=Title, Type:=1)

    Interesting here is the Type property, which allows you to select what type of data you want the user to enter. Type:=0 only allows the user to enter a formula, 1 = numeric data, 2 = text, 4 = boolean, 8 = range, 16 = an error value like #N/A and 64 = array. You can also allow multiple data types by combining codes; e.g. 1+2 is a numeric value or text

    EXAMPLE:
    Sub test()
    Dim ref As Range
    Set ref = Application.InputBox(prompt:="Enter the range address", Title:="Excel's Inputbox", Type:=8)
    MsgBox ref.Address
    End Sub

    You can either select the range or enter the range address.

    HP

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox data range

    HANS,
    Thanks a lot, this is very interesting.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox data range

    There is a example in <A target="_blank" HREF=http://www.j-walk.com/ss/excel/tips/index.htm>http://www.j-walk.com/ss/excel/tips/index.htm</A> which allows to select a range with the mouse/cursors from VBA. Sorry not being able to pinpoint it but I can not access it from here, the timeout is very short for this proxy, but the link is worth a bookmark anyway.

Posting Permissions

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