• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

How to de-activate certain macro buttons based on a cell value?

I have tried using code I found online, but keep getting errors and the code does not run.

I don't know code so any help is appreciated.

I am trying to de-activate certain macro buttons on sheet “Step 4 - 15 Questions - 1 of 2” based on the value in cell ‘C7”.
For example if cell “C7” has 2 in it then buttons 1,3,4, and 5 are deactivated.
For example if cell “C7” has 3 in it then buttons “1,2,4 and 5 are deactivated.
And so on…
RootCause.xlsm
0
cssc1
Asked:
cssc1
  • 2
2 Solutions
 
Glenn RayExcel VBA DeveloperCommented:
An ActiveX control like your combo box will not trigger a Worksheet_Change event necessary to automate the change in the behavior and appearance of the buttons.

However, if the box were replaced with a merged cell with Data Validation on your same group of values (on the Causal Factors sheet) then this would work and the requested activation/deactivation could be achieved.

I've modified your workbook in just this way.  I added a Worksheet_Change event to check the value in C7 and change the appearance of the five buttons as you noted (greying their text to visually indicate inactive).  Then, each macro has another parallel test on C7 to determine if that macro should be allowed to run based on the value.  For example, Macro1, assigned to Button 1 is now:
Sub Macro1()
    If Range("C7").Value = "Error" Or Range("C7").Value = "Mistake" Then Exit Sub
    Range("C1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Open in new window


Regards,
-Glenn
EE-RootCause.xlsm
0
 
Rob HensonFinance AnalystCommented:
In addition to Gelnn's comment, I was thinking along similar lines to include a cell value check at the start of each macro. If the check fails the user can click on the button as many times as they like but it won't run the macro.

However, I wouldn't use the Worksheet_Change event element. Once you trigger a macro with the Worksheet_Change event you lose your Undo history.

Thanks
Rob H
0
 
Glenn RayExcel VBA DeveloperCommented:
Echoing Rob:  it's true that a Worksheet_Change event does not preserve/record an Undo history.  But in this case, it really isn't a factor since the drop list change is only being used to affect the behavior of the macro buttons and not change a data set.
0
 
cssc1Author Commented:
Thanks again Glen and Rob.

I learn new excel stuff because of you guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now