In VBA, How to Reset a Dynamically Changing UserForm With Change Event?

[morgoyo]

Hello everyone,

I am trying to create a dynamic UserForm in a way that the design of the form changes (textboxes and labels added) upon selecting an option from an existing ComboBox with ComboBox1_Change() event.

Let’s say the user chose “Add New Item” from the combobox. ComboBox1_Change() event will create 4 new textboxes in the form so that the user can enter the information that is needed for adding a new item into those newly created textboxes and then the information will be processed by OKButton_Click () event to add those newly added information to their respective place in a table in one of the Sheets.

The problem is that the user can chose an option they didn’t want to from the ComboBox1 as a mistake and when they choose back the correct option they originally wanted, the earlier textboxes and labels that were created by ComboBox1_Change() event (as a result of the mistake) will still be there. I want to make sure that if such a mistake & correction thingy happens the UserForm is reset to its original state (or mistakenly created textboxes and labels are removed) before the correct textboxes and labels are created so that the Form doesn’t cause any confusion for the user and the data is processed correctly.

I know that one solution could be adding a Reset button with an event like

Private Sub ResetButton_Click()
Unload me
UserForm1.Show
End Sub

But I want the ComboBox1_Change () event to reset the form for a smoother experience. So, I want to add a code at the begining of the ComboBox1_Change() event to make sure the Form is first reset and then the new textboxes and labels are created. So that if a mistake happens it doesn’t cause any problems. How can I do that?

I have only very very basic understanding of Excel, this is really not my area of expertise, and this document is to help my father who owns a small business with his work. So, please bare with me if this question is absurd or has a really simple answer or what I want is simply not possible with Excel VBA

Thanks in advance!

Here is a simplified version of the code of the ComboBox1_Change() event:

Private Sub ComboBox1_Change()

If ComboBox1.Value = "Add New Item" Then

With Me

Dim Item, ItemForeign1, ItemForeign2, ItemCode As MSForms.TextBox

Set Item = .Controls.Add("Forms.TextBox.1", "txtItem")

With Item
.Top = 54
.Left = 84
.Height = 24
.Width = 132
End With

Set ItemForeign1 = .Controls.Add("Forms.TextBox.1", "txtItemForeign1")

With ItemForeign1
.Top = 84
.Left = 84
.Height = 24
.Width = 132
End With

Set ItemForeign2 = .Controls.Add("Forms.TextBox.1", "txtItemForeign2")

With ItemForeign2
.Top = 114
.Left = 84
.Height = 24
.Width = 132
End With

Set ItemCode = .Controls.Add("Forms.TextBox.1", "txtItemCode")

With ItemCode
.Top = 144
.Left = 84
.Height = 24
.Width = 132
End With

End With

ElseIf ComboBox1.Value = "Add New Package Type" Then
With Me

Dim Package As MSForms.TextBox

Set Package = .Controls.Add("Forms.TextBox.1", "txtPackage")

With Package
.Top = 54
.Left = 84
.Height = 24
.Width = 132
End With

End With
End If

End Sub

 

[dmt32]

Hi,
when adding controls at run time (on the fly) you would use the remove method to delete unwanted controls.

I have made some changes you can use that hopefully, will assist you developing your project & included a new code to remove controls created at run time.

Private Sub ComboBox1_Change()
    Dim Box         As MSForms.TextBox
    Dim i           As Long
    
    Const BoxHeight As Long = 24
    Const BoxWidth  As Long = 132
    Const BoxLeft   As Long = 84
    Const VSpace    As Long = BoxHeight + 6
    Const Topmargin As Long = 54
    
    RemoveControls Me
    
    If Me.ComboBox1.Value = "Add New Item" Then
        
        For i = 1 To 4
            Set Box = Me.Controls.Add("Forms.TextBox.1", _
                Choose(i, "txtItem", "txtItemForeign1", "txtItemForeign2", "txtItemCode"))
            
            With Box
                .Top = (i - 1) * VSpace + Topmargin
                .Left = BoxLeft
                .Height = BoxHeight
                .Width = BoxWidth
            End With
            Set Box = Nothing
        Next i
        
    ElseIf Me.ComboBox1.Value = "Add New Package Type" Then
        
        Set Box = Me.Controls.Add("Forms.TextBox.1", "txtPackage")
        
        With Box
            .Top = Topmargin
            .Left = BoxLeft
            .Height = BoxHeight
            .Width = BoxWidth
        End With
        
    End If
    
End Sub

Sub RemoveControls(ByVal Form As Object)
    Dim Ctrl As Control
    'deletes any control that was added at run time
    On Error Resume Next
    With Form
        For Each Ctrl In .Controls
             .Controls.Remove Ctrl.Name
        Next Ctrl
    End With
    On Error GoTo 0
End Sub

Dave

 

[morgoyo]

Hi,
when adding controls at run time (on the fly) you would use the remove method to delete unwanted controls.

I have made some changes you can use that hopefully, will assist you developing your project & included a new code to remove controls created at run time.

