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]

Morning Sykes!

"Firing Code" sonds well exciting!

The drop down is an ActiveX combo box called ComboBox1!

The order of items is exactly as you said: "None" (this actually disables part of my calculations much further down the sheet) followed by "Online", "CATI", "IDI" and "FGD".

Needless to say I am totally amazed by what is possible with this kind of knowledge (last time I checked decades ago there were only rubbish macros!) so I am getting myself some beginner's books on VBA (so I at least dont have to bug you with the boring stuff ) - what do you think of this one:

http://www.amazon.com/Mastering-VBA...=sr_1_1?ie=UTF8&s=books&qid=1267441396&sr=1-1

Man thanks once more!
<!-- / message --><!-- sig -->

 

[sykes]

That's great news. I'd got a reply ready in the hope that this was the case.

OK, firstly we need to delete all of the code so far, so please right-click the sheet's name tab again, select "View Code" (quick way of getting to the correct place

in the VBA editor) and delete the whole of:

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

Now we'll code the dropdown's click event, so that the code runs when the user selects a new entry from the list.

Select ALL of this code, then right_click (quickest way) and select "Copy" :

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

Select Case Me.ComboBox1.ListIndex
    Case 1: Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True
    Case 2: Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True
    Case 3, 4: Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True: Me.ToggleButton4.Enabled = True: Me.ToggleButton5.Enabled = True
End Select

Go back to the worksheet, and put it into "Design Mode" again.

Right-click the dropdown in segment 1, and select "View Code." You should see this:

Private Sub ComboBox1_Change()

End Sub

...if not select "ComboBox1" from the top left dropdown in the right hand pane, and "Change" from the right hand one.

Now paste in the code you copied off the web page (quickest way, right-click and select "Paste") in between the two lines, so that the whole lot looks thus:

Private Sub ComboBox1_Change()
Me.ToggleButton1.Enabled = False
Me.ToggleButton2.Enabled = False
Me.ToggleButton3.Enabled = False
Me.ToggleButton4.Enabled = False
Me.ToggleButton5.Enabled = False

Select Case Me.ComboBox1.ListIndex
    Case 1: Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True
    Case 2: Me.ToggleButton1.Enabled = True: Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True
    Case 3, 4: Me.ToggleButton2.Enabled = True: Me.ToggleButton3.Enabled = True: Me.ToggleButton4.Enabled = True: Me.ToggleButton5.Enabled = True
End Select
End Sub

Go back to the worksheet, de-select "Design Mode" and save the workbook.

You should now be able to select items from the dropdown, and the buttons will respond accordingly.

As long as this works to your satisfaction, we'll do the other afterwards, which will be fairly quick.

Let me know as soon as you can, because I'll only be available for a certain amount of time today, and it'd be great to get you sorted out.

 

[the researcher]

Works absolutely flawlessly! Thank you!

 

[sykes]

Excellent!
Now you need to repeat the process for the other segments, but changing the code to reflect the names of the associated ToggleButtons.
Is the drop down list content the same in segment 2, and do you wish the same result as far as how the TBs in segment 2 respond? If so, then copy this:

Me.ToggleButton6.Enabled = False
Me.ToggleButton7.Enabled = False
Me.ToggleButton8.Enabled = False
Me.ToggleButton9.Enabled = False
Me.ToggleButton10.Enabled = False

Select Case Me.ComboBox2.ListIndex
    Case 1: Me.ToggleButton6.Enabled = True: Me.ToggleButton7.Enabled = True
    Case 2: Me.ToggleButton6.Enabled = True: Me.ToggleButton7.Enabled = True: Me.ToggleButton8.Enabled = True
    Case 3, 4: Me.ToggleButton7.Enabled = True: Me.ToggleButton8.Enabled = True: Me.ToggleButton9.Enabled = True: Me.ToggleButton10.Enabled = True
End Select

Now go back to your sheet, select design mode, right-click the ComboBox in the second segment, select "View Code", and once again paste the copied code between the two lines. You should end up with:

Private Sub ComboBox2_Change()
Me.ToggleButton6.Enabled = False
Me.ToggleButton7.Enabled = False
Me.ToggleButton8.Enabled = False
Me.ToggleButton9.Enabled = False
Me.ToggleButton10.Enabled = False

