Have to Click Reset Button twice to execute full UserForm controls reset

[NBahar718]

I am using the following public subprocedure to reset all UserForm controls

Public Sub ResetUserFormControls(FormName As UserForm)

        Dim Ctrl As MSForms.Control

                For Each Ctrl In FormName.Controls
                
                        If (TypeName(Ctrl) = "TextBox") Or (TypeName(Ctrl) = "ListBox") Then
                        
                                Ctrl.Value = ""
                                
                        End If
                        
                Next Ctrl
                
End Sub

I am using a dynamic userform where a frame frmModifyTemplate is dynamically filled with textbox controls from a range in Excel
Code in the Userform AddMethodToTemplate for reset button goes like this

Private Sub btnReset_Click()
        
        '       Reset values on the USERFORM by calling ResetUserFormControls public subprocedure present in _
        CalledPublicProcedures

        Call ResetUserFormControls(AddMethodToTemplate)

End Sub

It works. However, I have to click twice for it to work. First time, it clears only the first textbox control, but not all. Please guide how I can change the code to make the reset all button work in first click only.

 

[RoryA]

Do you have Change code associated with any of the controls you are clearing?

 

[NBahar718]

Public Sub UserForm_Initialize()

        '       Public variables used in this subprocedure are declared in AllPublicVariables module
        '       under section - AddMethodToTemplate USERFORM public variables

        '       Instantiating classes for process and method stages defined in class modules
        '       1. ClassForManualMethod    2. ClassForManualStages

         Set AllEventClassInstancesOfProcessSteps = New Collection
         Set AllEventClassInstancesOfMethodStages = New Collection
        
        '       Defining button (toggle or command) labels based on closed/open state
                
         TemplateOpen = "Modify Existing Method" ' First Toggle Button label when existing method frame is not visible
         TemplateClose = "Close Existing Method" ' First Toggle Button label when existing method frame is visible
        
         AddStageOpen = "Add Method Manually" ' Second Toggle Button label when new method frame is not visible
         AddStageClose = "Close Add Method Manually" ' Second Toggle Button label when new method frame is visible
        
         AppendStageOpen = "Append Stages to Template" ' Third Toggle Button label when new method and existing method frames are not visible
         AppendStageClose = "Close Append Stages" ' Third Toggle Button label when new method and existing method frames are visible
                
         LabelAddStages = "No. of stages to add" ' First label when new method frame is visible
         LabelAppendStages = "No. of stages to append" ' Second label when new method and existing method frames are visible
                 
         BtnAddStages = "Add" ' First button label when new method frame is visible
         BtnAppendStages = "Append" ' Second button label when new method and existing method frames are visible
         
        '       Determining USERFORM height at various stages
        '       (initial, only-template, manual stages, append stages) of user's choices
        
        '       Userform Height = top buttons top plus top buttons height plus buffer
        
        InitialUserFormHeight = CLng(tglChangeTemplate.Top + tglChangeTemplate.Height + 35)
        
         Me.Height = InitialUserFormHeight
         
        '      Userform Top = Application screen height divided by 2 to put in middle
        
         Me.Top = (Application.UsableHeight / 2)
         
        '      Userform Left = Left location of application plus application screen width divided by 4 to put in middle
        
         Me.Left = Application.Left + (Application.UsableWidth / 4)
         
        '       Only-template USERFORM height
        
         OnlyTemplateHeight = CLng(frmModifyTemplate.Top + frmModifyTemplate.Height + 30)
        
        '       Manual stages USERFORM top
        
         AddStageManuallyFrameTopInitial = CLng(frmAddStagesManually.Top)
        
        '       Manual stages USERFORM height = modified frame top (as updated after adding no. of new method steps) + manual stages frame height
        
         OnlyAddStageHeight = CLng(AddStageManuallyFrameTopModified + frmAddStagesManually.Height + 30)
         
        '       Append stages USERFORM height = existing method frame height + manual stages frame height
        
         BothTemplateAndAddStageHeight = CLng(OnlyTemplateHeight + OnlyAddStageHeight - 60)
        
        '       Toggle state and caption for 'Append Stages to Existing Method' toggle button
        
         tglAppendStagesToTemplate.Value = False
         tglAppendStagesToTemplate.Caption = AppendStageOpen
        
        '       Toggle state and caption for 'Add All Stages Manually' toggle button
        
         tglAddStagesManually.Value = False
         tglAddStagesManually.Caption = AddStageOpen
        
        '       Toggle state and caption for 'Modify existing method' toggle button
        
         tglChangeTemplate.Value = False
         tglChangeTemplate.Caption = TemplateOpen
         
        '       Get the dynamic pre-populated textboxes for existing method from the tmpOperation table in BatchTemplate worksheet
        '       Set current worksheet as BatchTemplate and range as 'tmpOperation' table
        
         Dim ws As Worksheet:                                           Set ws = ThisWorkbook.Sheets("BatchTemplate")
         Dim rng As Range:                                                  Set rng = ws.Range("tmpOperation")
              
         '      Declare loop variables
         
         Dim i As Integer, j As Integer, k As Integer
         
         '      Declare USERFORM controls
         
         Dim TextBoxCtrl As MSForms.Control
         Dim TextBoxCtrlPrev As MSForms.Control
         
         '      Declare EventHandler class object for ClassForMethodStages class module
         
         Dim EventHandler As ClassForMethodStages
         
         '      Declare and initialize variables and arrays for the dynamic userform labels and controls
         
        '       Template Table
        
        Dim TableColumnsCount As Integer:                     TableColumnsCount = rng.Columns.Count
        Dim TableRowsCount As Integer:                          TableRowsCount = rng.Rows.Count
        
        '       Labels
        
         Dim LabelTop As Long:                                          LabelTop = 10
         Dim ControlTop As Long
         
         '      Controls
         
         Dim LabelControlType As String:                           LabelControlType = "Forms.Label.1"       '     For dynamic labels
         Dim TextBoxControlType As String:                       TextBoxControlType = "Forms.TextBox.1" '         For dynamic textbox controls
         
         '      Common variables for labels and controls
         
         Dim HeightValue As Long:                                     HeightValue = CLng(18)
         Dim WidthValue As Long:                                      WidthValue = Me.Width / 8.5
         
         '      Declaring an array of dynamic labels to be used as column headers
         
         Dim LabelName(25) As Variant

        '       Looping through labels defined for existing chromatographic method
        
         For j = 1 To TableColumnsCount
         
                '       Assigning values to array components in the form of range headers for each column 'j'
                
                LabelName(j) = rng.Cells(rng.ListHeaderRows, j)
                
                '      Calling LabelSpecsForFrame subprocedure defined in CalledPublicProcedures module to _
                dynamically assign labels to the frame
         
                Call LabelSpecsForFrame(FrameName:= _
                                                                AddMethodToTemplate.frmModifyTemplate, _
                                                        index:= _
                                                                j, _
                                                        ControlType:= _
                                                                LabelControlType, _
                                                        ControlName:= _
                                                                CStr("Label" & j), _
                                                        ControlHeight:= _
                                                                HeightValue + 6, _
                                                        ControlWidth:= _
                                                                WidthValue, _
                                                        ControlLeft:= _
                                                                (WidthValue * (j - 1)), _
                                                        ControlTop:= _
                                                                LabelTop, _
                                                        ControlCaption:= _
                                                                CStr(LabelName(j)), _
                                                        ControlFontBold:= _
                                                                True, _
                                                        ControlTextAlign:= _
                                                                2, _
                                                        FontSize:= _
                                                                10)
         
         Next j
         
        '         Loop through each row of the range
         
         For i = 1 To TableRowsCount
         
                    '   Defining top position for each row of process stages using ControlTop variable
         
                     ControlTop = 36 + 21 * (i - 1)
                     
                     '  Loop through each column of the range
                     
                     For k = 1 To TableColumnsCount
                     
                                '      Calling ControlSpecsForFrame subprocedure defined in CalledPublicProcedures module to _
                                dynamically assign controls to the frame
                     
                                Call ControlSpecsForFrame(FrameName:= _
                                                                                AddMethodToTemplate.frmModifyTemplate, _
                                                                        NextControl:= _
                                                                                TextBoxCtrl, _
                                                                        PrevControl:= _
                                                                                TextBoxCtrlPrev, _
                                                                        ControlType:= _
                                                                                TextBoxControlType, _
                                                                        ControlName:= _
                                                                                CStr("TextBox" & i & k), _
                                                                        Height:= _
                                                                                HeightValue, _
                                                                        Width:= _
                                                                                WidthValue, _
                                                                        Left:= _
                                                                                WidthValue * (k - 1), _
                                                                        Top:= _
                                                                                ControlTop, _
                                                                        FontBold:= _
                                                                                False, _
                                                                        TextAlign:= _
                                                                                2, _
                                                                        ValueVal:= _
                                                                                0)
                             
                                '       Get value in each textbox using the range cells and address
                                
                                With TextBoxCtrl
                             
                                        .Value = rng.Cells(i, k).Value
                                        
                                        '       Column address is provided in terms of letter ID using column index values _
                                        by using the function Col_Letter as defined in CalledPublicFunctions module
                                        '       CStr is used to convert any data type to a string
                                        
                                        .ControlSource = CStr(ws.Name & "!" & Col_Letter(rng.Cells(i + 2, k).Column) & (i + 2))
                                     
                                End With
                             
                                Select Case k
                                
                                Case 1  '       Adding event handler for process stage name
                                
                                        Call AddEventHandlerForTemplateProcessStages(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                                                
                                Case 2  '       Adding event handler for total CVs used in process stage
                                
                                        Call AddEventHandlerForTemplateTotalCV(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                                                
                                Case 3  '       Adding event handler for cumulative CVs used till process stage
                                
                                        Call AddEventHandlerForTemplateStopCV(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                                                
                                Case 4  '       Adding event handler for start % concentration of buffer B in process stage
                                
                                        Call AddEventHandlerForTemplateStartPercentB(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                                                
                                Case 5  '       Adding event handler for end % concentration of buffer B in process stage
                                
                                        Call AddEventHandlerForTemplateStopPercentB(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                                                
                                Case 6  '       Adding event handler for maximum delta pressure attained during process stage
                                
                                        Call AddEventHandlerForTemplatePressure(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                                                
                                Case 7  '       Adding event handler for average pH attained during process stage
                                
                                        Call AddEventHandlerForTemplatepH(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                                                
                                Case 8  '       Adding event handler for average conductivity (mS/cm) attained during process stage
                                
                                        Call AddEventHandlerForTemplateCond(EventHandler, TextBoxCtrl, i, Me, AllEventClassInstancesOfMethodStages)
                                        
                                End Select
                     
                     Next k
             
         Next i
         
         '      Determining height of the existing method frame after getting row and column input from the target table range
         
         frmModifyTemplate.Height = (TableRowsCount * 21) + 60
         
         '      Determining the modified 'Add Stages Manually' frame's top value, since it now appears _
         below the modified existing method frame
         
         AddStageManuallyFrameTopModified = CLng(frmModifyTemplate.Top + frmModifyTemplate.Height)
        
End Sub

 

[RoryA]

So that looks like a yes, but you haven't actually shown what the event handlers are doing. It is likely that they are interfering.

I would have thought that if you can write that code, you can debug what is going on.

 

[NBahar718]

So that looks like a yes, but you haven't actually shown what the event handlers are doing. It is likely that they are interfering.

I would have thought that if you can write that code, you can debug what is going on.

Sir, I actually thought the same, but direly need an expert's advice on this issue.

 

[NBahar718]

Sir, I actually thought the same, but direly need an expert's advice on this issue.

Event handlers are currently not doing anything. I have put them in case they do in future. Do you think event-handlers are an issue here?

 

[RoryA]

Sir, I actually thought the same, but direly need an expert's advice on this issue.

Well we don't have the workbook so we can't step through the code, whereas you can.

 

[NBahar718]

Well we don't have the workbook so we can't step through the code, whereas you can.

I understand Sir. It is just that the application is proprietary to the institute I work in and I cannot share the workbook without permission. However, I would be very grateful if you could suggest some solutions for this issue.

 

[RoryA]

I have already suggested the only thing I can without the workbook. Step through the code.