M
mgyanmote
Hi ,
am new to Excel-VBA
this is my code but am getting a run time error 91...object Variable
or With Block Variable not set at code point ........SheetName =
Sheet.name
Please help me to overcome this problem
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = False
Application.Run "DeleteBlankRows"
Dim Sheet As Worksheet
Dim SheetName As String
Set Sheet = ActiveSheet
SheetName = Sheet.name
If SheetName = "Accessions" And Not (Cancel = True) Then
If Cancel = False Then
Cancel = Module1.CheckSheet
End If
Dim ColCount As Integer
Dim TotalcolCount As Integer
TotalcolCount = 12
ColCount = 1
While ColCount < TotalcolCount
ColCount = ColCount + 1
If Application.CountA(Columns(1)) <
Application.CountA(Columns(ColCount)) And Cancel = False Then
Cancel = True
MsgBox "'Germplasm ID' is empty."
ColCount = TotalcolCount
End If
Wend
If Application.CountA(Columns(1)) >
Application.CountA(Columns(2)) And Cancel = False Then
Cancel = True
MsgBox "'Genus' is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(3)) And Cancel = False Then
Cancel = True
MsgBox "'Species' is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(7)) And Cancel = False Then
Cancel = True
MsgBox "'Ploidy' is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(8)) And Cancel = False Then
Cancel = True
MsgBox "'Biological Status'is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(9)) And Cancel = False Then
Cancel = True
MsgBox "'Source'is empty"
End If
End If
'End If
Cancel = Module1.CheckBookCompleteness(Cancel, "close")
End Sub
meenakshi
am new to Excel-VBA
this is my code but am getting a run time error 91...object Variable
or With Block Variable not set at code point ........SheetName =
Sheet.name
Please help me to overcome this problem
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = False
Application.Run "DeleteBlankRows"
Dim Sheet As Worksheet
Dim SheetName As String
Set Sheet = ActiveSheet
SheetName = Sheet.name
If SheetName = "Accessions" And Not (Cancel = True) Then
If Cancel = False Then
Cancel = Module1.CheckSheet
End If
Dim ColCount As Integer
Dim TotalcolCount As Integer
TotalcolCount = 12
ColCount = 1
While ColCount < TotalcolCount
ColCount = ColCount + 1
If Application.CountA(Columns(1)) <
Application.CountA(Columns(ColCount)) And Cancel = False Then
Cancel = True
MsgBox "'Germplasm ID' is empty."
ColCount = TotalcolCount
End If
Wend
If Application.CountA(Columns(1)) >
Application.CountA(Columns(2)) And Cancel = False Then
Cancel = True
MsgBox "'Genus' is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(3)) And Cancel = False Then
Cancel = True
MsgBox "'Species' is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(7)) And Cancel = False Then
Cancel = True
MsgBox "'Ploidy' is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(8)) And Cancel = False Then
Cancel = True
MsgBox "'Biological Status'is empty"
End If
If Application.CountA(Columns(1)) >
Application.CountA(Columns(9)) And Cancel = False Then
Cancel = True
MsgBox "'Source'is empty"
End If
End If
'End If
Cancel = Module1.CheckBookCompleteness(Cancel, "close")
End Sub
meenakshi