Solved

Fixing a Checkbox Macro

Posted on 2016-07-23
8
14 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
[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
  • 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
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!

 
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

Technology Partners: 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 descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

740 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