Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing control properties (XL2002)

    Hi. Got a problem.

    I've got a form in Excel with a pile of text boxes on it. They are named TEXTBOX0 through to TEXTBOX23. Now, I would dearly love to be able to refer to these as a single entity, but I can't because I need to access events. As a result, I have 24 virtually identical KEYPRESS events and 24 virtually identical CHANGE events, calling a single routine in each case which does what I want. That's a different story, however - at least it works.

    What I now want to do is to set the font colour of a given box based on the contents of an array of flags. In other words, whenever the text in a particular box has changed, change the colour to red, otherwise leave it black. To do this, I use code along the lines of:

    <pre>Sub DisplayItems()

    Dim iHour as integer
    for iHour=0 to 23
    if bChangeFlag(iHour) then
    Controls.Item("TEXTBOX" & iHour).Font.Color=RGB(255,0,0)
    Else
    Controls.Item("TEXTBOX" & iHour).Font.Color=RGB(0,0,0)
    Endif
    Next iHour

    End Sub
    </pre>


    Excel refuses to do this, however, and so I am left with no way to index the controls. Putting the controls into a collection is no help, either, as in both cases the only thing I can seem to access is the value of the control. Equally, I cannot allocate the control to a textbox variable.

    What am I to do? How can I modify one varying box out of many?

    Help much appreciated, before I (and my client!) go mad.

    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Changing control properties (XL2002)

    Hi Stuart,
    Have you tried:
    <pre>Controls.Item("TEXTBOX" & iHour).ForeColor=RGB(255,0,0)
    </pre>

    The NewFont object does not have a Color property.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Changing control properties (XL2002)

    The way to get rid of all those identical events is to define a wrapper class that contains all of the controls. Look at this thread to see how I did it with textboxes on a userform and also look at the referenced j-walk tip to see how John did it with command buttons. It's a little mind-boggling at first, but well worth it to get rid of all that duplicate code. All this mess because Microsoft won't give us an array of controls! 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>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing control properties (XL2002)

    But they did! All you have to do is switch to doing everything in VB!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    An alternative to VB is to declare a variant array and populate it with control objects. Then you can refer to each element of the array by its index and use the methods and properties of the object the element contains.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing control properties (XL2002)

    Sam,

    That's neat, thanks.

    I've one question for you on the implementation, though - the class refers to a collection by name which is only defined outside of the class (in the module). Is that good programming, or should the class ideally be designed to be completely contained, i.e. you should pass the collection by setting something or other?

    In only ask because I've never used classes before, but I have this vague idea about how I thought they worked and I wouldn't want to break the code when I release it out into the wild.

    Thanks.

    Stuart

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing control properties (XL2002)

    Great, ForeColor works perfectly. Thanks.

    Interestingly, though, if I create X as a textbox in code, I can then specify x.font.color in code (I haven't tried running it, but Microsoft will quite cheefully give me font as a property of x, and color as a propery of font). That's why I picked that property in the first place. Hohum.

    Stuart

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing control properties (XL2002)

    ... but if you use the variant collection, you can't have one event for multiple controls, or am I missing something?

    Anyway, surely the Controls collection is effectively a variant array of controls, isn't it?

    Stuart

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing control properties (XL2002)

    The difference is that the Controls collection contains *all* controls, not just the ones you want to address as an array.
    Charlotte

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

    Re: Changing control properties (XL2002)

    > question for you on the implementation [of my textbox example]

    Looking at the code again, I have exactly the same concerns! I think when I wrote the code I hadn't done classes for several years. It definitly needs a redesign. But, in your case, don't you just need something like the code from j-walk, except for textboxes? If so, you can just copy John's code, having a class for your textboxes and another for your command buttons.

    > if you use the variant collection, you can't have one event for multiple controls

    Exactly correct. I think that Rory and Charlotte were just trying to solve the font color problem, not the multiple events.

    Have a great day! --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>

Posting Permissions

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