Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Range Selection ((VBA 97/2000))

    Hi Everyone,

    I have an inputbox where I get a range selection. I would instead like to use the dialog box where you select the range on the screen using the mouse.

    Can anyone lead me in the right direction?

    -Leslie

  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: Range Selection ((VBA 97/2000))

    You can include the RefEdit control on your Userform which will provide the functionality you are looking for. It is usually the last of the default controls on the VBA Forms ToolBox .

    I would advise not placing this control in a Frame with Excel 97. There is a bug which in certain circustances causes excel to freeze.

    Andrew C

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range Selection ((VBA 97/2000))

    The RefEdit control mentioned by Andrew is the most elegant solution.

    But for quick&dirty solutions I sometimes use the Application.InputBox function. It is an extended Excel version of the generic VBA InputBox. You can find information about it in Excel VBA help by looking up the Application object. InputBox is one of its methods.

    Application.InputBox(Prompt:="Type or select a range", Type:=8)

    will allow the user to enter a range using the keyboard or the mouse.

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Selection ((VBA 97/2000))

    Andrew C has ESP!
    How did he know the thread was about Excel?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Range Selection ((VBA 97/2000))

    ESP is one of the qualifications for Moderators and WMVPs, Howard! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

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

    Re: Range Selection ((VBA 97/2000))

    Awckie posted

    < I have an inputbox where I get a range selection >.

    What VBA application other than Excel has an InputBox that provides that functionality ?.

    Andrew C

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Selection ((VBA 97/2000))

    The typical reader of this forum doesn't know the differences among the various flavors of VBA in each app.

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Range Selection ((VBA 97/2000))

    I'm pretty sure you could do this in word with a Modeless userform

    StuartR

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

    Re: Range Selection ((VBA 97/2000))

    You are probably right, but not with an InputBox. Also Modeless UserForms are not availabe to VBA 97 (unless you are prepared to experiment with API calls)

    Andrew C

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Range Selection ((VBA 97/2000))

    That sounds a bit patronizing, Howard. Do you have any statistics to support the assumption?
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Range Selection ((VBA 97/2000))

    Hans...

    Thank you very much! I was actually looking for the quick & dirty solution, so this will work.

    Somtime in the future I will look into Andrew's solution!

    Thank you....

  12. #12
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Range Selection ((VBA 97/2000))

    Thank you Andrew...

    I appreciate your response (and in the future will remember to put XL in my subject line [img]/forums/images/smilies/smile.gif[/img]

    I will try your solution in the future, but this time I think I will use Hans!

    Thank you!

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Selection ((VBA 97/2000))

    Of course not.

    I based my judgement on ESP (Extraordinarily Silly Presumptions) and the sense I get from reading the postings in the Word/VBA forums.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Range Selection ((VBA 97/2000))

    Then it sounds like *your* ESP is the one in question, Howard. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Most of us appear to have more respect for the intelligence of the posters.
    Charlotte

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

    Re: Range Selection ((VBA 97/2000))

    Thank you very much acwckie.

    I merley tried, based on what I thought were 'presumptions' of your requirements, to provide assitance.

    Thnakfully I have not reached the stage of my existence where I would prefer to denigrate the efforts of others to provide assistance rather than provide some constructive help myself.

    It seems that there are some sad indeviduals that have a different outlook on life.

    Andrew

Page 1 of 2 12 LastLast

Posting Permissions

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