I have a workbook that has to be filled out at the end of each employee's
shift. I have a macro built in so that it is supposed to verify that all
fields are filled in before they save. I have a macro built in that reminds
them to check certain devices they are responsible for. And i am trying to
put one in so that it clears data in all cells within certain ranges when the
workbook is opened. I am getting errors when i open the workbook and it does
not clear anything. And my macro that check s to see if cells are filled in
is not working at all. Can someone help me? I will post the code i have.

WORKBOOK :

Option Explicit

'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Call ChkData
' If CancelA = True Then Cancel = True
'End Sub

Private Sub Workbook_Open()
Dim RngName As Variant
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
Range(RngName).ClearContents
Next RngName
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Double check everything before you save!"
Dim res As Long
res = MsgBox(prompt:="Did you check all of the SEMs?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Did you check all of the NC1500s?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Have you forgotten to validate your timestamps?", _
Buttons:=vbYesNo)
Cancel = res = vbYes
End Sub


MODULE 2:

Option Explicit
Public CancelA As Boolean
Sub ChkData()
Dim RngName As Variant
Dim Msg As String
Dim Designation As String
CancelA = False
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
If Application.CountA(Range(RngName)) < Range(RngName).Count Then _
GoTo ErrorInData
Else
If Application.CountA(Range(RngName)) < 1 Then _
GoTo ErrorInData
End If
Next RngName
Exit Sub
ErrorInData:
CancelA = True
Select Case RngName
Case "EOSV1": Designation = "EOS"
Case "SEMErrorsV1": Designation = "SEM Errors"
Case "DEVLogOlivetteV1": Designation = "DEV Log for Olivette"
Case "DEVLogOverlandV1": Designation = "Dev Log for Overland"
Case "NMXNSGV1": Designation = "NMX for the NSG network"
Case "NMXSIMULTRANSV1": Designation = "NMX for the Simultrans network"
End Select
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
Msg = "You have not completely filled out the '" & Designation & "'
tab! You must complete the entire report before you save."
Else
Msg = "You have not noted any information about the '" & Designation
& "'. If there were no major alarms, please note so."
End If
MsgBox Msg, 16, "Data Error"
End Sub

Kasey
The first macro is remarked out. This means Excel doesn't see it and it
will never run. Go into the code and remove the apostrophe at the far left
of each line of that macro. I sent you that code and I'm sending you the
corrected code. HTH Otto
"kcdonaldson" wrote in message
...
I have a workbook that has to be filled out at the end of each employee's
shift. I have a macro built in so that it is supposed to verify that all
fields are filled in before they save. I have a macro built in that
reminds
them to check certain devices they are responsible for. And i am trying to
put one in so that it clears data in all cells within certain ranges when
the
workbook is opened. I am getting errors when i open the workbook and it
does
not clear anything. And my macro that check s to see if cells are filled
in
is not working at all. Can someone help me? I will post the code i have.

WORKBOOK :

Option Explicit

'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Call ChkData
' If CancelA = True Then Cancel = True
'End Sub

Private Sub Workbook_Open()
Dim RngName As Variant
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
Range(RngName).ClearContents
Next RngName
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Double check everything before you save!"
Dim res As Long
res = MsgBox(prompt:="Did you check all of the SEMs?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Did you check all of the NC1500s?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Have you forgotten to validate your timestamps?", _
Buttons:=vbYesNo)
Cancel = res = vbYes
End Sub


MODULE 2:

Option Explicit
Public CancelA As Boolean
Sub ChkData()
Dim RngName As Variant
Dim Msg As String
Dim Designation As String
CancelA = False
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
If Application.CountA(Range(RngName)) < Range(RngName).Count
Then _
GoTo ErrorInData
Else
If Application.CountA(Range(RngName)) < 1 Then _
GoTo ErrorInData
End If
Next RngName
Exit Sub
ErrorInData:
CancelA = True
Select Case RngName
Case "EOSV1": Designation = "EOS"
Case "SEMErrorsV1": Designation = "SEM Errors"
Case "DEVLogOlivetteV1": Designation = "DEV Log for Olivette"
Case "DEVLogOverlandV1": Designation = "Dev Log for Overland"
Case "NMXNSGV1": Designation = "NMX for the NSG network"
Case "NMXSIMULTRANSV1": Designation = "NMX for the Simultrans
network"
End Select
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
Msg = "You have not completely filled out the '" & Designation & "'
tab! You must complete the entire report before you save."
Else
Msg = "You have not noted any information about the '" &
Designation
& "'. If there were no major alarms, please note so."
End If
MsgBox Msg, 16, "Data Error"
End Sub

Kasey
You mentioned to me in your previous emails that the errors occurring
with the second macro were related to having merged cells. That macro
clears a number of cells in your file and apparently some or all of those
cells are parts of numerous merged sets of cells. This will produce errors.
The best thing you can do is to eliminate all cell merging in your file and
in all future files. I gave you a procedure for attaining the same
appearance as merged cells without actually merging any cells. HTH Otto
"Otto Moehrbach" wrote in message
...
Kasey
The first macro is remarked out. This means Excel doesn't see it and
it will never run. Go into the code and remove the apostrophe at the far
left of each line of that macro. I sent you that code and I'm sending you
the corrected code. HTH Otto
"kcdonaldson" wrote in message
...
I have a workbook that has to be filled out at the end of each employee's
shift. I have a macro built in so that it is supposed to verify that all
fields are filled in before they save. I have a macro built in that
reminds
them to check certain devices they are responsible for. And i am trying
to
put one in so that it clears data in all cells within certain ranges when
the
workbook is opened. I am getting errors when i open the workbook and it
does
not clear anything. And my macro that check s to see if cells are filled
in
is not working at all. Can someone help me? I will post the code i have.

