Conditional Disabling of ActiveX Controls

[the researcher]

First of all many thanks to many of you for providing me with amazing solutions to my large and small Excel problems. So far I never had to post and so I never had an opportunity to thank you before this.

What I want to do disable certain ActiveX toggles depending on the value of a specific cell; this cell can contain more than one value that should disable that control. the logic works as follows:

IF Cell="A" THEN Toggle 1=Disabled AND Toggle 2=Disabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="B" THEN Toggle 1=Ensabled AND Toggle 2=Enabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="C" THEN Toggle 1=Enabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="D" OR Cell="E" THEN Toggle 1=Disabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Enabled AND Toggle 5-Enabled

Is this even possible?

Many thanks in advance for your suggestions and sorry if I am overlooking something very obvious here!

 

[the researcher]

Oh, I represented the actual words in the source cell - D43 for the first segment, then F43 H43 J43 L43 N43 P34 R34 T34 and V43 for the other 9 segments, each of which has the same 5 buttons to be switched off based on Row 43 in each segment.

 

[the researcher]

Maybe this helps - I am worried that I might not be explaining myself conclusively...http://rapidshare.com/files/357181332/Capture.JPG.html

 

[sykes]

Firstly, well done for asking for help!

Now that we've a little more info (!!!) I suggest we deal with just one of your "segments" and get that going first.

Can we go back to basics, and use this code to begin with:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$43" Then Exit Sub

Me.ToggleButton1.Enabled = False
Me.ToggleButton2.Enabled = False
Me.ToggleButton3.Enabled = False
Me.ToggleButton4.Enabled = False
Me.ToggleButton5.Enabled = False
         
End Sub

Copy and paste it into exactly the same place as before.

You'll need to replace the ToggleButton names with those associated with your D43 segment.

Then, if you change the value of D43 to anything different, the 5 buttons should all become disabled.
Let us know how it goes....

 

[the researcher]

Hi Sykes,

thanks again - this worked. nothing happened at first but when I changd D43 to something random it disabled all buttons for that segment. Changing it back had no effect.

 

[sykes]

Great
What does your new code look like? Can you post it so that we can see the TB names please.
Then I can make the necessary changes, to re-enable the buttons, based upon the value in D43.

 

[the researcher]

Hi Sykes,

it looks just like yours - the TB's were actually named 1-5 by coincidence:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$43" Then Exit Sub
Me.ToggleButton1.Enabled = False
Me.ToggleButton2.Enabled = False
Me.ToggleButton3.Enabled = False
Me.ToggleButton4.Enabled = False
Me.ToggleButton5.Enabled = False

End Sub


Here's a listing of what the other TB's are called (I can manage to rename anything you provide myself of course, just in case this helps you help me).

Segment 2: 6-10
Segment 3: 11-15
Segment 4: 16-20
Segment 5: 21-25
Segment 6:26-30
Segment 7:31-35
Segment 8:36-40
Segment 9:41-45
Segment 10:46-50

Thank you very much !

 

[sykes]

OK, can we make the whole code look thus, now:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$43" Then Exit Sub

Me.ToggleButton1.Enabled = False
Me.ToggleButton2.Enabled = False
Me.ToggleButton3.Enabled = False
Me.ToggleButton4.Enabled = False
Me.ToggleButton5.Enabled = False

Select Case Target.Value
    Case "online": Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True
    Case "CATI": Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True
    Case "IDI", "FGD": Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True: Me.ToggleButton4.Enabled = True: Me.ToggleButton5.Enabled = True
End Select
           
End Sub

....and change D43 to your various intended values - to test that the respective buttons become enabled after the change to D43.

 

[the researcher]

This is amazing! It works perfectly! Can I copy the same code for the other segments underneath the present one and change the references?

Also, D43 is determined by the first dropdown in the column; when I change the references by typing into D43 it works, but not if D43 changes as a result of the linked dropdown changing. This is not crucial, but it'd be very nice if it worked...

Many many thanks for your help with this! I owe you!

 

[sykes]

Hurrah and Huzzar! Very sorry - glass of wine, and fire took their toll. Have just woken up!

Will attack again tomorrow.

 

[sykes]

OK...............
Now that I know that the row 43 cell is being affected as a result of your DropDown boxes, there's a much better way of doing this, which is to fire the code as a result of changing the dropdown, and keeping the cell completely out of it.

This will make it smoother, and more robust. It will also allow for more functionality, more easily, in the future - should you wish to change things.

Two questions, and we'll easily be able to implement the code:
1. What's the name of the dropdown in segment 1, and what type is it? I'm guessing that it's an ActiveX control (as your buttons are), and that it's probably a ComboBox named ComboBox1?

2. Please say again ALL of the items in the dropdown list and in exactly the correct order - from top to bottom. I'm guessing that it's "None" followed by "Online", "CATI", "IDI" and "FGD" at the bottom?

Armed with this info we can quickly put your whole worksheet to bed, well - as far as this post's concerned, but I have a feeling that once you've seen the potential with VBA we'll be hearing quite a bit more from you !!!