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