Excel VBA ComboBox “.ShowDropButtonWhen = fmShowDropButtonWhenFocus” Error 438

Topic

New Reply

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:

45988-20161115-VBA-438-Error

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

Reply | Quote

Replies

You need to access those properties of the actual control through the Object property of the OLEObject itself:

Code:

.Object.ShowDropButtonWhen = fmShowDropButtonWhenFocus

Reply | Quote

Thanks Rory, That’s resolved the issue beautifully.

Cheers

Trevor

Reply | Quote