Macro giving errors at workbook open
 

I have several macros built into this workbook. One of the macros is supposed
to clear all fields in several different ranges. I get the error "cannot
change part of a merged cell" and then when i removed any merged cells from
the ranges i get some kind of global error. i wiil post my macro here, can
anyone tell me what i'm doing wrong?

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("ClearEOSV1", "ClearSEMErrorsV1",
"DEVLogOlivetteV1", "DEVLogOverlandV1", "ClearNMXNSGV1", "ClearNMXSIMV1")
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

Macro giving errors at workbook open
 

Instead of using:
Range(RngName).ClearContents
use:
Range(RngName).value = ""



kcdonaldson wrote:

I have several macros built into this workbook. One of the macros is supposed
to clear all fields in several different ranges. I get the error "cannot
change part of a merged cell" and then when i removed any merged cells from
the ranges i get some kind of global error. i wiil post my macro here, can
anyone tell me what i'm doing wrong?

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("ClearEOSV1", "ClearSEMErrorsV1",
"DEVLogOlivetteV1", "DEVLogOverlandV1", "ClearNMXNSGV1", "ClearNMXSIMV1")
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

--

Dave Peterson