Solved

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

Posted on 2014-07-30
4
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

615 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