PDA

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



Erdin? E. Ka
12-03-2006, 08:44 AM
Hi everyone, :hi:

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

=EMBED("Forms.ListBox.1";"")

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","NameOfControlType")
=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.:friends:

mdmackillop
12-03-2006, 10:55 AM
Try
Sub Macro1()
Range("D20") = ActiveSheet.ListBox1.Value
End Sub

malik641
12-03-2006, 11:18 AM
This works pretty nicely:

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:)

malik641
12-03-2006, 11:23 AM
Hmm...it's having trouble with the Checkbox. Add this to the ThisWorkbook class module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Calculate
End Sub