Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong type of Listbox from VBA
Hello group,
I am using this code to generate a listbox in a worksheet. The only problem is that the listbox is from the Forms toolbar. ================================================== =================== Application.CommandBars("Control Toolbox").Visible = True ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, Height:=125.25) ..Select Application.CommandBars("Control Toolbox").Visible = False ================================================== ==================== I require the Linked Cell function of the Control Listbox . I used the Macro record for this code. Thanks, Michael Singmin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong type of Listbox from VBA
Even though it says: Forms.listbox.1, it's from the control toolbox toolbar.
But this worked ok for me: Option Explicit Sub testme() Dim OLEObj As OLEObject Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", _ Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, _ Height:=125.25) With OLEObj .ListFillRange = ActiveSheet.Range("a1:a10").Address(external:=True ) .LinkedCell = ActiveSheet.Range("b1").Address(external:=True) End With End Sub Michael Singmin wrote: Hello group, I am using this code to generate a listbox in a worksheet. The only problem is that the listbox is from the Forms toolbar. ================================================== =================== Application.CommandBars("Control Toolbox").Visible = True ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, Height:=125.25) .Select Application.CommandBars("Control Toolbox").Visible = False ================================================== ==================== I require the Linked Cell function of the Control Listbox . I used the Macro record for this code. Thanks, Michael Singmin -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong type of Listbox from VBA
Well done Dave,
Again, I have learnt something new and valuable. Much appreciated. Michael ================================================== ==================== Dave Peterson wrote: Even though it says: Forms.listbox.1, it's from the control toolbox toolbar. But this worked ok for me: Option Explicit Sub testme() Dim OLEObj As OLEObject Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", _ Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, _ Height:=125.25) With OLEObj .ListFillRange = ActiveSheet.Range("a1:a10").Address(external:=True ) .LinkedCell = ActiveSheet.Range("b1").Address(external:=True) End With End Sub Michael Singmin wrote: Hello group, I am using this code to generate a listbox in a worksheet. The only problem is that the listbox is from the Forms toolbar. ================================================== =================== Application.CommandBars("Control Toolbox").Visible = True ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, Height:=125.25) .Select Application.CommandBars("Control Toolbox").Visible = False ================================================== ==================== I require the Linked Cell function of the Control Listbox . I used the Macro record for this code. Thanks, Michael Singmin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong type of Listbox from VBA
Hello again Dave,
I notice the listbox is not alive after I run this code. It does not respond if I click it. What I have to do to wake it up, is to go manually in and out of design mode (Control toolbar). Also, what is the purpose of the (external:=true) in the code ? Thanks, Michael Dave Peterson wrote: Even though it says: Forms.listbox.1, it's from the control toolbox toolbar. But this worked ok for me: Option Explicit Sub testme() Dim OLEObj As OLEObject Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", _ Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, _ Height:=125.25) With OLEObj .ListFillRange = ActiveSheet.Range("a1:a10").Address(external:=True ) .LinkedCell = ActiveSheet.Range("b1").Address(external:=True) End With End Sub Michael Singmin wrote: Hello group, I am using this code to generate a listbox in a worksheet. The only problem is that the listbox is from the Forms toolbar. ================================================== =================== Application.CommandBars("Control Toolbox").Visible = True ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, Height:=125.25) .Select Application.CommandBars("Control Toolbox").Visible = False ================================================== ==================== I require the Linked Cell function of the Control Listbox . I used the Macro record for this code. Thanks, Michael Singmin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong type of Listbox from VBA
Maybe you're in design mode.
Show that control toolbox toolbar and toggle the "design mode" icon. (Top left corner of the toolbar.) try a simple test: msgbox range("a1").address and msgbox range("a1").address(external:=true) You'll see that with external:=true, you get the workbook name, worksheet name and cell address. So I guess it's just one way of making sure that you're using the range you want to use. Michael Singmin wrote: Hello again Dave, I notice the listbox is not alive after I run this code. It does not respond if I click it. What I have to do to wake it up, is to go manually in and out of design mode (Control toolbar). Also, what is the purpose of the (external:=true) in the code ? Thanks, Michael Dave Peterson wrote: Even though it says: Forms.listbox.1, it's from the control toolbox toolbar. But this worked ok for me: Option Explicit Sub testme() Dim OLEObj As OLEObject Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", _ Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, _ Height:=125.25) With OLEObj .ListFillRange = ActiveSheet.Range("a1:a10").Address(external:=True ) .LinkedCell = ActiveSheet.Range("b1").Address(external:=True) End With End Sub Michael Singmin wrote: Hello group, I am using this code to generate a listbox in a worksheet. The only problem is that the listbox is from the Forms toolbar. ================================================== =================== Application.CommandBars("Control Toolbox").Visible = True ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, Height:=125.25) .Select Application.CommandBars("Control Toolbox").Visible = False ================================================== ==================== I require the Linked Cell function of the Control Listbox . I used the Macro record for this code. Thanks, Michael Singmin -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong type of Listbox from VBA
? ActiveSheet.Range("a1:a10").Address(external:=True )
[Book3]Sheet2!$A$1:$A$10 ? activesheet.Range("A1:a10").Address $A$1:$A$10 -- Regards, Tom Ogilvy "Michael Singmin" wrote in message ... Hello again Dave, I notice the listbox is not alive after I run this code. It does not respond if I click it. What I have to do to wake it up, is to go manually in and out of design mode (Control toolbar). Also, what is the purpose of the (external:=true) in the code ? Thanks, Michael Dave Peterson wrote: Even though it says: Forms.listbox.1, it's from the control toolbox toolbar. But this worked ok for me: Option Explicit Sub testme() Dim OLEObj As OLEObject Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", _ Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, _ Height:=125.25) With OLEObj .ListFillRange = ActiveSheet.Range("a1:a10").Address(external:=True ) .LinkedCell = ActiveSheet.Range("b1").Address(external:=True) End With End Sub Michael Singmin wrote: Hello group, I am using this code to generate a listbox in a worksheet. The only problem is that the listbox is from the Forms toolbar. ================================================== =================== Application.CommandBars("Control Toolbox").Visible = True ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListB ox.1", Link:=False, DisplayAsIcon:=False, Left:=114, Top:=105, Width:=90, Height:=125.25) .Select Application.CommandBars("Control Toolbox").Visible = False ================================================== ==================== I require the Linked Cell function of the Control Listbox . I used the Macro record for this code. Thanks, Michael Singmin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: Value in Formula of wrong data type | Excel Worksheet Functions | |||
Wrong data type | Excel Discussion (Misc queries) | |||
Where am I going wrong? - Date type function. | Excel Worksheet Functions | |||
Type mismatched in Listbox | Excel Programming | |||
Populating multicolumn listbox the wrong way | Excel Programming |