none
BackStyle problem when adding ActiveX ComboBox using VBA (Excel) RRS feed

  • Question

  • When I add an ActiveX combobox to an Excel sheet (not a form) using the code below, the object has BackStyle as fmBackStyleOpaque. When I use the Properties window to change to fmBackStyleTransparent, nothing changes. 

    If I add the combobox manually, the BackStyle can be changed. 

    I have a lot of comboboxes to add so I'd like to be able to control BackStyle programmatically.


    Tim Bostwick

    Saturday, November 16, 2019 3:14 AM

All replies

  • "using the code below" ????

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2019 9:57 AM
  • Dim ole As OLEObject
    Set ole = ws.OLEObjects.Add("Forms.ComboBox.1")
    With ole
          .name = "Level20"
          .Left = rng.Left
          .Top = rng.Top
          .Width = rng.Width
          .Height = rng.Height
          .listFillRange = "'Agents-Agencies'!A2:A190"
          With .Object
                ole.Object.BackStyle = 0 'fmBackStyleTransparent
          End With
    End With
    


    Tim Bostwick

    Saturday, November 16, 2019 3:39 PM
  • Apparently, Excel sets another property when you make an ActiveX control transparent interactively. The following does this in the code:

        Dim ole As OLEObject
        Set ole = ws.OLEObjects.Add("Forms.ComboBox.1")
        With ole
            .Name = "Level20"
            .Left = rng.Left
            .Top = rng.Top
            .Width = rng.Width
            .Height = rng.Height
            .ListFillRange = "'Agents-Agencies'!A2:A190"
            With .Object
                .BackStyle = 0 'fmBackStyleTransparent
            End With
            ' *** NEW ***
            ws.Shapes(.Name).Fill.Transparency = 1
        End With
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2019 4:09 PM
  • It still creates an opaque BackStyle.

    Tim Bostwick

    Saturday, November 16, 2019 6:24 PM
  • The workaround is to add an extra step:

    Dim ole2 As OLEObject
    For Each ole2 In Sheet1.OLEObjects
          If ole2.name = "Level20" Then
                ole2.Object.BackStyle = 0
          End If
    Next ole2


    Tim Bostwick

    Saturday, November 16, 2019 8:15 PM
  • The code that I posted works for me...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2019 8:21 PM