Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change a controlbox to a listbox on a Form
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change a controlbox to a listbox on a Form
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. -- Regards, Tom Ogilvy "Post Tenebras Lux" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change a controlbox to a listbox on a Form
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. "Post Tenebras Lux" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change a controlbox to a listbox on a Form
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 "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. "Post Tenebras Lux" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change a controlbox to a listbox on a Form
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").D esigner 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").D esigner 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" wrote in message ... 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. "Post Tenebras Lux" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change a controlbox to a listbox on a Form
Thanks. I'll give it a try. Maybe it should be called VB for some
Applications. "Peter T" wrote: 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").D esigner 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").D esigner 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" wrote in message ... 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. "Post Tenebras Lux" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlbox - Form Control - ActiveX | Excel Discussion (Misc queries) | |||
listbox value to a form | Excel Programming | |||
vb6 form with listbox | Excel Programming | |||
User form with a listbox | Excel Programming | |||
Listbox/Form question | Excel Programming |