VBA - Get Text from "Name Box" without subclassing

L

Legacy 98055

Guest
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
So, is this something like this, what you're after ?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Selection.Rows.Count & "R X " & Selection.Columns.Count & "C"
End Sub

Edit:

"......but would like to display the selection as it is being selected (While the mouse is being held down."

Sorry, missed that part....
 
Upvote 0
Bump.

Thanks Raider. Yea... I can get the selection after mouseup. I guess I may need to settle for that but would really like to do the following if possible.

Ok. I'm even willing to subclass. I cannot find out what the class of this control is.

Maybe somebody has an alternative?

I am looking to create "real time" selection address updating in my app.

Fo example, App.Inputbox with option 8

Any other ideas. I cannot find the namebox control that is created when cells are selected. I did find the Class: "Combobox" and Class: "edit" but these do not actually display the text. Could this be a windowless control?

I can't subclass the intput box. It is worksheet modal. Maybe the function wizard?

Thanks for any ideas...
 
Upvote 0
Hi Ivan. I used several different Spy++ type apps and could not locate this control that displays the text (where the Name Box is).. Any ideas?

Thanks,

Tom
 
Upvote 0
Tom,

Stumbled on this thread while searching for something else... Maybe you already know of this thread? It's the only code I've ever seen that hits the control you're looking at - at least when it's in its "passive state". I don't know enough to say if this will help get a handle on it when you're actually selecting cells.

This is so old, maybe it's no longer of interest, but thought I'd at least post the link to Chip's code on the off chance it's of any use.
 
Upvote 0
Thanks Greg.

That code goes as deep as the combobox class. Every combobox class has a child edit control which is what you would think would contain the text I was looking for but apparently does not. I can't get the text out of it for some reason. Since Excel uses a lot of windowless controls, I can only guess that the text is being directly rendered as opposed to being sent to a windowed control by way of a message. I ran a Spy type program and could not locate any messages corresponding to the namebox being updated when a range was being selected. The project in question is completed but I am still curious if there are any takers.

Thanks for the input.

Tom
 
Upvote 0
Hi Tom,

I couldn't find the window message that is sent by the system when the user drags the Mouse while holding the Mouse left Button down to select a Range therefore I couldn't use Subclassing or Hooking which would have been cleaner.

However, using a Timer with a combination of other APIs does give some good results !

Here is a workbook Demo to download :http://www.savefile.com/files/9184592


Place this Code in a Standard Module and run the DisplayCellsAddressInNameBox Procedure :

Code:
Option Explicit

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function DrawText Lib "user32" Alias "DrawTextA" _
(ByVal hdc As Long, ByVal lpStr As String, ByVal nCount As Long, _
lpRect As RECT, ByVal wFormat As Long) As Long

Declare Function GetClientRect Lib "user32" _
(ByVal hwnd As Long, lpRect As RECT) As Long

Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long

Private Declare Function ReleaseDC Lib "user32" ( _
  ByVal hwnd As Long, ByVal hdc As Long) As Long

Declare Function SetBkColor Lib "gdi32" _
(ByVal hdc As Long, ByVal crColor As Long) As Long

Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Declare Function KillTimer Lib "user32" _
(ByVal hwnd As Long, ByVal nIDEvent As Long) As Long

Const DT_CENTER = &H1

Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Dim lngTimerID, lngDC, lngXLhwnd, lngLeftFrmBarhwnd, _
lngNameBoxhwnd, lngEditBoxhwnd As Long
Dim udtEditBoxMetrics As RECT
Dim strSelectionAddress As String

Sub DisplayCellsAddressInNameBox()

    '\get the NameBox edit control handle and store it in a global variable
    lngXLhwnd = FindWindow("XLMAIN", vbNullString)
    lngLeftFrmBarhwnd = FindWindowEx(lngXLhwnd, 0, "EXCEL;", vbNullString)
    lngNameBoxhwnd = FindWindowEx(lngLeftFrmBarhwnd, 0, "ComboBox", vbNullString)
    lngEditBoxhwnd = FindWindowEx(lngNameBoxhwnd, 0, "Edit", vbNullString)
    '\get its rectangle
    GetClientRect lngEditBoxhwnd, udtEditBoxMetrics
    '\get the DC of the EditBox and store it in a global variable
    lngDC = GetDC(lngEditBoxhwnd)
    '\start a timer to watch for cells selections
    lngTimerID = SetTimer(0, 0, 1, AddressOf TimerCallBack)

End Sub


Sub StopDisplayingAddress()

    '\restore XL default behaviour & release te DC
    KillTimer 0, lngTimerID
    ReleaseDC 0, lngDC

End Sub


Sub TimerCallBack()

    '\get the selected cells address and padd it to cover all the edit box
    strSelectionAddress = Space(10) & Selection.Address(False, False) & Space(10)
    '\check if more than one cell is selected
    If Selection.Cells.Count > 1 Then
        '\if so change the NameBox backcolor to draw the user's attention
        SetBkColor lngDC, vbYellow
        '\finally,center the selection address in the NameBox
        DrawText lngDC, strSelectionAddress, Len(strSelectionAddress), _
        udtEditBoxMetrics, DT_CENTER
    Else
        '\if on e cell selected, restore default NameBox color
        SetBkColor lngDC, vbWhite
    End If

End Sub

Remember to kill the Timer when you are done by running the StopDisplayingAddress Procedure !

Regards.
 
Upvote 0
Jafaar, that's a very interesting approach. I think Tom was looking to fetch the contents of that box, not alter them, but using a timer to capture seletion may be an option for Tom. Tom, I got so keyed in on your discussing the NameBox-based solution that I forgot your actual problem, which was to show the address while selecting cells. That's actually the default behavior for RefEdit controls. I hate the things because I find them unstable as heck and when I try to class them WithEvents to intercept user interaction with them, bad things have happened to me (apps getting hung and have to kill Excel in task manager). Nonetheless, perhaps RefEdits are an idea?
 
Upvote 0
Thanks Jaafar.
I suppose that I could have created a timer to simply intercept the current selection. That's basically what I was after. Your very nice code is sending info to the namebox while I was actually trying to retrieve it. Your workbook works great!

Thanks also Greg.
What are "RefEdits" controls?

Thanks for the input... But I really need the output. :biggrin: Attempting HUMOR

Tom
 
Upvote 0
Tom,

How can you know about all this advanced WinAPI stuff and not know about RefEdits? :unsure: (Seriously, you could run rings around me programming WinAPI stuff.)

RefEdits are the lil' fellers that you see in any dialog boxes with the little ws icon and the red arrow and you can click the right-hand button and the form collapses while you select the cells. Like the Refers To section on the Define Name dialog. On the RefEdits that I add to my on UF's, I don't get the pretty little red arrow on a ws icon; I only get a little black bar. But the functionality is the same as them pretty built-ins' refedits.

Just drop a UF in a blank WB and r-click the toolbox pallet and pick the additional controls... option on the popup and scan down to RefEdit.Ctrl and add it to your toolbox. Then plop one on the UF and F5 and test 'er out.
 
Upvote 0

Forum statistics

Threads
1,216,165
Messages
6,129,235
Members
449,496
Latest member
Patupaiarehe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top