VBA Excel - Textbox disappearing BEHIND listview control - Zorder is useless.

[Radu]

Hi. I have the following problem: On a userform I need to show some
(editable) data in a grid. I am using a listview control.

Since for some unknown reason the HitTest method refuses to work when
in Report mode, giving me erorrs even if I have a single line in the
HitTest event: "debug.print X" !!!, on MouseUp I detect (with
SendMessage(lsvTruckingCharges.hWnd, LVM_SUBITEMHITTEST, 0&, hti)) the
subitem the user has clicked on. Now I need to position a textbox/a
combo box/a date-time picker in the cell's place, for editing,
depending on the column's type.

I have started testing with a texbox, with the code (snip):

With txtFloat
'Set its text:
.Text = lsvTruckingCharges.ListItems(lngRow +
1).ListSubItems(lngCol)
.SelStart = 0
.SelLength = Len(.Text)

'Set its dimensions:
.Width = lsvTruckingCharges.ColumnHeaders(lngCol + 1).Width
.Height = lsvTruckingCharges.ListItems(lngRow + 1).Height

'Position it:
.Left = lsvTruckingCharges.Left +
lsvTruckingCharges.ColumnHeaders(lngCol + 1).Left
.Top = lsvTruckingCharges.Top +
lsvTruckingCharges.ListItems(lngRow + 1).Top
End With

The problem is that the textbox DISAPPEARS behind the listview, and no
amount of Zorder will show it. What can I do ? Where is the problem
?????? I did something similar in VB (not VBA) and it works like a
charm. Do ai have any alternatives ?

Thanks a lot
Alex. Nitulescu

 

[Leith Ross]

Hello Alex,

Since you didn't say what error you encountered and didn't show th
code, I am guessing. The problem with the Hit Test API code maybe du
to not having declared the *LVHITTESTINFO* structure or an imprope
variable assignment to a variable in it. Here is the method I use. Thi
works for ListViews 5.0 and 6.0. The return value is a Variant Arra
that contains the Row (element 0) and the Column (element 1).

(PLACE THIS CODE IN A VBA MODULE

'List View Hit Test constants
Private Const LVHT_ONITEMICON As Long = &H2
Private Const LVHT_ONITEMLABEL As Long = &H4
Private Const LVHT_ONITEMSTATEICON As Long = &H8
Private Const LVHT_ONITEM As Long = (LVHT_ONITEMICON Or _
LVHT_ONITEMLABEL Or _
LVHT_ONITEMSTATEICON)

Private Type LVHITTESTINFO
Pt As POINTAPI
flags As Long
item As Long
subitem As Long
End Type

Public Function GetSubItemRowCol(LV As ListView, ByVal x As Single
ByVal y As Single) As Variant

Dim RetVal
Dim HitData(1)
Dim Hit As LVHITTESTINFO

With Hit
.Pt.x = x
.Pt.y = y
.flags = LVHT_ONITEM
End With

RetVal = SendMessage(LV.hWnd, LVM_SUBITEMHITTEST, 0&, Hit)
HitData(0) = Hit.item
HitData(1) = Hit.subitem

GetSubItemRowCol = HitData

End Function

( THIS CODE IS FOR THE MOUSEUP EVENT

Private Sub ListView1_MouseUp(ByVal Button As Integer, ByVal Shift A
Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y A
stdole.OLE_YPOS_PIXELS)

Dim Col
Dim Row
Dim RetVal

RetVal = GetSubItemRowCol(ListView1, x, y)
Row = RetVal(0)
Col = RetVal(1)

End Sub

If you have any problems, contact me vai email at (e-mail address removed).

Sincerely,
Leith Ros

 

[Radu]

Thank you, Leith, for your answer.

No, the problem is NOT in the specified API code, that works fine, the
problem is in the fact that I can't put the (editable) text box ON TOP
OF THE listview. On click, I position and dimension it correctly (I
verified that), only it cannot go in front of the listview, it always
goes behind it, therefore being invisible. It looks like for some
reason the listview is always on top of any other control (window).
However, a similar code in VB, with VB's controls, works just fine - in
VBA, however....

Thank you
Alex.

 

[DM Unseen]

Alex,

got the same issue as you. There is no way you can force the Listview
to respect Ordering on the MSForms Form.
It seems most ActiveX controls except the standard MSForms controls,
behave like this on a MSForms Form.
I have not found any workaround for this except creating a COM addin
with VB, and only use VB forms.

Dm Unseen