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!

 

[sykes]

Hi, and welcome to actually posting!! Many folk don't use the search facility at all, and just post their problem, so well done for helping yourself so much in the past, and thanks for your praise for all the good folk on this site. I totally agree - it's a great source of information for Excellers no matter what their level of ability.

In answer to your latter question, yes this will be possible - it's just finding the neatest solution for you.

Question - are you using VBA? I'm guessing that this will need to be a VBA solution.

 

[sykes]

Well..........................I've had a look anyway and come up with the following...

I've assumed that your "Cell" is A1 - change this in the code to suite.

I've assumed that if A1's empty that you wish all of the buttons to be disabled.

I've also assumed that your ToggleButtons are named using Excel's standard nomenclature ("ToggleButton1", "ToggleButton2" etc). If your buttons are named differently then you'll need to reflect this in your changes. It doesn't matter what the buttons' captions are - just the actual names. To check the name, go into design mode, click the button and look at the top left drop-down on the spreadsheet.
In order for the code to work, it needs to be placed in the worksheet's change event, so copy it, right-click the sheet's name tab at the bottom, select "View code", and in the blank pane on the right-hand side, paste in the code.

Save the workbook, then have a go at changing A1 on the worksheet. You'll need to click into another cell afterwards for the code to fire.

Even though you've posted using Uppercase letters, I've assumed that your users may use either upper or lower, so this isn't case sensitive.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$1" Then Exit Sub

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

 With Me
     If Target.Value = "" Or Target.Value <> "B" And Target.Value <> "b" And Target.Value <> "C" And Target.Value <> "c" And Target.Value <> "D" And Target.Value <> "d" And Target.Value <> "E" And Target.Value <> "e" Then Exit Sub
    If Target.Value = "B" Or Target.Value = "b" Then .ToggleButton1.Enabled = True: .ToggleButton2.Enabled = True
    If Target.Value = "C" Or Target.Value = "c" Then .ToggleButton1.Enabled = True: .ToggleButton2.Enabled = True: .ToggleButton3.Enabled = True
    If Target.Value = "D" Or Target.Value = "d" Or Target.Value = "E" Or Target.Value = "e" Then .ToggleButton2.Enabled = True: .ToggleButton3.Enabled = True: .ToggleButton4.Enabled = True: .ToggleButton5.Enabled = True
 End With
            
End Sub

It can probably be shortened by using CASE or many other methods come to that, but it should at least do what you seek.

Good luck !!

 

[the researcher]

Thanks for helping me!

I am afraid I have no VBA skills at all... I have copied code into the VBA console before (from here, I think ) , but that is it. Will learn though if that is what's required.

I just used the menus in Excel to insert the ActiveX controls and get them to do hat I need them to, which is basic data entry (simply because the drop-downs are more accessible than the validation ones...

thanks again!

 

[the researcher]

Hi Sykes,

I only just saw your response - thanks a lot - will give it a go right now and let you know how I get on!

 

[sykes]

Have played around a bit more, and the following code is a bit less "Schoolboy" and a bit cleaner.

It assumes that ToggleButtons 1 to 5 are the only ones on your worksheet - otherwise any other TBs will be deactivated as well.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$1" Then Exit Sub

Dim obj As OLEObject
Dim str As String

For Each obj In Sheets("sheet1").OLEObjects
If TypeName(obj.Object) = "ToggleButton" Then obj.Enabled = False
Next

str = Target.Value

Select Case str
Case "B" To "E", "b" To "e"
GoTo continue
End Select
Exit Sub

continue: With Me
    Select Case str
        Case "B", "b"
            .ToggleButton1.Enabled = True: .ToggleButton2.Enabled = True
        Case "C", "c"
            .ToggleButton1.Enabled = True: .ToggleButton2.Enabled = True: .ToggleButton3.Enabled = True
        Case "D", "d", "E", "e"
            .ToggleButton2.Enabled = True: .ToggleButton3.Enabled = True: .ToggleButton4.Enabled = True: .ToggleButton5.Enabled = True
    End Select
End With
           
End Sub

Let us know if you need more help with the VBA thing. I know it can be a bit daunting at first, and sometimes it's better to just ask, if it's not working, or you don't understand how to implement it.

Hope this works for you.

 

[VoG]

Hi again Sykes.

No criticism intended or implied but wouldn't this be simpler (and avoid the dreaded GoTo)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim obj As OLEObject
If Target.Address <> "$A$1" Then Exit Sub
For Each obj In Me.OLEObjects
    If TypeName(obj.Object) = "ToggleButton" Then obj.Enabled = False
Next obj
Select Case UCase(Target.Value)
    Case "B": Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True
    Case "C": Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True
    Case "D" To "E": Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True: Me.ToggleButton4.Enabled = True: Me.ToggleButton5.Enabled = True
End Select
End Sub

I may be missing something, in which case don't hesitate to tell me

 

[sykes]

Peter - Thanks very much for that.

It's my first attempt ever with Select Case - I've always been meaning to look at it, but until now have never got round to it.
Your code is much more concise.
I also like the use of UCase to force up the lowercase possibility, and only have to test for UpperCase.

Like many folk here, I'm always learning.

Thanks again.

 

[the researcher]

Dear Sykes, dear Peter,

I have obviously completely underestimated what it takes to get the code you have kindly provided to work as I have been trying for hours and just cant get it to disable the buttons...

Here some more info I probably shouldn't have omitted:

- The input values aren't actually A,B,C,D,E - I used these to distinguish between the different states of the source cell because I didn't think the fact that there were actual words in it mattered. The words are "none", "Online", "CATI", "IDI" and "FGD". I did try to replace the letters in the code with the words of course; I also changed, to test, the source cell to those letters A-E, also without success.

The sheet is (to my mind at least) rather complex; it uses 50 togglebuttons, 30 dropdown comboboxes and 10 comboboxes. A combination of these grouped in 10 identical segments (organised in 1 column each) determines the variables I need to calculate prices for a service out of 17 pricelists elsewhere in the workbook.

Would you mind giving me a dummy guide please?

I appreciate the help very much!

 

[VoG]

Perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
Dim obj As OLEObject
If Target.Address <> "$A$1" Then Exit Sub
For Each obj In Me.OLEObjects
    If TypeName(obj.Object) = "ToggleButton" Then obj.Enabled = False
Next obj
Select Case UCase(Left(Target.Value), 1)
    Case "B": Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True
    Case "C": Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True
    Case "D" To "E": Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True: Me.ToggleButton4.Enabled = True: Me.ToggleButton5.Enabled = True
End Select
End Sub
[[code]