Solved

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

Posted on 2014-07-30
4
301 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 400 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 33

Assisted Solution

by:Rob Henson
Rob Henson earned 100 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

821 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