Range Selection ((VBA 97/2000))

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

ESP is one of the qualifications for Moderators and WMVPs, Howard!

Reply | Quote

Awckie posted

.

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

Andrew C

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Then it sounds like *your* ESP is the one in question, Howard. Most of us appear to have more respect for the intelligence of the posters.

Reply | Quote

Howard,

How can you call it “Extraordinarily Silly Presumptions”?

Andrew replied based on his knowledge of the various Office products, and based on that knowledge, assumed (correctly as it turned out) that it applied to Excel.

Please do not denigrate people who take the time and effort to provide help to others.

Reply | Quote

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

StuartR

Reply | Quote

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

Reply | Quote

Thank you Andrew…

I appreciate your response (and in the future will remember to put XL in my subject line

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

Thank you!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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….

Reply | Quote