Trying get macro to clear cells when workbook is opened.

G

Guest

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
 
D

Dave Peterson

You have one guess in .misc.
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro giving errors at workbook open 1
I need help with my macros. 3
Excel Providing user message when RED X clicked & properly closing application. 2
ThisOutlookSession - Question 0
workbook beforesave event 4
Help On search macro 5
Macro that opens a separate workbook to a specified worksheet whenthe active workbook is right mouse 8
Excel Is it possible to ask a question within a question? 0

Top