Solved

Fixing a Checkbox Macro

Posted on 2016-07-23
8
12 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

805 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