Dynamic dependent combobox sizing issues 1

[ZenRaven]

I use the following sub to move a combobox around, size it, and modify the range associated with the control. This is called on the worksheet's selection_change event to move and align the control to the selected cell.

Public Sub MoveBox(Obj As OLEObject, aRange As Range)
  Dim c As Integer
  Dim wsFiltered As Worksheet
  Dim wsSelections As Worksheet
  Dim sSQL As String
  Dim SelAddr As String
  Dim SelValue As String
  
 
  IgnoreEvents = True
  
  Set wsFiltered = Worksheets("Filtered")
  Set wsSelections = Worksheets("Selections")
  
  c = aRange.Column
  
  If c = 6 Then
    sSQL = "select id, parentid, description from categories order by ID"
  ElseIf c > 6 Then
    SelAddr = aRange.Address
    SelValue = wsSelections.Range(SelAddr).Offset(0, -1).Value
    sSQL = "select id, parentid, description from categories where parentid = '" & SelValue & "' order by ID"
  End If

  If c > 5 Then
    With wsFiltered
      With .QueryTables("FilteredCats")
        .CommandText = sSQL
        .Refresh
      End With
      With .Columns(3)
        .AutoFit
        Obj.Object.ListWidth = .Width + 10
        aRange.ColumnWidth = .ColumnWidth + 3
      End With
    End With
  End If
  
  aRange.RowHeight = 15
  
  With Obj
    .Left = aRange.Left
    .Top = aRange.Top
    .Width = aRange.Width + 1
    .Height = aRange.Height + 1
    .LinkedCell = aRange.Address
    .Visible = True
  End With
  
  'aRange.Select
  

  IgnoreEvents = False
  
End Sub

I have 2 problems.

1. Sometimes when the control/cell widths are updated, the dropdown image and text are stretched/enlarged. I say sometimes, because it doesn't happen in every instance and I can't seem to find the pattern.
I can also get this to happen if I manually resize the column that the control is currently "attached to".

2. If I click between 2 cells that use this control, the line

         aRange.ColumnWidth = .ColumnWidth + 3

seems to be using the columnwidth of the previous cell/control source. If I set a breakpoint in this sub and step through it, it works fine and uses the correct columnwidth of the current cell/control source.

If none of this makes sense or you need further info, let me know

 

[PHV]

I'd try this:
.Refresh [!]False[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZenRaven]

Perfect, I overlooked that. Now that the control is getting the same widths, have you ever seen anything like #1?. Is that normal?