+ Reply to Thread
Results 1 to 23 of 23

Active-X checkbox with macro won't work in top 1 inch of page in Page View

  1. #1
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Active-X checkbox with macro won't work in top 1 inch of page in Page View

    I have the following macro set to run a series of checkboxes. The checkboxes work as designed ONLY when they are located below the 1" mark on the vertical ruler. I doesn't matter if a row is expanded or multiple rows move the checkbox that low, it seems to be location related. I need the checkbox with the macro to work closer to the top of the page. This is only a problem with Page View, the prefered view for this product. Excel Office Pro Plus 2010
    What is wrong?

    Please Login or Register  to view this content.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    The code won't tell us what the problem is. We would need your actual file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

    Please explain what "doesn't work" means.
    • Does the code run?
    • Does it run but do nothing?
    • Does it produce error messages? If so, what do the messages say?
    • Does it produce unexpected/wrong results? If so, how do the results differ from what you expect?
    • Does it hang?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    The checkbox that is located above the 1" mark does not function at all. You cannot check or uncheck the box and the macro function does not occur (it is coded to insert N/A into certain fields and protect the sheet, but it does not do either). It does not produce an error message or produce any other results. It does not hang, you can click and click the checkbox but nothing happens at all. The subsequent checkboxes below the 1" mark work properly and all results are as expected.

    Thank you!

  4. #4
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Now I've also noticed that if I make some of the rows so high that some of the fields move to the next page that the the first check box on the continued page doesn't work as I've described above and some sort of page break follows

  5. #5
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Here is a shortened version of the file. The first page always worked, but you can see the problem on the second page.
    Attached Files Attached Files

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    I don't see any problem on the second page. There are no check boxes above the 1" mark and the first check box works fine.

    Further, I cannot make any changes to experiment because both the VBA and the sheet are protected with passwords.

  7. #7
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Hmm. Are you in page view? This problem only happens in page view. The password for the page and code is secret. If you move the first line with checkbox above the 1" mark it should happen. If it doesn't for you, then maybe this is something local for me on my version??

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    I am in the page view, because that's how the file opened. I can't move the first line because the sheet is protected.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Your profile say MS Office Version 7, which isn't an Office version. You might want to correct your profile.

  10. #10
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Thank you, that should have been 10. I have corrected it.

  11. #11
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    the password is "secret"

    It must have sounded like I was trying to keep the password from you. It's a dumb password, but it was a dummy placeholder and I just went with it. Sorry for the confusion!

  12. #12
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Quote Originally Posted by SaraMoulton View Post
    Thank you, that should have been 10. I have corrected it.
    We still have a disconnect. That is your Windows version, not your Excel version.

    Quote Originally Posted by SaraMoulton View Post
    the password is "secret"
    Yeah, I thought you were telling me that you could not reveal the password. So I unprotected the sheet but "secret" did not get me into the VBA code.

    I adjusted the row height of row 66 to force the first checkbox on the second page to be above the 1" mark but it still worked fine. I tried deleting that row but then got an error message about setting the Locked property of a range, and about merged cells. (We discourage using merged cells.)

    Can you set the file up in the same way that gives you an error and reattach it?

  13. #13
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Ok, I'm sorry. My Excel version is 14.0.7232.500 64-bit
    I have changed the password on the code to match the sheet, "secret" and reuploaded it. I'll see if I can follow what you're asking me to try. I feel like I've tried everything!

    I so appreciate you hanging in there to help me get to the bottom of this.

    Sara
    Attached Files Attached Files

  14. #14
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    I am getting it to work although sometimes I get erratic behavior that looks like a Excel bug or file corruption.

    This seems to depend on where I have scrolled the view. Sometimes it works perfectly normally. At other times, when I click the box nothing happens. Other times, when I click the box and the check mark doesn't change but the cells change.

    As you described, I do not have this problem in Normal view.

    I can't figure out what the problem is but I am pretty sure you didn't do anything wrong. How do you use this, and why is Page Layout the preferred view?

    By the way your code is very labor-intensive. It could be coded to use a single Function to identify the range to change (scan down until it finds the Comments row), rather than hard-coding every range for every checkbox.

    I would experiment with Forms checkboxes, but that would be a lot of work and I don't know if it would fix it.

  15. #15
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    So, if it's a corruption, it seems to copy from file to file because this is a shortened version of a 20-page file with 58 checkboxes, and the problem carried with it. What would be the best way to get rid of the corruption?
    What do you mean by "..the cells change"?
    I am very new to macros. It took me awhile to get someone to write this code that works for me; if there is something simpler I'm happy to give it a try. I don't know enough about code to begin to rewrite it.
    This file will be used in manufacturing and will likely often be printed into pages, so having the page view work is important for the esthetics and for those folks who will print and then fill it out by pen.

  16. #16
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    I am not 100% sure it is a corruption. The only way to be sure is to recreate the file, which for this file would be a lot of work. And it might not fix it.

    Quote Originally Posted by SaraMoulton View Post
    What do you mean by "..the cells change"?
    When I click the box, the cells fill up with N/A but the check mark doesn't always appear in the box.

    The way the file is viewed on the screen doesn't affect how it prints. If you use Normal view, the printed pages will still be the same.

    I don't have any suggestions on how to fix this other than a wholesale redesign.

  17. #17
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Thank you for your help. I may just have to start from scratch. Is there an easy way to fix the code as you suggested, so that it fills N/A until it sees the Comments row? Can you please explain why merged cells is discouraged?

  18. #18
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    I was going to do this for you and noticed that you have 9 checkboxes on the worksheet, but the code addresses 10-58. Here is the general approach. However, this does not fix your problem. I am still thinking about that.

    Add Option Explicit. I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors.

    Declare your password at the top of the module. That way you only have to change it in one place if it changes.

    You will have one copy of a sub that updates the cells in columns D and E when any checkbox is clicked.

    Each checkbox click handler will just have one line that calls the other sub. For this to work, the checkbox has be located within the cell in column E on the row where it says "Check box if standard is not applicable." This is how the code knows what rows to change.



    Example:

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Thank you very much! Having to change the PW only once would be very useful, as well as being able to add and delete rows without reworking all the code. I appreciate the Declare Variables tip.

    So, do I need to link the check box with the E cell on the specified line, or just place it there? Last time I tried to link an active-X checkbox it hung up indefinately. Not sure if it was something I did or if linking Active-X checkboxes is just a no-no.

    Also, there are actually 58 checkboxes ( I shortened the file so that it was more manageable for testing). For the section of code at the bottom, do I need to repeat that part for each checkbox 1-58? I noticed you included check box 1, 2 and 6...

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    This is working really well, and I see that I do need to add each checkbox. No problem, it's so much simpler than the old code. Thank you very much.

  21. #21
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Well, it was working really well! Then at checkbox 38 it stopped working and even checkboxes 1-36 stopped working. The Msg "Cannot find Comments line for checkbox at row " does not appear when it's missing. That's what happened at checkbox 38. I added the comment row at the bottom of that section, and then all the checkboxes and code sopped working and the debugger flagged:

    Please Login or Register  to view this content.
    I'm not sure what happened....

  22. #22
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    21

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Closed and reopened, got a scary security message about disabling macros. Should I worry? But the code is working again, although I didn't save so I only have it coded to checkbox 14 or so.

  23. #23
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Active-X checkbox with macro won't work in top 1 inch of page in Page View

    Sorry, I have had limited time to check on the forum today.

    The checkbox does not need to be linked to a cell. The code is based on the checkbox's physical location. That's why it need to be inside the correct cell.

    I see the problem with the Comments missing. It looks for the next one, so if one is missing it just skips to the next group. I'll look at that when I get some time.

    Still trying to figure our your original problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA help -- after macro is run, page layout view changes
    By marshak in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2019, 09:36 PM
  2. [SOLVED] Hide page breaks in page layout view
    By Solvax in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2018, 01:25 PM
  3. Replies: 2
    Last Post: 01-16-2016, 03:26 PM
  4. Replies: 2
    Last Post: 08-07-2015, 09:01 AM
  5. Replies: 2
    Last Post: 10-22-2014, 03:10 PM
  6. Page setup, view page in 3 equal sections?
    By JapanDave in forum Excel General
    Replies: 0
    Last Post: 07-13-2010, 08:03 PM
  7. Page View - Page numbering
    By dgkindy in forum Excel General
    Replies: 3
    Last Post: 05-19-2009, 11:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1