Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pass Objects In Excel? (Office 97 SR2)

    I need to pass a reference to a form control to a sub procedure but in doing so Excel seems to pass a reference to the control's default property instead. How do I pass a reference to the object itself ?

    For example, I built a simple form with a text box (Text0)and a command button (Command2). When you click the button, the background color of the text box changes from red to green or vice versa.

    Option Explicit

    Private Sub Command2_Click()
    Dim x As TextBox
    Set x = Text0 'I have tried Set x = (Text0) as well.

    ChangeColor x
    End Sub

    Sub ChangeColor(aTextbox As TextBox)
    If aTextbox.Interior.Color = vbRed Then
    aTextbox.Interior.Color = vbGreen
    Else
    aTextbox.Interior.Color = vbRed
    End If
    End Sub

    Won't work at all in Excel - throws an error to tell me there's an object required. No sh....Anyway, it's bugging me. Without this ability, I have to replicate code in several different places which even I, in my programming stupidity, know isn't good. So what do I have to do here?

    Oh, and the above code, with minor alterations in syntax, works fine in Access.

    Thanks for help.

    Howard

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pass Objects In Excel? (Office 97 SR2)

    Where you refer to TextBox use MsForms.TextBox and instead of Interior.Color try BackColor.

    Andrew C

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass Objects In Excel? (Office 97 SR2)

    Andrew,

    Thanks for the suggestion. I have implemented it as best I can understand with no luck. Here is the revised code, with the changes you suggested:

    Option Explicit

    Private Sub Command2_Click()
    Dim x As MsForms.TextBox
    Set x = (Text0)

    ChangeColor x ' passes the OBJECT to the ChangeColor Sub
    End Sub

    Sub ChangeColor(aTextbox As MsForms.TextBox)
    If aTextbox.BackColor = vbRed Then
    aTextbox.BackColor = vbGreen
    Else
    aTextbox.BackColor = vbRed
    End If
    aTextbox.Font.Color = vbBlack
    End Sub

    The problem appears to be that when I attempt to refer to the textbox as an object, Excel automatically assumes that I want to refer to the object's default property.

    In other words, "Set x = TextBox", is assumed to mean "Set x = TextBox.Value" (seems that's the default) so the result is that I get the type mismatch: x is defined as being a text box already, but the Set statement tries to assign a string (from the .Value).

    Is it possible that you could alter my example to show me how it would work?

    Thanks

    Howard

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pass Objects In Excel? (Office 97 SR2)

    Howard, I have tested the following in XL2000: <pre>Private Sub Command2_Click()
    Dim x As MsForms.TextBox
    Set x = Text0
    ChangeColor x
    End Sub

    Sub ChangeColor(aTextbox As MsForms.TextBox)
    If aTextbox.BackColor = vbRed Then
    aTextbox.BackColor = vbGreen
    Else
    aTextbox.BackColor = vbRed
    End If
    End Sub</pre>

    Remove the brackets from Set x = (Text0)

    See the attached workbook.. All code is stored in the Userform codepane.

    Andrew C
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pass Objects In Excel? (Office 97 SR2)

    Works fine in XL97.

    Just for the record, the reason that you have to prefix the TextBox dim with MSForms is that Excel also has a TextBox object, so if you dim something as TextBox, Excel assumes that you mean Excel.TextBox even though you are in a UserForm. I wish that there was a stronger Option Explicit that caught an ambiguous reference like this. It has bitten me more time that I'd like to admit. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass Objects In Excel? (Office 97 SR2)

    OH, OK - yes, it works just fine now. Thanks for sticking with me.

    Howard

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass Objects In Excel? (Office 97 SR2)

    Sammy,

    That makes sense now that you mention it. However, it leads me to 2 other questions:
    1. How would I know "MSForms" had to be included - where can I find this sort of stuff documented so I don't waste people's time asking ridiculous questions?

    2. Why doesn't the "RefEdit" object appear in the list of objects after I type MSForms. ?

    Thanks again.

    Howard

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pass Objects In Excel? (Office 97 SR2)

    > How would I know "MSForms" had to be included ?
    Usually by trial and failure! But, when you get an objected related error, you should open the Object Browser and type the object name, TextBox in this case. When you do this, you will notice that there is a TextBox object in both the Excel and MSForms library, so you need the library prefix.

    > Why doesn't the "RefEdit" object appear in the list of objects after I type MSForms
    Because it is not in the MSForms library. Again, open the Object Browser and type RefEdit. You will see that it is in the RefEdit library, so you could say "dim rf as RefEdit.RefEdit" but since it is only in a single library, there is no need.

    Initially, I thought that the ObjectBrowser was useless, but I find that I use it more and more. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pass Objects In Excel? (Office 97 SR2)

    Just for the record, ChangeColor Text0 would have worked just as well.

    Andrew C

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pass Objects In Excel? (Office 97 SR2)

    Sammy, thanks for the input here. I would agree that a lot can be learned from the Object Browser, together with Intellisense and the online help. The object browser can also be useful if you have undocumented AddIns, you might find some useful functions.

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •