Combo Boxes

[Trystan]

I have a series of combo boxes, and have created a macro to make other
boxes appear or disappear. To make the correct boxes appear, I need the
macro to select the name of the combo box that was used. ie if it was
"dd1" i need a function that will return this to a string.

Any ideas?

T

[Ronin]

Trystan,

Try this

Function ReturnCBOName(optional byval cboBox as MSForms.ComboBox) As String
ReturnCBOName = cboBox.Name
End Function

when you call the function in any procedure use the following
Assigning to a string variable:
strVariable = ReturnCBOName(your_combo_box_Name)
Other Reason (sample "IF" statement)
If ReturnCBOName(your_combo_box_Name) = "Your Result If TRUE" then
End If

Best Luck.


Ronin


"Trystan" wrote:

> I have a series of combo boxes, and have created a macro to make other
> boxes appear or disappear. To make the correct boxes appear, I need the
> macro to select the name of the combo box that was used. ie if it was
> "dd1" i need a function that will return this to a string.
>
> Any ideas?
>
> T
>
>

[Trystan]

Thanks for the help - much appreciated - however when I step through, i
get a run time error 91 - object variable or with block variable not
set - hav i forgotten to add anything?

Thanks

T

[Ronin]

More likely the error is happening with in the function.

If that's the case, specify your current combo box... not the property of
the combobox... sample:

ReturnCBOName(your_combo_box_name) '<--- Correct way

ReturnCBOName(your_combo_box_name.ListIndex) '<--- Incorrect

This way... you have all (every thing) the information within the combobox.
Like the listed items, properties, etc...

If this is not happening in the function... post the basic information...
either... how you are calling it and or the function itself so i could
analyze it.



Thanks in advance


"Trystan" wrote:

> Thanks for the help - much appreciated - however when I step through, i
> get a run time error 91 - object variable or with block variable not
> set - hav i forgotten to add anything?
>
> Thanks
>
> T
>
>

[Trystan]

I've got that working however I have to enter the name of the combobox
that I am working on (your_combo_box_name) - this is the value I am
trying to get see my code below:

So for instance I need to get currentcomboboxname so that I can use it
to specify the other shapes - currently "ot12" and "mt12" - can i do
that with his code? - am I missing something here ?

strVariable = ReturnCBOName(currentcomboboxname)

If Range("D12").Value = "Other" Then
ActiveSheet.Shapes("ot12").Visible = True
ActiveSheet.Shapes("mt12").Visible = True
Else:
If ActiveSheet.OLEObjects("ot12").Object.Text = "" Then
ActiveSheet.OLEObjects("ot12").Visible = False
ActiveSheet.OLEObjects("mt12").Visible = False
Else
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "You have already entered text in a field. Press Yes
to continue and delete all text, press No to return." ' Define
message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define
buttons.
Title = "Error" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
ActiveSheet.OLEObjects("ot12").Object.Text = ""
ActiveSheet.OLEObjects("mt12").Visible = False
ActiveSheet.OLEObjects("ot12").Visible = False
Else ' User chose No.
Range("D12").Value = "7"
End If

End If
End If

End Sub


Function ReturnCBOName(Optional ByVal cboBox As MSForms.ComboBox) As
String
ReturnCBOName = cboBox.Name
End Function

[Ronin]

Trystan,

If you want the value selected in the combobox, then in the function:

Function ReturnCBOName(Optional ByVal cboBox As MSForms.ComboBox) As
String
ReturnCBOName = cboBox.Text '<--- or you could subtitute
cboBox.Column(#) <--- # represents what ever column has your value.
End Function

But i'm not sure what you're wanting it to do... return combo box's name or
value?
else if you want other object to be evaluated:

Function ReturnName(Optional ByVal mObject) As
String
ReturnName = mObject.Name
End Function

notice after "ByVal" the rest of the characters were changed or removed
(cboBox As MSForms.ComboBox) befor the ")". this allows you to send any
object/control to the function to be evaluated and the "ReturnName becomes
like avariable that stores the value to return to the calling procedure...
meaning

ReturnName = mObject.Name '<--- this assigns the name of the object to the
function ReturnName


so when you got a procedure and within the procedure you call the function
like this (and this is just an example),

Private Sub UserForm_Activate()

Dim strStorageName As String*50
strStorageName = ReturnName(MyShape) '<-- once the code has finish
running function (ReturnName)... strStorageName then have the string name of
the "MyShape"

End Sub



Ronin
"Trystan" wrote:

> I've got that working however I have to enter the name of the combobox
> that I am working on (your_combo_box_name) - this is the value I am
> trying to get see my code below:
>
> So for instance I need to get currentcomboboxname so that I can use it
> to specify the other shapes - currently "ot12" and "mt12" - can i do
> that with his code? - am I missing something here ?
>
> strVariable = ReturnCBOName(currentcomboboxname)
>
> If Range("D12").Value = "Other" Then
> ActiveSheet.Shapes("ot12").Visible = True
> ActiveSheet.Shapes("mt12").Visible = True
> Else:
> If ActiveSheet.OLEObjects("ot12").Object.Text = "" Then
> ActiveSheet.OLEObjects("ot12").Visible = False
> ActiveSheet.OLEObjects("mt12").Visible = False
> Else
> Dim Msg, Style, Title, Help, Ctxt, Response, MyString
> Msg = "You have already entered text in a field. Press Yes
> to continue and delete all text, press No to return." ' Define
> message.
> Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define
> buttons.
> Title = "Error" ' Define title.
> Help = "DEMO.HLP" ' Define Help file.
> Ctxt = 1000 ' Define topic
> ' context.
> ' Display message.
> Response = MsgBox(Msg, Style, Title, Help, Ctxt)
> If Response = vbYes Then ' User chose Yes.
> ActiveSheet.OLEObjects("ot12").Object.Text = ""
> ActiveSheet.OLEObjects("mt12").Visible = False
> ActiveSheet.OLEObjects("ot12").Visible = False
> Else ' User chose No.
> Range("D12").Value = "7"
> End If
>
> End If
> End If
>
> End Sub
>
>
> Function ReturnCBOName(Optional ByVal cboBox As MSForms.ComboBox) As
> String
> ReturnCBOName = cboBox.Name
> End Function
>
>