?
Solved

Fixing a Checkbox Macro

Posted on 2016-07-23
8
Medium Priority
?
17 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
Industry Leaders: 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 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