0

I have this code in which I do some checking to delete items in a table according to their numeration. When debugging the code whenever it reaches the critical line (shown below) it jumps to the respective Excel Objects code of that sheet (shown even lower). I already tested positioning it in different places across the code and using Application.Enableevents = false but it didn't work.

Any suggestions?

First Code:

Sub deleta_linhas()    
    Dim linha As Integer, coluna As Integer
    Dim n_item As Integer, n_prod As Integer
    Dim soma As Integer, linha_selecionada As Integer
    linha_selecionada = ActiveCell.Row
    Dim linha_atual(1 To 2) As Integer, next_line(1 To 2) As Integer, line_before(1 To 2) As Integer
    Dim i As Integer
    i = 0
    Dim novos_numeros As String

    ' Condição 1 em que há apenas 1.1 e mais nada
    If IsEmpty(Cells(36, 1)) = True Then
        ActiveCell.EntireRow.Delete
        Range("B5").Value = 1
        Range("B6").Value = 1
        Range("B7").Value = 0
        Range("B8").Value = 35
    Else
        ' Condição 2 em que há 1.1, 1.2 e mais ou 1.1, 2.1 e mais
        linha_atual(1) = Mid(Cells(linha_selecionada, 1), 1, 1)
        linha_atual(2) = Mid(Cells(linha_selecionada, 1), 5, 1)
        next_line(1) = Mid(Cells(linha_selecionada + 1, 1), 1, 1)
        next_line(2) = Mid(Cells(linha_selecionada + 1, 1), 5, 1)
        line_before(1) = Mid(Cells(linha_selecionada - 1, 1), 1, 1)
        line_before(2) = Mid(Cells(linha_selecionada - 1, 1), 5, 1)
    
        If linha_atual(1) = next_line(1) = False And linha_atual(2) = next_line(2) Then
            Selection.EntireRow.Delete                   '(THIS IS WHERE IT JUMPS TO EXCEL OBJECTS)
            Range("B8").Value = Range("B8").Value - 1
            Range("B5").Value = Range("B5").Value - 1

            While IsEmpty(Cells(linha_selecionada + i, 1)) = False
                n_item = Mid(Cells(linha_selecionada + 1, 1), 1, 1) - 1
                novos_numeros = n_item & " . 1"
                Cells(linha_selecionada + 1, 1).Value = novos_numeros
                i = i + 1
            Wend
        End If
    End If
    
    linha = 36
    coluna = 9
    soma = 0
    Selection.EntireRow.Delete  
 
    While IsEmpty(Cells(linha, coluna)) = False
        soma = soma + Cells(linha, coluna).Value
        linha = linha + 1
    Wend

    linha = linha + 1
    Cells(linha, coluna).Value = soma   
End Sub

Code in the Excel Objects (mostly of combobox buttons)

Private Sub ComboBox1_Change()    
    If ComboBox1.Value = "Fabricação" Then
        Rows("41:52").Select
        Selection.EntireRow.Hidden = True
        Range("B28").Select
        Range("B11").Value = "Fabricação"
    End If

    If ComboBox1.Value = "Nacionalização" Then
        Rows("41:52").Select
        Selection.EntireRow.Hidden = False
        Range("B28").Select
        Range("B11").Value = "Nacionalização"
    End If

    If ComboBox1.Value = "Projeto" Then
        Rows("41:52").Select
        Selection.EntireRow.Hidden = False
        Range("B28").Select
        Range("B11").Value = "Projeto"
    End If

    If ComboBox1.Value = "Manutenção" Then
        Rows("41:52").Select
        Selection.EntireRow.Hidden = False
        Range("B28").Select
        Range("B11").Value = "Manutenção"
    End If

    If ComboBox1.Value = "Industrialização" Then
        Rows("41:52").Select
        Selection.EntireRow.Hidden = False
        Range("B28").Select
        Range("B11").Value = "Industrialização"
    End If
End Sub

' Lógicas dos tratamentos
Private Sub OptionButton2_Click()
    If OptionButton2.Value = True Then
        Range("B10").Value = "Tempera"
    End If
End Sub

Private Sub OptionButton1_Click()
    If OptionButton1.Value = True Then
        Range("B10").Value = "Nitretação"
    End If
End Sub

Private Sub OptionButton3_Click()
    If OptionButton3.Value = True Then
        Range("B10").Value = "Cementação"
    End If
End Sub

Private Sub OptionButton4_Click()
    If OptionButton4.Value = True Then
        Range("B10").Value = "---"
    End If
End Sub
' FIM
Mark S.
  • 1,474
  • 1
  • 6
  • 20
fsmisina
  • 1
  • 3
  • When debugging, which event is triggered? – Samuel Everson May 31 '21 at 02:04
  • 1
    Have you set the `RowSource` of the Combobox to an Excel range? – Siddharth Rout May 31 '21 at 04:50
  • @SamuelEverson when it runs the "delete row" line it goes from the module code to a code which is in the Excel Objects, a code which is attached to a combobox. – fsmisina May 31 '21 at 06:35
  • @SiddharthRout yes the RowSource is defined in cells above the line i want to be deleted. Its source are 5 cells in a straight column. – fsmisina May 31 '21 at 06:35
  • I do nto see it getting defined in the code? Have you set it in designtime? – Siddharth Rout May 31 '21 at 06:54
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 31 '21 at 07:38
  • @SiddharthRout I defined it as ListFillRange in the properties window, set to S3:S7 – fsmisina May 31 '21 at 16:38
  • I thought so. When the row is getting deleted, the `ComboBox1_Change` event will fire because of that and hence the code is jumping to the sheet code module. – Siddharth Rout May 31 '21 at 16:54
  • @Pᴇʜ I tried using cells(line,1).entirerow.delete and using with Sheets("").Cells(line,1) entirerow.delete End With But both failed... I'm thinking of declaring the list fill range inside the combobox code but I don't know how to do that – fsmisina May 31 '21 at 17:06
  • @SiddharthRout do you have any suggestions on how to declare them inside the VBA? I'm not using userform, it is an activex combobox – fsmisina May 31 '21 at 17:09

0 Answers0