Solved

Fixing a Checkbox Macro

Posted on 2016-07-23
8
10 Views
Last Modified: 2016-07-23
EE Pros,

I have tried to replicate a worksheet macro, where in Column C, when you doubleclick, it cycles through 4 stages of Identification ( Blank, A check mark, A Question mark and a X).  I've included the actual workbook/sheet and previous code that worked.

I'm using this as a quick, "check sheet", to have someone using it, declare the status of certain statements.

Any help would be most appreciated.  Thank you in advance.

B.
Checkmarks.xlsm
0
Comment
Question by:Bright01
  • 5
  • 3
8 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41725623
Bright01,

This part of your macro seems to say that the code only runs where the value in col A =1

In the example file a checkmark is places in col c row 4 because the the value in col A row 4 = 1

If ActiveCell.Offset(0, -2).Value = "1" Then
            Togglecheckmarks Target
            Cancel = True
        End If

Also the following line
Application.EnableEvents = False fires after after you double click the first cell in the range thereby stopping further events from firing - i.e. your macros will stop working after the first pass.

I wonder why this is designed in ?

Rgds,
Jonathan
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41725625
To add to above comment the
Application.EnableEvents = False is called only if the Target is Blank.

By removing

1.
If ActiveCell.Offset(0, -2).Value = "1" Then

AND
2.
Application.EnableEvents = False

The macros seem to work as you have described. I will attach my updated file.

Rgds,
Jonathan
Checkmarks.xlsm
0
 

Author Comment

by:Bright01
ID: 41725626
Datrias,

Thank you very much for your comments.  I remember now that we had originally put a "1" in column A to demonstrate that the macro only ran when there was a "1" in that column.  I'm good with that.  I had forgotten that we used that as a way to designate the row should be active.  So you are right and my sample is not right.  Put a 1 in each row, Col. A for each of the cells that should be active.  

Also, it shouldn't stop after the first check.  So that should also be changed.

I wish I could do this on my own but I'm not that good at diagnosing problems.

Thanks,

B.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41725628
Bright01,

Practice & Patience go along way with this stuff !

Do you want me to make any changes to the original file and send it back to you?

Rgds,
Jonathan
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Bright01
ID: 41725629
That would be great.  I just downloaded your fix and couldn't get it to work on a doubleclick.  It also came across as an XLSX and had to change it to an XLSM?

Thanks!
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41725633
XLSM have macro execution turned on by default.

I will edit the original and upload it.
0
 
LVL 7

Accepted Solution

by:
Jonathan Kelly earned 500 total points
ID: 41725635
I have commented out the line
Application.EnableEvents
and put a comment next to it.

Seems to work ok for me.

You might need to enable macro's under Security.

Rgds,
Jonathan
Checkmarks.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 41725746
Fantastic!  Thank you very much.  Works perfectly.

B.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now