Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-07-30
4
Medium Priority
?
331 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 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 33

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

Independent Software Vendors: 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 will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 use a scrolling table in Microsoft Excel using the INDEX function.

670 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