center combobox

[ZTK]

Hi


I request your valuable help

I have the following code:

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr

Set xWs = Application.ActiveSheet
On Error Resume Next
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right (xStr, Len (xStr) - 1)
If xStr = "" Then Exit Sub
Set xCombox = xWs.OLEObjects ("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
With xCombox
.Visible = False
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 3
.Height = Target.Height + 3
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split (xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown (ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset (0, 1) .Activate
Case 13
Application.ActiveCell.Offset (1, 0) .Activate
End Select
End Sub

It works excellent, the only detail is that the combobox listing appears randomly throughout the sheet.

Is there a way for the list to appear in the selected cell without modifying anything else in the code?

Thank you

 

[Anthony47]

Add these 2 lines to set the top & left of the contro:

    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Top = Target.Offset(0, 0).Top      'Top
        .Left = Target.Offset(0, 0).Left    'Left
    End With

The ".Offset(0,0)" portion is of course un-necessary to set the position just on top of the selected cell (and thus can be removed); it is inserted to show how setting the position somewhere else, for example Offset(0,1) wil set the control on the cell at the right of the selected one

Bye

 

[ZTK]

Add these 2 lines to set the top & left of the contro:

    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Top = Target.Offset(0, 0).Top      'Top
        .Left = Target.Offset(0, 0).Left    'Left
    End With

The ".Offset(0,0)" portion is of course un-necessary to set the position just on top of the selected cell (and thus can be removed); it is inserted to show how setting the position somewhere else, for example Offset(0,1) wil set the control on the cell at the right of the selected one

Bye

Thank you very much for the reply

 

[Anthony47]

Thank you very much for the reply

You are welcome. If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

Bye

 

[ZTK]

You are welcome. If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

Bye

I already had the opportunity to verify the modification, unfortunately the error continues.

I took the liberty of adding the link to the file for reference, additionally in the image I point out the error to which I am trying to find an answer.

Thank you again

ERROR.xlsm

BITA

docs.google.com

 

[Anthony47]

Ok, after downloading the file how do we replicate the problem?

 

[Anthony47]

I got a situation similar to what you show in your picture when, after the control get up in the correct position, you scroll the worksheet.
If this is what you mean then you have to wait for a WorksheetScroll event to be released by microsoft.
However @Jaafar Tribak developed something that is quite close to that, see A Worksheet OnScroll Event ! - How cool is that ?
See how you can adapt that to your situation. When the scroll events are detected you have to check wether the OLEObjects("TempCombo") is visible; if Yes then you have to read its LinkedCell property and reset top & left according the LinkedCell

Bye