The code example below causes an "Object doesn't support this property or method (Error 438)" message. The Compiler sees the
word "ComboBox1" in the code before it gives itself a chance to create the Object and make references to it as a Member of
the Sheet it was created in.
How can Code be used to add a ComboBox to a worksheet and set properties such as Fonts and FillColor for that ComboBox
without using or adding a Form? (See: Experiment Goals and the Code example below for a better explanation.)
Without using the Forms Toolbar and without adding a Form to the Project.
In VBA code,
1. Add a ComboBox to a Worksheet.
2. Format the ComboBox (e.g. Change its Font, FillColor, Placement etc. See: code example below)
3. Do the above in "One" Run. (In other words, without asking the user to run the program twice.)
desired results can be viewed by adding Conditional Compiler options to create the object on the first Run, and Format it on
the Next. For an explanation of why this workaround can't be used See: Goal Notes (below code example).
Private Sub ComboBoxTest()
Dim strtHrBox As Shape
'Create an Hour ComboBox.
Set strtHrBox = Application.ActiveSheet.Shapes. _
Left:=249.75, Top:=38, Width:=30, Height:=15.5)
'Error: Object doesn't support this property or method (Error 438)
'Note: ComboBox1 doesn't exist until after Run-time
'The Compiler is complaining about the non-existance of a
'Sheet Member it hasen't given itself a chance to create.
.AutoSize = False
.BackColor = RGB(197, 197, 197)
.ColumnCount = 1
.ColumnWidths = 1
.Font.Name = "Small Fonts"
.Font.Size = 7
.Font.Bold = True
.LinkedCell = "Sheet1!E2" 'Change this to match your Sheets Name.
.ListFillRange = "Sheet1!B2:B13" 'Change this to match your Sheets Name.
.ListRows = 12
.ListWidth = 28
.Placement = xlFreeFloating
.SelectionMargin = False
.TextAlign = fmTextAlignLeft
.Name = "StartHrBox" '
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Getting this to work in one pass or one run will allow the code to hook to an event trigger. Or at the very least I won't
have to ask the user to run the program twice in order to display an object correctly on screen. Figuring out how to do this
without using Forms allows me to embed the Controls in Shapes.
The ComboBox Class has all the properties and members that need to be used for this experiment but the ComboBox Class seems
to be intended for use within a Form. I mentioned this because the With block in the above example works on the second Run
after ComboBox1 is created as a Member of the Sheet in which it was created by the first run.
Member of MSForms
Microsoft Forms 2.0 Object Library
Thank you for your time and help. At the very least I hope this sparks
some interesting thoughts, ideas, or questions for the Forum.