UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Code Works Sporadically, Office 2013    
 
   
nmartin1230
post Nov 27 2017, 03:47 PM
Post#1



Posts: 447
Joined: 20-May 14



I have those code(with the help of some forum members) that is supposed to fire on before print. It seems to only work when the sheet was originally open then if any corrections are made, it doesn't check again before the user prints. Here is the code
CODE
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheet1.Unprotect Password:="Bryce@09"
    Dim intCol As Integer
    Dim response As VbMsgBoxResult
    If ActiveSheet.Name = "Sheet1" Then
        Application.EnableEvents = False
        For intCol = 2 To 14
            If IsEmpty(Cells(13, intCol)) = False And IsEmpty(Cells(25, intCol)) = True Then
            response = MsgBox("Warning!  There is a Task Code but no Job Number in column " & Chr(64 + intCol), vbCritical + vbOKCancel + vbDefaultButton2, "Missing Data")
            ElseIf response = vbCancel Then
            Cancel = True
            Else
            End If
        Next intCol
        Application.EnableEvents = True
    End If
    If ActiveSheet.Name = "Sheet1" Then
        Application.EnableEvents = False
        For intCol = 2 To 14
            If IsEmpty(Cells(25, intCol)) = False And IsEmpty(Cells(13, intCol)) = True Then
            response = MsgBox("Warning! There is a Job Number but no Task Code in column " & Chr(64 + intCol), vbCritical + vbOKCancel + vbDefaultButton2, "Missing Data")
            ElseIf response = vbCancel Then
            Cancel = True
            Else
            End If
        Next intCol
        Application.EnableEvents = True
    End If
        If Range("B16") < 8 Then
                response = MsgBox("Make sure your total hours equal 8 or more.", vbCritical + vbOKCancel + vbDefaultButton2, "Hours")
        If response = vbCancel Then
                Cancel = True
        End If
    
            If Range("B16") > 8 Then
                response = MsgBox("Your total hours are over 8, are you working overtime/credit time today?", vbCritical + vbOKCancel + vbDefaultButton2, "Total Hours")
             If response = vbCancel Then
                Cancel = True
                With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = False
                End With
            End If
             End If
    End If
    Sheet1.Protect Password:="Bryce@09"
End Sub

Any help would be greatly appreciated. iconfused.gif
Go to the top of the page
 
doctor9
post Nov 27 2017, 05:16 PM
Post#2


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


nmartin1230,

After setting Application.EnableEvents to False, the BeforePrint event will no longer fire. My guess is that this bit has the mistake:


CODE
             If response = vbCancel Then
                Cancel = True
                With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = False  '<-----This should probably be True.
                End With
            End If


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
nmartin1230
post Nov 28 2017, 09:02 AM
Post#3



Posts: 447
Joined: 20-May 14



Well, that bit of code was originally set to True but I was playing around with it trying to get it to work. I must not have changed it back before I posted the code. It all works individually but it doesn't seem to loop through all code.(Not sure if loop is the word I want to use) When the user clicks print if one portion of the codes fires and the user hits cancel to fix the error, then after the fix the error the code doesn't run again. I've included the Excel workbook so you can see what I am talking about. Thanks for your help!
Attached File  Excelzip.zip ( 37.26K )Number of downloads: 0
Go to the top of the page
 
doctor9
post Nov 28 2017, 09:05 AM
Post#4


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


nmartin,

Can you explain why you are turning off events with your code?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
nmartin1230
post Nov 28 2017, 01:26 PM
Post#5



Posts: 447
Joined: 20-May 14



Honestly, it was suggested by someone in this forum to do. At the time, when I started this little project part of this code was under the OnChange event. I didn't like that the code fired every time someone made a change to the sheet. Would you suggest taking out those lines where I am disabling the events?
Go to the top of the page
 
doctor9
post Nov 28 2017, 01:40 PM
Post#6


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


nmartin,

Yes, I'd definitely remove all references to .EnableEvents in this particuar subroutine. Just use the apostrophe to comment them out at first.

> I didn't like that the code fired every time someone made a change to the sheet.

Well, that's when the OnChange event fires, after all. smile.gif

For your Change event code, you can test which row/column the change was made in. If you only want to run the code if the changed cell is between B4 and B9 for example, you can put that IF test first.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
nmartin1230
post Nov 28 2017, 01:54 PM
Post#7



Posts: 447
Joined: 20-May 14



Okay, so I commented out all the .EnableEvents as you suggested. It works better but for some reason, it is not catching the last bit of code. The line that checks if cell B16 is greater than 8. It checks the line before that checks if cell B16 is less than 8. I tried combining the two with an Else but that didn't work either.
Go to the top of the page
 
doctor9
post Nov 28 2017, 03:14 PM
Post#8


UtterAccess Editor
Posts: 17,921
Joined: 29-March 05
From: Wisconsin


nmartin1230,

The part that checks if B16 is greater than 8 is INSIDE the True section for if B16 is lees than 8, so it will never test true. Your indentation might be throwing you off.

CODE
    If Range("B16") < 8 Then
        response = MsgBox("Make sure your total hours equal 8 or more.", vbCritical + vbOKCancel + vbDefaultButton2, "Hours")
        
        If response = vbCancel Then
            Cancel = True
        End If
    
        If Range("B16") > 8 Then
            response = MsgBox("Your total hours are over 8, are you working overtime/credit time today?", vbCritical + vbOKCancel + vbDefaultButton2, "Total Hours")
            
            If response = vbCancel Then
                Cancel = True
                Application.Undo
            End If
        End If
    End If


The likeliest solution is to move the whole "If it's greater than 8" test AFTER the "If it's less than 8" block.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
nmartin1230
post Nov 28 2017, 04:10 PM
Post#9



Posts: 447
Joined: 20-May 14



That worked!!! I think you were correct in saying that my indentation was messing me up. Thanks again for all your help!! uarulez2.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 02:05 AM