Private Sub ComboBox1_Change()
    Dim Box         As MSForms.TextBox
    Dim i           As Long
   
    Const BoxHeight As Long = 24
    Const BoxWidth  As Long = 132
    Const BoxLeft   As Long = 84
    Const VSpace    As Long = BoxHeight + 6
    Const Topmargin As Long = 54
   
    RemoveControls Me
   
    If Me.ComboBox1.Value = "Add New Item" Then
       
        For i = 1 To 4
            Set Box = Me.Controls.Add("Forms.TextBox.1", _
                Choose(i, "txtItem", "txtItemForeign1", "txtItemForeign2", "txtItemCode"))
           
            With Box
                .Top = (i - 1) * VSpace + Topmargin
                .Left = BoxLeft
                .Height = BoxHeight
                .Width = BoxWidth
            End With
            Set Box = Nothing
        Next i
       
    ElseIf Me.ComboBox1.Value = "Add New Package Type" Then
       
        Set Box = Me.Controls.Add("Forms.TextBox.1", "txtPackage")
       
        With Box
            .Top = Topmargin
            .Left = BoxLeft
            .Height = BoxHeight
            .Width = BoxWidth
        End With
       
    End If
   
End Sub

Sub RemoveControls(ByVal Form As Object)
    Dim Ctrl As Control
    'deletes any control that was added at run time
    On Error Resume Next
    With Form
        For Each Ctrl In .Controls
             .Controls.Remove Ctrl.Name
        Next Ctrl
    End With
    On Error GoTo 0
End Sub

Dave

I'm utterly speechless! This is just what I wanted and even much more! The code is so neat and organized and does exactly what I wanted. Thank you Dave, you made my day! I marked your reply as the solution.

 

[dmt32]

I'm utterly speechless! This is just what I wanted and even much more! The code is so neat and organized and does exactly what I wanted. Thank you Dave, you made my day! I marked your reply as the solution.

most welcome & thank-you for your very generous feedback, it is very much appreciated

Dave

 

[dmt32]

Hi,
found a moment to tidy the solution up a little which you may find useful if your list needs to contain more than two choices.
Solution uses the comboxes listindex property to select the appropriate array of control names.

Option Base 1
Private Sub ComboBox1_Change()
    Dim Box         As MSForms.TextBox
    Dim i           As Long, Indx As Long
    Dim arr         As Variant
   
    '-----------------------------------------------------------------------------------------
    '                                           Settings
    '-----------------------------------------------------------------------------------------
   
    Const BoxHeight As Long = 24
    Const BoxWidth  As Long = 132
    Const BoxLeft   As Long = 84
    Const VSpace    As Long = BoxHeight + 6
    Const TopMargin As Long = 54
   
    '-----------------------------------------------------------------------------------------
   
    RemoveControls Me
   
    Indx = Me.ComboBox1.ListIndex + 1
    If Indx < 1 Then Exit Sub
   
    arr = Array(Array("txtItem", "txtItemForeign1", "txtItemForeign2", "txtItemCode"), _
          Array("txtPackage"))(Indx)
   
   
        For i = 1 To UBound(arr)
       
            Set Box = Me.Controls.Add("Forms.TextBox.1", arr(i))
           
            With Box
                .Top = (i - 1) * VSpace + TopMargin
                .Left = BoxLeft
                .Height = BoxHeight
                .Width = BoxWidth
            End With
           
            Set Box = Nothing
           
        Next i
       
End Sub

Hope Helpful

Dave

 

[morgoyo]

Hi,
found a moment to tidy the solution up a little which you may find useful if your list needs to contain more than two choices.
Solution uses the comboxes listindex property to select the appropriate array of control names.

Option Base 1
Private Sub ComboBox1_Change()
    Dim Box         As MSForms.TextBox
    Dim i           As Long, Indx As Long
    Dim arr         As Variant
  
    '-----------------------------------------------------------------------------------------
    '                                           Settings
    '-----------------------------------------------------------------------------------------
  
    Const BoxHeight As Long = 24
    Const BoxWidth  As Long = 132
    Const BoxLeft   As Long = 84
    Const VSpace    As Long = BoxHeight + 6
    Const TopMargin As Long = 54
  
    '-----------------------------------------------------------------------------------------
  
    RemoveControls Me
  
    Indx = Me.ComboBox1.ListIndex + 1
    If Indx < 1 Then Exit Sub
  
    arr = Array(Array("txtItem", "txtItemForeign1", "txtItemForeign2", "txtItemCode"), _
          Array("txtPackage"))(Indx)
  
  
        For i = 1 To UBound(arr)
      
            Set Box = Me.Controls.Add("Forms.TextBox.1", arr(i))
          
            With Box
                .Top = (i - 1) * VSpace + TopMargin
                .Left = BoxLeft
                .Height = BoxHeight
                .Width = BoxWidth
            End With
          
            Set Box = Nothing
          
        Next i
      
End Sub

Hope Helpful

Dave

Hi again Dave,

Just wow... This is beyond what I would even dare to imagine It is so neat and well organized and eliminates the need to write the same code for each item repetitively. I have learned so much from you! Thank you so much for your very generous help, I really appreciate it.

 

[dmt32]

Due to time limitations, I just went broadly with your initial approach to resolve your issue but in most cases, repeating code can be avoided.

Pleased update helps

Dave

 

[morgoyo]

Due to time limitations, I just went broadly with your initial approach to resolve your issue but in most cases, repeating code can be avoided.

Pleased update helps

Dave

Due to the original question being about removing the unwanted controls that were created at run time, I believe it would be best to keep the solution as your first post.

Needless to say it helped me a ton. Thanks!