|
object Variable or With Block Variable not set
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 |
object Variable or With Block Variable not set
Hi,
No error for me... What if you comment : Application.Run "DeleteBlankRows" Regards. Daniel 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 |
object Variable or With Block Variable not set
On Oct 30, 4:35 pm, Daniel.C wrote:
Hi, No error for me... What if you comment : Application.Run "DeleteBlankRows" Regards. Daniel 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 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 |
object Variable or With Block Variable not set
On Oct 30, 4:35 pm, Daniel.C wrote:
Hi, No error for me... What if you comment : Application.Run "DeleteBlankRows" Regards. Daniel 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 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 |
| All times are GMT +1. The time now is 08:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com