I Want Get =EMBED("Forms.ListBox.1";"")'s Value to Cell...

[Erdin? E. Ka]

Hi everyone,

I have to ListBox Control on Sheet1. I tried get their value to a cell via formula but i couldn't solve it.

[VBA]=EMBED("Forms.ListBox.1";"")[/VBA]

Is there a way to get a page control's value via formula?

If this is impossible;

Can we coding for an UDF for all control types ?

Example syntaxs:
=GET_PAGE_CONTROL_VALUE("FullNameOfWorkBook","NameOfWorkSheet","NameOfContr olType")
=GET_PAGE_CONTROL_VALUE("C:\Test.xls","Sheet1","ListBox1")
=GET_PAGE_CONTROL_VALUE("C:\Test.xls","Sheet1","ComboBox1")
=GET_PAGE_CONTROL_VALUE("C:\Test.xls","Sheet1","TextBox1")


I attached my file.

Thanks in advance.

[mdmackillop]

Try
[VBA]Sub Macro1()
Range("D20") = ActiveSheet.ListBox1.Value
End Sub
[/VBA]

[malik641]

This works pretty nicely:

[VBA]Public Function Get_Page_Control_Value(ByVal strWB As String, _
ByVal strWS As String, ByVal strControlName As String) As Variant
' Will return the selected or current value of an embedded object
Application.Volatile True

On Error GoTo ErrorHandler
Get_Page_Control_Value = Workbooks(strWB).Sheets(strWS).OLEObjects(strControlName).Object.Value
Exit Function

ErrorHandler:
Get_Page_Control_Value = CVErr(xlErrValue)
End Function[/VBA]

[malik641]

Hmm...it's having trouble with the Checkbox. Add this to the ThisWorkbook class module:

[VBA]Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Calculate
End Sub
[/VBA]