Select Case Me.ComboBox2.ListIndex
    Case 1: Me.ToggleButton6.Enabled = True: Me.ToggleButton7.Enabled = True
    Case 2: Me.ToggleButton6.Enabled = True: Me.ToggleButton7.Enabled = True: Me.ToggleButton8.Enabled = True
    Case 3, 4: Me.ToggleButton7.Enabled = True: Me.ToggleButton8.Enabled = True: Me.ToggleButton9.Enabled = True: Me.ToggleButton10.Enabled = True
End Select
End Sub

Once again, come out of Design Mode, save the WB, and test the functionality.

Notice what I've changed:
1. The 5 ToggleButton numbers at the top of the code - to reflect the second segment's TB numbers.
2. The ComboBox number in the "Select Case" line (changed to the number of the ComboBox we're working on).
3. The ToggleButton numbers at the bottom (basically just add 5 to the previous code's numbers) - once again to reflect the TBs associated with the second segment.
As long as this works, you should be able to repeat the process for the rest of your segments.

Please let me know how you get on.

With regard to your book, I'm afraid that I'm completely self-taught, so can't make any recommendations. What I WOULD suggest though - as you feel you're getting some benefit from this great site (and will continue to do so - particularly if you do more with VBA) is that you consider buying the book from the Mr Excel shop:
http://www.mrexcel.com/store/index.php which is probably the best way of showing your thanks to the great folk at Mr Excel.

 

[the researcher]

Dear Sykes,

sorry, I had a mad day and couldn't work on this, but I have done it now and it all works great! What's more, I am imagining that I have a glimpse of what's possible and why and how...

I will certainly support Mr Exceland buy books here first rather than elsewhere!

Thanks again for your kind help and patience!

 

[the researcher]

All,

although I promised to leave you in peace for a bit after Sykes sorted my TB's out, there is perhaps one other thing you might be able to help with... I have scoured the net for a solution but not found one anywhere!

Every time I reopen the workbook after a save (this does not happen when I don't save before reopening), the listboxes in my sheet come out about half the size they were before... have you seen this before?

I tried fixing the size, tried all combinations of the Format Object menu and even tried a little piece of VBA I found elsewhere:

listbox1.width = 110

and

listbox1.width = combobox1.width

all to no avail, the latter of course because it only worked when the listbox value changed...

 

[sykes]

Glad the TB question's cleared up.

It sounds as if some code is setting the width of the list box, or (if mine is the only code on the whole workbook) that the original width of the listboxes need changing in their properties.

Try going into design mode again, right_clicking the listbox, and setting the width property (at the bottom) to 110 in there. This should "hard code" the width when you save the workbook.

With regard to your code only working when the listbox is changed - this will only be the case if you place the code in the listbox's _change event holder - as we've been doing with the ComboBoxes.You can get code to fire whenever you like, depending upon where you place it. For example, if you wish code to run whenever the workbook's opened, you place it in the workbook's _open event holder.
If you go into your VBA browser again, you should see your VBA workbook's name in the "Projects" list on the left hand side (if not use View/Project explorer).
Your workbook will have several objects displayed in the list - one of which will be "This Workbook." If you double-click on it, the workbook's code area will open up in the right-hand pane. You should see:

Private Sub Workbook_Open()

End Sub

If not, select "Workbook" from the left hand drop down, and "Open" from the right hand one.
Any code entered in here will run every time the workbook opens.
Put this between the lines:

 MsgBox "Good morning Researcher!!! " & Chr(10) & Chr(10) & "Welcome to " & Me.Name, 64, "                         " & Me.Name

, save the workbook, then re-open.

As you'll see it's not only important WHAT you put in code, but also WHERE it's placed.

 

[the researcher]

Morning Sykes,

thanks for that - hardcoding the width into the properties of the control I tried and it will still resize it to exactly 1/2 size each time it is saved!

I worked out that you have to set IntegralHeight = FALSE…

thanks for the instruction on code placement - understood! Used the MsgBox to put a welcome message and some credits for you in!

 

[sykes]

Hi
Thanks for that. I couldn't access the image (or it didn't transfer onto the website) but not to worry.
Glad to have been of help.

Enjoy the VBA !!