'n more ! Excel 97: Return of Forms Controls
Assigning low points so I am able to honour valuable contributions w/o going broke.
Looking for simplifying the calculation with return values of _Forms_ controls:
What I do today:
a) Combo-Box and Listbox: 'Cell Link' to put the return index somewhere on the worksheet, then write =INDEX(Listrange, CellLinkName) in an additional cell if I want/need to use the description of the choosen item somewhere else.
b) Option Button and Check Box: 'Cell Link', eventually translate it with a IF...Then...Else construct.
Question: As I have _lots_ of them, I would like to have a way to get the return directly. Should it require a centralized 'User Defined Function' (i.e. VBA), then only if I am able to pass the control with a name I will assign, no way I am going to mess with the Microsoft numbering system.
Restriction: No Toolbox controls, please, they are ActiveX.
Looking for simplifying the calculation with return values of _Forms_ controls:
What I do today:
a) Combo-Box and Listbox: 'Cell Link' to put the return index somewhere on the worksheet, then write =INDEX(Listrange, CellLinkName) in an additional cell if I want/need to use the description of the choosen item somewhere else.
b) Option Button and Check Box: 'Cell Link', eventually translate it with a IF...Then...Else construct.
Question: As I have _lots_ of them, I would like to have a way to get the return directly. Should it require a centralized 'User Defined Function' (i.e. VBA), then only if I am able to pass the control with a name I will assign, no way I am going to mess with the Microsoft numbering system.
Restriction: No Toolbox controls, please, they are ActiveX.
ASKER
ture, thanks, I see now that my question was not formulated clearly.
I want to have a general purpose function/mean to calculate with the returns values
Example:
instead
=IF(CellLinkA=3,...,...)
I want to be able to write
=IF(wtis(ListboxA,"val")=3
Or, mainly to ease additions
=IF(wtis(ListboxA,"dscr")=
I want to have a general purpose function/mean to calculate with the returns values
Example:
instead
=IF(CellLinkA=3,...,...)
I want to be able to write
=IF(wtis(ListboxA,"val")=3
Or, mainly to ease additions
=IF(wtis(ListboxA,"dscr")=
Cri,
You mean something like this?
Function GetListBoxValue(lb As String) As String
Application.Volatile
GetListBoxValue = ActiveSheet.ListBoxes(lb).
End Function
/Ture
You mean something like this?
Function GetListBoxValue(lb As String) As String
Application.Volatile
GetListBoxValue = ActiveSheet.ListBoxes(lb).
End Function
/Ture
Or something like this:
Function MyRet(Ctrl As String)
Application.Volatile
Dim mctr As Shape
Set mctr = ActiveSheet.Shapes(Ctrl)
If mctr.Type <> msoFormControl Then Exit Function
Select Case mctr.FormControlType
Case xlListBox
MyRet = mctr.ControlFormat.Value
Case xlDropDown
MyRet = mctr.ControlFormat.Value
Case xlCheckBox
If mctr.ControlFormat.Value = -4146 Then
MyRet = True
Else
MyRet = False
End If
Case xlOptionButton
If mctr.ControlFormat.Value = -4146 Then
MyRet = True
Else
MyRet = False
End If
End Select
End Function
Calacuccia
Function MyRet(Ctrl As String)
Application.Volatile
Dim mctr As Shape
Set mctr = ActiveSheet.Shapes(Ctrl)
If mctr.Type <> msoFormControl Then Exit Function
Select Case mctr.FormControlType
Case xlListBox
MyRet = mctr.ControlFormat.Value
Case xlDropDown
MyRet = mctr.ControlFormat.Value
Case xlCheckBox
If mctr.ControlFormat.Value = -4146 Then
MyRet = True
Else
MyRet = False
End If
Case xlOptionButton
If mctr.ControlFormat.Value = -4146 Then
MyRet = True
Else
MyRet = False
End If
End Select
End Function
Calacuccia
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, will try the solutions under working conditions, might take 1-2 days.
ASKER
I think calacuccia is nearer to what I need/want. I think the only correction to be made is to invert the returns of the Option and the Check Box, as "-4146=False"
I am not sure whether I can actually use it, but this is due to Microsoft and not your VBA/routines. Reason: The refresh of the function returns work nicely _as long_ the worksheet recalculates, be this because of _another_ calculation or because of a (unnecessary) link between the control and a spreadsheet cell. If the recalculation is not triggered, then nothing happens. In the real world this would mean that if the user changes control w/o changing a numeric input nothing will happen. Additionally, I was badly burned with Volatile functions, my workbooks can be pretty complicated.
ture's first comment, writing to the Immediate Pane, could be useful if there is a way to pass the assigned name and not the original numbering.
Leaving the question open for a while, perhaps you have some additions/corrections.
I am not sure whether I can actually use it, but this is due to Microsoft and not your VBA/routines. Reason: The refresh of the function returns work nicely _as long_ the worksheet recalculates, be this because of _another_ calculation or because of a (unnecessary) link between the control and a spreadsheet cell. If the recalculation is not triggered, then nothing happens. In the real world this would mean that if the user changes control w/o changing a numeric input nothing will happen. Additionally, I was badly burned with Volatile functions, my workbooks can be pretty complicated.
ture's first comment, writing to the Immediate Pane, could be useful if there is a way to pass the assigned name and not the original numbering.
Leaving the question open for a while, perhaps you have some additions/corrections.
ASKER
Adjusted points from 50 to 75
ASKER
Comment accepted as answer
ASKER
calacuccia and ture, thanks.
ASKER
calacuccia, seems that I have pushed you over 100k in MS-Office. Congratulations.
My Pleasure, cri
And thanks a million for the final push ;-)
Now it's up to you. (my Prediction 15th of August)
Calacuccia
And thanks a million for the final push ;-)
Now it's up to you. (my Prediction 15th of August)
Calacuccia
ASKER
As I will be away the next two weeks I will have to re-climb the mole hill again.
Which reminds me of your 'Auditing Question'.
I will post a comment there shortly, but it does not look good from my angle.
I will post a comment there shortly, but it does not look good from my angle.
ASKER
Take your time, I don't think I can use this tomorrow at work.
Does this help any?
Sub ShowNamesAndValues()
Dim x
For Each x In ActiveSheet.ListBoxes
Debug.Print x.Name, x.Value
Next x
For Each x In ActiveSheet.DropDowns
Debug.Print x.Name, x.Value
Next x
For Each x In ActiveSheet.CheckBoxes
Debug.Print x.Name, x.Value
Next x
For Each x In ActiveSheet.OptionButtons
Debug.Print x.Name, x.Value
Next x
For Each x In ActiveSheet.ScrollBars
Debug.Print x.Name, x.Value
Next x
For Each x In ActiveSheet.Spinners
Debug.Print x.Name, x.Value
Next x
Debug.Print ActiveSheet.CheckBoxes(1).
Debug.Print ActiveSheet.CheckBoxes("Ch
End Sub
Ture Magnusson
Karlstad, Sweden