Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-07-30
4
Medium Priority
?
334 Views
Last Modified: 2014-07-31
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
Comment
Question by:cssc1
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1600 total points
ID: 40230802
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
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 400 total points
ID: 40231632
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40232028
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
 

Author Closing Comment

by:cssc1
ID: 40232343
Thanks again Glen and Rob.

I learn new excel stuff because of you guys.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question