Can I change a controlbox to a listbox on a Form

[Post Tenebras Lux]

I want to change many controlboxes to listboxes on a form. In Access, this
is easy with either a rightclick or format change. I can't find a similar
menu action in Excel VBA. Is it possible?

It would save me alot of time renaming / repositioning the listbox controls.
The code underneath is fine.

Thanks!

 

[Tom Ogilvy]

It is unclear what you are refering to with the term controlboxes, but
Neither forms controls or controls from the control toolbox toolbar support
this.

If you mean make a combobox act like a listbox in terms of forcing the user
to select from the dropdown list, then there is an option for this in the
control toolbox combobox.

 

[Post Tenebras Lux]

Thanks. I think you got my question in your first paragraph answer. I
wonder why its possible in Access VBA by right-clicking the control and
selecting "Change to Listbox". It will teach me to think before I design.

Que sera sera.

 

[NickHK]

Access has always been designed differently. Why only Access has this
capability is one of those unknowns.
Excel you have to do it yourself.

NickHK

 

[Peter T]

Drag your userform into a new workbook. Probably best to comment or store &
remove all code in the form module.

In a normal module -
change name of UserForm1 to that of your form

Sub CombosToListboxes()
Dim fm As UserForm 'Object
Dim i As Long
Dim ctr As Control, ctrLB As Control
Dim tp As Single

Set fm = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
tp = fm.InsideHeight

For Each ctr In fm.Controls
If TypeName(ctr) = "ComboBox" Then
i = i + 1
Set ctrLB = fm.Controls.Add("Forms.ListBox.1")
With ctr
Cells(i, 1) = ctr.Name
Cells(i, 2) = ctr.TabIndex
Cells(i, 3) = ctrLB.Name

ctrLB.Left = .Left
ctrLB.Top = .Top
ctrLB.Width = .Width
ctrLB.Height = .Height
' copy any other properties here

.Move 0, tp ' put combobox below the visible form
End With

End If
Next

End Sub

' manually make the form taller and delete all the comboboxes
' (not sure how to delete controls as 'designer', hence this two step
approach)

Sub renameListBoxes()
Dim fm As UserForm
Dim i As Long
Dim ctr As Control

Set fm = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
For Each ctr In fm.Controls
If TypeName(ctr) = "ListBox" Then
i = i + 1
ctr.Name = Cells(i, 1)
End If
Next

End Sub

Export the form from the original the original workbook. Drag the new form
back and if necessary replace the code. Hold your breath and test!

Regards,
Peter T

 

[Post Tenebras Lux]

Thanks. I'll give it a try. Maybe it should be called VB for some
Applications.