Greetings Nitin,
It would most likely be helpful if you post the proedure so we can see what's going on.
Mark
Hi Buddy! Here's the code and th prblm description
Private Sub CircuitTerminalDelete_Click()
Dim sFirstCell As String, nNormalExecution As Integer, nSelectedRow As Integer, nFirstCellRow As Integer
Dim vShapes As Shape
Dim sShapename As String
Dim nrows As Integer
Dim i As Integer
Dim sHyperlink As String
Dim nLastrow As Integer
Dim sSheetadd As String
Dim sSheetname As String
On Error GoTo last
Application.ScreenUpdating = False
sFirstCell = ThisWorkbook.ActiveSheet.Range("CktTerm_Input1").Address
nSelectedRow = Selection.Row
'get the name of the sheet linked to the hyperlink in that row
If ActiveSheet.Cells(nSelectedRow, 7).Value <> "" Then
sHyperlink = ActiveSheet.Cells(nSelectedRow, 7).Hyperlinks(1).SubAddress
sSheetadd = Left(sHyperlink, 18)
End If
'every sheet is protected by passwd pwdSheets
nFirstCellRow = ThisWorkbook.ActiveSheet.Range("CktTerm_Input1").Row
If pwdSheets = "" Then
pwdSheets = "123"
End If
'below function deletes a row returns 0 if the row gets deleted else -1
'-----------
nNormalExecution = DeleteRow("CktTerm_Input1", sFirstCell, YES")
'-------------
If (nNormalExecution = -1) Then
Exit Sub
Else
'if the row gets deleted we need to delete the combo box of that row
'as well as the worksheet linked with the hyperlink of that row
ActiveSheet.Unprotect pwdSheets
'this code segment deletes the combo-box of the deleted row
'------------------------------------------------------
For Each vShapes In ActiveSheet.Shapes
If Right(vShapes.Name, 2) = nSelectedRow Then
If vShapes.TopLeftCell.Row = nSelectedRow Then
vShapes.Delete
End If
End If
Next vShapes
'----------------------------------
Do Until Range("CktTerm_Input1").Offset(nrows, 0).Value = ""
nrows = nrows + 1
Loop
nLastrow = Range("CktTerm_Input1").Offset(nrows, 0).Row
If nSelectedRow >= nFirstCellRow And nSelectedRow <= nFirstCellRow + nrows - 1 Then
For i = nSelectedRow To nLastrow
' this is used for renaming of the combo boxes below
'the deletd row so that their names also reflect the row
'in which they are now present
'-----------------------------------------------
For Each vShapes In ActiveSheet.Shapes
If vShapes.TopLeftCell.Row = i And vShapes.TopLeftCell.Column = 6 Then
vShapes.Name = "InputDropDown" & i
End If
Next vShapes
Next i
'-----------------------------------------
End If
'the below code segment searches for the sheet using the sheet name found from the hyperlink
'and then deletes the sheet from workbook
'--------------------------------------------------------
If ActiveSheet.Cells(nSelectedRow, 7).Value <> "" Then
For Each ws In Worksheets
sSheetname = ws.Name
If "'" & sSheetname & "'" = sSheetadd Then
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(sSheetname).Unprotect pwdSheets
ThisWorkbook.Worksheets(sSheetname).Activate
ThisWorkbook.Worksheets(sSheetname).Delete
Application.DisplayAlerts = True
End If
If "'" & sSheetname = sSheetadd Then
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(sSheetname).Unprotect pwdSheets
ThisWorkbook.Worksheets(sSheetname).Activate
ThisWorkbook.Worksheets(sSheetname).Delete
Application.DisplayAlerts = True
End If
Next ws
End If
End If
last: MsgBox (Err.Description)
ThisWorkbook.Worksheets("CIRCUIT TERMINAL").Activate
Application.ScreenUpdating = True
ThisWorkbook.Worksheets("CIRCUIT TERMINAL").Protect pwdSheets
End Sub
I have a table with 5 columns.col1---contains serialno(int),col2 contains a terminal no(int),col3 contains name(string),col4 contains a combo-box,col5 contains a hyperlink to
a sheet.Each hyperlink is associated with a distinct sheet.When a row is deletd then the sheet linked to the hypelink of col5 of that row should also get deleted and
the combo box in that row should also be deleted .
Problem occurs after the program complets executin the code highlighted in
RED.While debugging the message comes
"CAnnot enter break mode at this time" and if I directly run it the message is "
Application or object defined error"
