excelvba

How To Close a UserForm Properly in Excel VBA?


I need to close a UserForm from a procedure that is inside the general module. The following code is just a test. I cannot use Me once that I am out of the form module.

Private Sub btnCancel_Click()        
    On Error GoTo TreatError

    Dim screen As Object

    Set screen = UserForms.Add(Me.Name)
    Unload screen
Leave:
    Set screen = Nothing
    Exit Sub
TreatError:
    GoTo Leave
End Sub

What's missing in this code? When I press the Cancel button, nothing happens, well, the form still keeps loaded. This UserForm is ShowModal True.

thanks in advance.

Ok Pᴇʜ. Here you are:

Public Sub EditarCombo(nomeColuna As String, itemCombobox As Variant, novoValor As Variant)
    On Error GoTo TratarErro
    
    Dim planilha As Worksheet
    Dim planRamais As Worksheet
    Dim tela As UserForm
    
    If ((itemCombobox & "") <> "") Then
        If ((Trim(novoValor) & "") <> "") Then
            If (itemCombobox <> Trim(novoValor)) Then
                Set planilha = Worksheets("CombosRamais")
                Set planRamais = Worksheets("Ramais")
                
                EditarNaColuna planilha, nomeColuna, itemCombobox, novoValor
                ExcluirDuplicadasNaColuna planilha, nomeColuna, novoValor
                OrdemarColuna planilha, nomeColuna, True
                RedefinirAreaColuna planilha, planRamais, nomeColuna
                
                EditarNaColuna planRamais, nomeColuna, itemCombobox, novoValor
            Else
                MsgBox "Você deve digitar um novo valor para o item escolhido.", vbInformation + vbOKOnly, "Editar Item"
                GoTo Sair
            End If
        Else
            MsgBox "O campo de novo valor está vazio.", vbInformation + vbOKOnly, "Editar Item"
            GoTo Sair
        End If
    Else
        MsgBox "Escolha um item na lista para ser editado.", vbInformation + vbOKOnly, "Editar Item"
        GoTo Sair
    End If
    
    Set tela = UserForms.Add(Replace(nomeColuna, "Col", "frmEditar"))
    Unload tela
Sair:
    Set tela = Nothing
    Set planilha = Nothing
    Set planRamais = Nothing
    Exit Sub
TratarErro:
    GoTo Sair
End Sub

Solution

  • Based on your comment to FunThomas' answer you would like to have a function like that

    Public Function UnLoadFrm(formName As String) 
        Dim frm As Object
    
        For Each frm In VBA.UserForms
            If frm.Name = formName Then
                Unload frm
                Exit Function
            End If
        Next frm
    
    End Function
    

    Be careful when using it as it is case sensitive. It might also be a good idea to use frm.Hide instead of Unload frm but then you should also rename the function.