VBA - Get Text from "Name Box" without subclassing

[Legacy 98055]

To understand what I am after, please simply select some cells and watch the RC formatted range updated within the Name Box. My application involves selecting ranges. I can get the selection after mouse up, but would like to display the selection as it is being selected (While the mouse is being held down).

Thanks,

Tom

 

[Legacy 98055]

Hi Greg.

I just did not know. What's worse is, it appears to be selected in my toolbox by default. It was already selected and I have never used it. I placed one of these babies on a modeless form and was forced to perform a three finger solute to kill Excel. However, I think it might work with a bit of creative finagling. I'll post back for the one other person in the world who needs this solution.

Thanks,

Tom

 

[Greg Truby]

...was forced to perform a three finger solute to...

LOL. Like I said, they're unstable little buggers. But sometimes the rascals are the only solution. Doesn't mean I like 'em much.

(edited to remove slightly vulgar remark since this thread seems to have gotten serious again)

 

[Jaafar Tribak]

Hi Tom,

Where do you want the NameBox text displayed ?

I may have soomething that works without a Timer that gets the text !

Regards,

 

[Legacy 98055]

Hi Jaafar.

I needed a real-time selection address indicator that is not modal. The RefEdit control is ideal but is modal. Don't spend too much time on this. The project has been satisfactorily completed without this functionality.

I do need help on another problem that involves some API stuff that you seem to love. I'll work out a post.

Thanks as always.

Tom

 

[tusharm]

The RefEdit control hasn't been updated in eons. Consequently, it doesn't work except when in the 'top layer' of a userform shown modal. As you've discovered, it doesn't work if the uf is shown modeless. It also doesn't work in a COM Add-in, nor does it work if embedded in a control in a uf. Other than that it is quite powerful. A workaround that I originally developed for use with a COM Add-in is demonstrated in
Interactive graph analyzer
http://www.tushar-mehta.com/excel/software/utilities/iga.html

Hi Greg.

I just did not know. What's worse is, it appears to be selected in my toolbox by default. It was already selected and I have never used it. I placed one of these babies on a modeless form and was forced to perform a three finger solute to kill Excel. However, I think it might work with a bit of creative finagling. I'll post back for the one other person in the world who needs this solution.

Thanks,

Tom

 

[Norie]

What exactly do you want to do?

 

[Greg Truby]

Tushar, are you then using a RefEdit control in a second userform? Or am I misinterpreting what I'm seeing there?

And can you clarify what you mean by "top layer" of a userform? I have an application where the main UF uses a multipage control and I have RefEdits on multiple pages. Now you have me wondering if that's part of what's causing my stability problems.

 

[tusharm]

No, it's just a text box. The user has to click the OK button at which time the address of the selection is shown in the textbox. Even though this requires one additional click on the user's part, I prefer it to the alternative -- going deep into XL/VBA/OS and figuring out how to muck around with a super RefEdit control.

Tushar, are you then using a RefEdit control in a second userform? Or am I misinterpreting what I'm seeing there?

The reason I used the term "top layer" was just that it has been a long time since I extensively tested RefEdits and discovered their limitations. IIRC, putting on in a multipage control does cause problems but cannot be certain now. Having learnt the hard way I tend to put RefEdits directly in a userform or at the most in a frame.

I find it absolutely surprising how little attention this control has received. How do developers create userforms in which people provide information if they cannot point to worksheet range(s)? Is there another control that provides the same capability but one that I don't know about?

And can you clarify what you mean by "top layer" of a userform? I have an application where the main UF uses a multipage control and I have RefEdits on multiple pages. Now you have me wondering if that's part of what's causing my stability problems.

 

[Greg Truby]

As I mentioned in passing to Tom, I have tried to use a class module to trap events on the RefEdits -- basically testing to see if a user types a space or a grave accent and if so, autofill some addresses based on addresses in other RefEdits. I had it working perfectly in XL2002. But now, I don't know if its because we changed to XL2003 or if I made some change in the application (I document my major changes and I don't show anything that I think would cause them to fall apart, but who knows); but now the danged things are causing headaches again.

So anyway thank you for that tip (twice in two days that I've said that to you ). When I get a chance, I'll have to play with that idea and see if I like it better.

 

[Jaafar Tribak]

Digging up this old thread because I have come up against this problem myself .

I need to dynamically retrieve the addresses of the Ranges being selected as they are being selected (ie before releasing the mouse) and store each range address in an array for later use.

The main problem here is that during the process of selecting cells , excel enters a modal state and no code can run at the same time.

This is challenging and I would love to see if this can somehow be solved.

Thanks.