Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Combo Box and Populate
I am trying to create a combo box and populate it in a macro. If I create the
combo box in one sub and then run a separate sub to populate it then it works fine. If I try to create and populate the combo in the same sub then it fails. It creates and names the combo box but when it tries to populate it returns Runtime Error 438. Will very much appreciate any help on what I am missing here because I want to be able to put it into a workbook open routine. My example code as follows:- The following works if I run the subs separately:- Dim objCombo As Object Sub Create_Combo() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" End Sub Sub Combo_Populate() Set objCombo = Sheets("Sheet1").MyCombo objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem "E - EE" objCombo.AddItem "F - FF" End Sub The following does not work as a single sub:- Sub CreateAndPopulate() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" 'Runtime error 438 occurs with the following objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem ("E - EE") objCombo.AddItem ("F - FF") End Sub Regards, OssieMac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Combo Box and Populate
try add the Userformname
Userformname.objCombo.AddItem "A - AA" "OssieMac" skrev: I am trying to create a combo box and populate it in a macro. If I create the combo box in one sub and then run a separate sub to populate it then it works fine. If I try to create and populate the combo in the same sub then it fails. It creates and names the combo box but when it tries to populate it returns Runtime Error 438. Will very much appreciate any help on what I am missing here because I want to be able to put it into a workbook open routine. My example code as follows:- The following works if I run the subs separately:- Dim objCombo As Object Sub Create_Combo() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" End Sub Sub Combo_Populate() Set objCombo = Sheets("Sheet1").MyCombo objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem "E - EE" objCombo.AddItem "F - FF" End Sub The following does not work as a single sub:- Sub CreateAndPopulate() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" 'Runtime error 438 occurs with the following objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem ("E - EE") objCombo.AddItem ("F - FF") End Sub Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Combo Box and Populate
Try something like the following:
Dim objCombo As OLEObject Sub CreateAndPopulate() With Sheets("Sheet1") Set objCombo = .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) End With With objCombo .Name = "MyCombo" With .Object .AddItem "A - AA" .AddItem "B - BB" .AddItem "C - CC" .AddItem "D - DD" .AddItem ("E - EE") .AddItem ("F - FF") .ListIndex = 0 End With End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "OssieMac" wrote in message ... I am trying to create a combo box and populate it in a macro. If I create the combo box in one sub and then run a separate sub to populate it then it works fine. If I try to create and populate the combo in the same sub then it fails. It creates and names the combo box but when it tries to populate it returns Runtime Error 438. Will very much appreciate any help on what I am missing here because I want to be able to put it into a workbook open routine. My example code as follows:- The following works if I run the subs separately:- Dim objCombo As Object Sub Create_Combo() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" End Sub Sub Combo_Populate() Set objCombo = Sheets("Sheet1").MyCombo objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem "E - EE" objCombo.AddItem "F - FF" End Sub The following does not work as a single sub:- Sub CreateAndPopulate() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" 'Runtime error 438 occurs with the following objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem ("E - EE") objCombo.AddItem ("F - FF") End Sub Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Combo Box and Populate
Thanks Chip. Very much appreciated.
Regards, OssieMac "Chip Pearson" wrote: Try something like the following: Dim objCombo As OLEObject Sub CreateAndPopulate() With Sheets("Sheet1") Set objCombo = .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) End With With objCombo .Name = "MyCombo" With .Object .AddItem "A - AA" .AddItem "B - BB" .AddItem "C - CC" .AddItem "D - DD" .AddItem ("E - EE") .AddItem ("F - FF") .ListIndex = 0 End With End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "OssieMac" wrote in message ... I am trying to create a combo box and populate it in a macro. If I create the combo box in one sub and then run a separate sub to populate it then it works fine. If I try to create and populate the combo in the same sub then it fails. It creates and names the combo box but when it tries to populate it returns Runtime Error 438. Will very much appreciate any help on what I am missing here because I want to be able to put it into a workbook open routine. My example code as follows:- The following works if I run the subs separately:- Dim objCombo As Object Sub Create_Combo() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" End Sub Sub Combo_Populate() Set objCombo = Sheets("Sheet1").MyCombo objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem "E - EE" objCombo.AddItem "F - FF" End Sub The following does not work as a single sub:- Sub CreateAndPopulate() With Sheets("Sheet1") .OLEObjects.Add _ (ClassType:="Forms.ComboBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=288, Top:=39, _ Width:=193.5, Height:=26.25) _ .Select End With Set objCombo = Selection objCombo.Name = "MyCombo" 'Runtime error 438 occurs with the following objCombo.AddItem "A - AA" objCombo.AddItem "B - BB" objCombo.AddItem "C - CC" objCombo.AddItem "D - DD" objCombo.AddItem ("E - EE") objCombo.AddItem ("F - FF") End Sub Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to populate Combo Box ?? | Excel Programming | |||
Populate one combo box based on the selection of another combo box | Excel Programming | |||
populate combo box | Excel Discussion (Misc queries) | |||
Populate a combo box | Excel Discussion (Misc queries) | |||
Populate a combo Box | Excel Worksheet Functions |