WORKBOOK :

Option Explicit

'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Call ChkData
' If CancelA = True Then Cancel = True
'End Sub

Private Sub Workbook_Open()
Dim RngName As Variant
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
Range(RngName).ClearContents
Next RngName
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Double check everything before you save!"
Dim res As Long
res = MsgBox(prompt:="Did you check all of the SEMs?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Did you check all of the NC1500s?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Have you forgotten to validate your timestamps?", _
Buttons:=vbYesNo)
Cancel = res = vbYes
End Sub


MODULE 2:

Option Explicit
Public CancelA As Boolean
Sub ChkData()
Dim RngName As Variant
Dim Msg As String
Dim Designation As String
CancelA = False
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
If Application.CountA(Range(RngName)) < Range(RngName).Count
Then _
GoTo ErrorInData
Else
If Application.CountA(Range(RngName)) < 1 Then _
GoTo ErrorInData
End If
Next RngName
Exit Sub
ErrorInData:
CancelA = True
Select Case RngName
Case "EOSV1": Designation = "EOS"
Case "SEMErrorsV1": Designation = "SEM Errors"
Case "DEVLogOlivetteV1": Designation = "DEV Log for Olivette"
Case "DEVLogOverlandV1": Designation = "Dev Log for Overland"
Case "NMXNSGV1": Designation = "NMX for the NSG network"
Case "NMXSIMULTRANSV1": Designation = "NMX for the Simultrans
network"
End Select
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
Msg = "You have not completely filled out the '" & Designation &
"'
tab! You must complete the entire report before you save."
Else
Msg = "You have not noted any information about the '" &
Designation
& "'. If there were no major alarms, please note so."
End If
MsgBox Msg, 16, "Data Error"
End Sub

Shortly after I posted this i got your email. I fixed the problem with the
apostrophe and i fixed the global error. I am now having an issue where most
of the cells will clear after the workbook oipen macro but on a couple of
sheets there are still several cells that still don't clear. I don't get an
error stating that they're locked or anything, it just doesn't clear them. is
there anything that i can do to correct this? Does excel have a problem with
all of the range names i have to use or can i fix this? I'll email this to
you also, Otto.

"Otto Moehrbach" wrote:

Kasey
You mentioned to me in your previous emails that the errors occurring
with the second macro were related to having merged cells. That macro
clears a number of cells in your file and apparently some or all of those
cells are parts of numerous merged sets of cells. This will produce errors.
The best thing you can do is to eliminate all cell merging in your file and
in all future files. I gave you a procedure for attaining the same
appearance as merged cells without actually merging any cells. HTH Otto
"Otto Moehrbach" wrote in message
...
Kasey
The first macro is remarked out. This means Excel doesn't see it and
it will never run. Go into the code and remove the apostrophe at the far
left of each line of that macro. I sent you that code and I'm sending you
the corrected code. HTH Otto
"kcdonaldson" wrote in message
...
I have a workbook that has to be filled out at the end of each employee's
shift. I have a macro built in so that it is supposed to verify that all
fields are filled in before they save. I have a macro built in that
reminds
them to check certain devices they are responsible for. And i am trying
to
put one in so that it clears data in all cells within certain ranges when
the
workbook is opened. I am getting errors when i open the workbook and it
does
not clear anything. And my macro that check s to see if cells are filled
in
is not working at all. Can someone help me? I will post the code i have.

WORKBOOK :

Option Explicit

'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Call ChkData
' If CancelA = True Then Cancel = True
'End Sub

Private Sub Workbook_Open()
Dim RngName As Variant
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
Range(RngName).ClearContents
Next RngName
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Double check everything before you save!"
Dim res As Long
res = MsgBox(prompt:="Did you check all of the SEMs?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Did you check all of the NC1500s?", _
Buttons:=vbYesNo)
Cancel = res = vbNo
res = MsgBox(prompt:="Have you forgotten to validate your timestamps?", _
Buttons:=vbYesNo)
Cancel = res = vbYes
End Sub


MODULE 2:

Option Explicit
Public CancelA As Boolean
Sub ChkData()
Dim RngName As Variant
Dim Msg As String
Dim Designation As String
CancelA = False
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
If Application.CountA(Range(RngName)) < Range(RngName).Count
Then _
GoTo ErrorInData
Else
If Application.CountA(Range(RngName)) < 1 Then _
GoTo ErrorInData
End If
Next RngName
Exit Sub
ErrorInData:
CancelA = True
Select Case RngName
Case "EOSV1": Designation = "EOS"
Case "SEMErrorsV1": Designation = "SEM Errors"
Case "DEVLogOlivetteV1": Designation = "DEV Log for Olivette"
Case "DEVLogOverlandV1": Designation = "Dev Log for Overland"
Case "NMXNSGV1": Designation = "NMX for the NSG network"
Case "NMXSIMULTRANSV1": Designation = "NMX for the Simultrans
network"
End Select
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
Msg = "You have not completely filled out the '" & Designation &
"'
tab! You must complete the entire report before you save."
Else
Msg = "You have not noted any information about the '" &
Designation
& "'. If there were no major alarms, please note so."
End If
MsgBox Msg, 16, "Data Error"
End Sub