Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Excel VBA ComboBox ".ShowDropButtonWhen = fmShowDropButtonWhenFocus" Error 438

    I have a VBA procedure (see below) for inserting a number of ComboBoxes in a worksheet that produces the following error at the two lines shown immediately below the error dialogue:


    The full procedure code is:

    Code:
    Sub Insert_ComboBoxes()
     
    '   Inserts ComboBoxes by row and column
    '   Trevor R Bird
    '   15 Nov 2016
     
    Dim dblHeight As Double
    Dim dblWidth As Double
    Dim lngNumCboBoxes As Long
    Dim lngCboBox As Long
     
    Dim lngRow As Long
    Dim lngColumn As Long
    Dim lngFirstRow As Long
    Dim lngFirstColumn As Long
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
     
    Dim ws As Worksheet
    Dim ctlCombo As OLEObject
     
    Dim Msg, Button, Title As String
    Dim Response As Boolean
     
    Button = vbExclamation
    Title = "Sub Insert_ComboBox()..."
    '    Msg = ""
    '    Response = MsgBox(Msg, Button, Title)
     
    Set ws = Worksheets("Instructions & Analysis")
     
    '   Set row and columns limits for Aspect ComboBoxes
    lngFirstRow = 20
    lngFirstColumn = 2
    lngLastRow = 23
    lngLastColumn = 4
     
    '   Set number of ComboBoxes
    lngNumCboBoxes = 5
     
    '   Set up ComboBoxes
    For lngRow = lngFirstRow To lngLastRow Step 1
        dblHeight = ws.Rows(lngRow).Height
        For lngColumn = lngFirstColumn To lngLastColumn Step 1
            dblWidth = ws.Columns(lngColumn).Width
                Set ctlCombo = ws.OLEObjects.Add _
                            (ClassType:="Forms.ComboBox.1", _
                            Link:=False, _
                            DisplayAsIcon:=False)
                With ctlCombo
                    .Name = "cboAspect_R" & lngRow & "C" & lngColumn
                    .Left = ws.Cells(lngRow, lngColumn).Left
                    .Top = ws.Cells(lngRow, lngColumn).Top
                    .Width = ws.Columns(lngColumn).Width
                    .Height = ws.Rows(lngRow).Height
                    .LinkedCell = ws.Cells(lngRow, lngColumn).Address
                    .ListFillRange = "Parameters!Aspectlist"
     
                    .ShowDropButtonWhen = fmShowDropButtonWhenFocus
                    .SpecialEffect = fmSpecialEffectEtched
                End With
        Next
    Next
     
    End Sub
    What am I not doing correctly that is causing me these errors?

    If you copy and experiment with the code you will find the following procedure useful for deleting the inserted ComboBoxes:

    Code:
    Sub DeleteComboboxes()
     
    '   DeleteComboboxes by row and column
    '   Trevor R Bird
    '   15 Nov 2016
     
    Dim dblHeight As Double
    Dim dblWidth As Double
    Dim lngNumCboBoxes As Long
    Dim lngCboBox As Long
     
    Dim lngRow As Long
    Dim lngColumn As Long
    Dim lngFirstRow As Long
    Dim lngFirstColumn As Long
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
     
    Dim ws As Worksheet
    Dim ctlCombo As OLEObject
     
    Dim Msg, Button, Title As String
    Dim Response As Boolean
     
    Button = vbExclamation
    Title = "Sub Insert_ComboBox()..."
    '    Msg = ""
    '    Response = MsgBox(Msg, Button, Title)
     
    On Error Resume Next
     
    Set ws = Worksheets("Instructions & Analysis")
               
    '   Set row and columns limits for Aspect ComboBoxes
    lngFirstRow = 20
    lngFirstColumn = 2
    lngLastRow = 23
    lngLastColumn = 11
     
    '   Set number of ComboBoxes
    lngNumCboBoxes = 5
     
    '   Set up ComboBoxes
    For lngRow = lngFirstRow To lngLastRow Step 1
        dblHeight = ws.Rows(lngRow).Height
        For lngColumn = lngFirstColumn To lngLastColumn Step 1
            dblWidth = ws.Columns(lngColumn).Width
            ws.Shapes.Range(Array("cboAspect_R" & lngRow & "C" & lngColumn)).Delete
        Next
    Next
     
    End Sub
    Clues to a solution to the error will be very much appreciated.

    Many Thanks

    Trevor

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need to access those properties of the actual control through the Object property of the OLEObject itself:

    Code:
    .Object.ShowDropButtonWhen = fmShowDropButtonWhenFocus
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    BygAuldByrd (2016-11-15)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Thanks Rory, That's resolved the issue beautifully.

    Cheers

    Trevor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •