object Variable or With Block Variable not set

[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

 

[Daniel.C]

Hi,
No error for me...
What if you comment :
Application.Run "DeleteBlankRows"
Regards.
Daniel

 

[mgyanmote]

Hi,
No error for me...
What if you comment :
Application.Run "DeleteBlankRows"
Regards.
Daniel

am proving the excel sheet as a link in my jsp page when i close the
Sheet am getting the error 91....at
SheetName = Sheet.name .its Showing this line in yellow colour .when
it take the mouse on sheet.name it is showing <object Variable or With
Block Variable>.
Please help me

 

[Daniel.C]

am proving the excel sheet as a link in my jsp page when i close the
Sheet am getting the error 91....at
SheetName = Sheet.name .its Showing this line in yellow colour .when
it take the mouse on sheet.name it is showing <object Variable or With
Block Variable>.
Please help me

Can just guess...
Try :
Msgbox Sheet.Name
before :
SheetName = Sheet.name
and check the displyed name is the name of the right sheet.
Try :
SheetName = ActiveSheet.name
Try replacing "SheetName" with a trivial variable name such as "Tom".
Try deleting the line and using "Sheet.Name" in lieu of "SheetName"
Hope this helps.
Daniel