dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
323
share rss forum feed


Grey Area

join:2003-11-18
UK

[Excel] Updating Form buttons

I have a form with buttons on. I want them to change colour as they are clicked, to improve the contrast. I have the following code;

For Each TB In UserForm1.Controls
If TypeName(TB) = "ToggleButton" Then
Select Case TB.Value
Case True
TB.ForeColor = RGB(0, 255, 0)
Case False
TB.ForeColor = RGB(255, 0, 0)
End Select
End If
Next TB
 

Which I know works because I've attached it to a button to run. Where can I put it so that it runs every time a button is clicked, without having to repeat it for every single "Onclick" command?

I have been messing with;
Private Sub Worksheet_Change(ByVal Target As Range)
 

for the worksheet that contains the control source for all the buttons, but it doesn't seem to work.

Any miracle solutions?


H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK

If you would attach the worksheet it would be much easier to help. I think I know what you are talking about, but an example would be much better.
--
He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot



Grey Area

join:2003-11-18
UK

Trouble is the worksheet is huge and contains a lot of proprietary data...by the time I stripped all that out it would be hard to replicate it.

However...

I think I've sussed it. I have put the control sources for the buttons in one sheet, and I look them up in another. It allows users to sort the second sheet without screwing up all the control sources (SO stupid that they don't follow within VB when they move, but never mind).

Anyways...

I had the Worksheet change event on the second sheet and it appears that a change in a formula is not enough to trigger it - I've put the code on the sheet with the sources now and it seems to work fine.

Furthermore...

The code seems to make Excel unresponsive if I edit the source sheet without having the form open. Does anyone know a quick "IF" test that will check if the form is loaded and skip the code if it's not?

Thanks!