Macro giving errors at workbook open

[kcdonaldson]

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]

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