| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error Number: 91 object variable or With block Variable not set | Excel Programming | |||
| Need help with Error 'object variable or with block variable not set' | Excel Programming | |||
| Run-Time error '91': Object variable of With block variable not set | Excel Programming | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Linear Mode
