VBA Assigning Value to Global Array of MS.Textbox

[everyColorDumb]

Howdy there!

Objective: I have a UserForm2 module. Before any sub or function definition, I declared a global array barcodeArr() of MS.Textbox. Within the Public Sub Init, I am try to add Textbox objects and setting the properties for each one to that global array. I then want to retrieve those array values from within the Private Sub btnSubmit_Click() routine.
Issue: When I try to access the barcodeArr() from inside the btnSubmit_Click()
Troubleshooting:
1) Creating a watch for barcodeArr() to see if the values get modified as I am add elements to the array from inside Init(). I saw that the global barcodeArr() under Option Explciit was not modified and showed "Out of Cnntext" in the Watch menu; however, when I added an additional watch for barcodeArr() referenced from within the Init() function, that was being populated as I stepped through the routine using the debugger.
2) Creating a function GetBarcodes() to get the barcodeArr() and store it in a Variant type return parameter. Tried retrieving it from inside the btnSubmit_Click() and assign it to a LocalBarcodeArr(), which I tried declaring as an MS.Textbox and another time as a Variant. Both generated a Type Mismatch error at line Local BarocdeArr = GetBarcodes()

Below is an outline of the code to show where the variables are declared and called.

Option Explicit

Public Form As UserForm2

Public childFrame As MSForms.Frame

Public globalNumPlates As Integer
Dim barcodeArr() As MSForms.TextBox


Public label_VolUnits  As MSForms.label
Public label_concUnits  As MSForms.label
Public combo_volUnits As MSForms.ComboBox
Public combo_concUnits As MSForms.ComboBox


Public WithEvents btSubmit As MSForms.CommandButton

Dim sConnString As String


Private Sub UserForm_Initialize()
    OriginFrame.Caption = "Register Info"
    OriginFrame.BorderStyle = fmBorderStyleNone
    
    Call Init(UserForm2, OriginFrame)
End Sub

Public Sub Init(myForm As UserForm2, fraMain As MSForms.Frame)
globalNumPlates = totalDestPlates

With childFrame
    With .Controls
        ReDim barcodeArr(totalDestPlates)
        Set barcodeArr(i) = .Add("Forms.TextBox.1")
                With barcodeArr(i)
                    .Top = top_barcodeField + (i - 1) * pad_barcodeField
                    .Left = offset_barcodeField
                    .Width = width_barcodeField
                    .Name = "barcodeString" & i
                    barcodeLabelsArr(i) = "barcodeString" & i
                    
                 End With
        End With
End Sub

'Tried with and without using this getter function
Function GetBarcodes() As Variant
    GetBarcodes = barcodeArr
End Function     
    
Private Sub btSubmit_Click()
    'ReDim LocalBarcodeArr(1 To globalNumPlates)
       'LocalBarcodeArr = GetBarcodes() ERROR RETURNED HERE

    'Populate barcodes
    Dim i As Integer
    For i = LBound(barcodeArr) + 1 To UBound(barcodeArr)
        Debug.Print barcodeArr(i)
        Range("A" & (i + 3)).Value = barcodeArr(i)
    Next i
    
End Sub

 

[RoryA]

There appear to be a few issues there but to start with i has no value in your Init routine.

 

[everyColorDumb]

@RoryA , my bad. I accidentally omitted "Dim i As Integer" preceding that array assigning block when I copied my code over. What were the other issues? Thanks for taking the time!

 

[RoryA]

Declaring it doesn't give it a value.

I get the impression that is not all your actual code, since I doubt it would run at all. I'm not going to try and guess what the errors are in your real code from some other code, I'm afraid.

 

[everyColorDumb]

Declaring it doesn't give it a value.

I get the impression that is not all your actual code, since I doubt it would run at all. I'm not going to try and guess what the errors are in your real code from some other code, I'm afraid.

Here is the full UserForm2 module code. My apologies in advance as I haven't refactored the code yet, and I am new to VBA.

Option Explicit

Public Form As UserForm2

Public childFrame As MSForms.Frame

'Public barcodeVal As MsForms.TextBox
Public destLabel As MSForms.label
Public destNumLabel As MSForms.label
Public destNumLabel2 As MSForms.label

Public globalNumPlates As Integer
Dim barcodeArr() As MSForms.TextBox
Dim barcodeLabelsArr() As String


Public txtDesig As MSForms.TextBox
Public lblDesig As MSForms.label

Public chkMisc1 As MSForms.CheckBox
Public chkMisc2 As MSForms.CheckBox

Public fraRadioOption As MSForms.Frame
Public optFemale As MSForms.OptionButton
Public optMale As MSForms.OptionButton

Public lblLine As MSForms.label

Public label_VolUnits  As MSForms.label
Public label_concUnits  As MSForms.label
Public combo_volUnits As MSForms.ComboBox
Public combo_concUnits As MSForms.ComboBox


Public WithEvents btSubmit As MSForms.CommandButton

Dim sConnString As String

Private Sub OriginFrame_Click()

End Sub

Private Sub UserForm_Initialize()
    OriginFrame.Caption = "Register Info"
    OriginFrame.BorderStyle = fmBorderStyleNone
    
    Call Init(UserForm2, OriginFrame)
End Sub

Public Sub Init(myForm As UserForm2, fraMain As MSForms.Frame)
    Set Form = myForm
    Set childFrame = fraMain.Controls.Add("Forms.Frame.1")
    Dim RunSheet As Worksheet
    Set RunSheet = ThisWorkbook.Sheets("Run Info")
    
    
    
    'Extract total destination plates from "Run Info" sheet
    RunSheet.Activate
    Dim totalDestPlates As Integer
    totalDestPlates = WorksheetFunction.Max(Range("I3", Range("I3").End(xlDown)))
    
    globalNumPlates = totalDestPlates
    
    ' Formatting paramaters
    Dim top_frame As Integer: top_frame = 20
    
    'Arbitrary estimation of frame height based on number of destination plates
    Dim h_frame As Integer: h_frame = totalDestPlates * 30 + 150 '
    Dim width_barcodeField As Integer: width_barcodeField = 130
    Dim top_barcodeField As Integer: top_barcodeField = 8
    Dim left_frameMargin As Integer: left_frameMargin = 8
    Dim left_colMargin As Integer: left_colMargin = 8
    Dim left_destNumLabel As Integer: left_destNumLabel = 70
    Dim offset_barcodeField As Integer: offset_barcodeField = 90
    Dim pad_barcodeField As Integer: pad_barcodeField = 20
      
    'Space between barcode and Units section
    Dim pad_div As Integer: pad_div = 50
    
    'This keeps track of current distance from top of frame
    Dim h_curr As Long: h_curr = 10
              
    With childFrame
        .Height = h_frame
        .Width = myForm.Width - 200
        .Top = top_frame
        .Left = left_frameMargin
      
        
        With .Controls
        
        ' BARCODE SECTION
            
            'Destination Label Description. This remains static in userform.
            Set destLabel = .Add("Forms.Label.1")
            With destLabel
                destLabel.FontBold = True
                .Top = h_curr
                .Left = left_colMargin
                .Caption = "Destination Barcodes"
                .Name = "destLabel" & 1
            End With
            
            ' Destination plate number label. Loop based on number of dest plates
            
            'Initialize destination labels based on total dest plates.
            'Dim numDestPlates As Integer: numDestPlates = 4
            
            
            'Dim barcodeArr() As MSForms.TextBox
            ReDim barcodeArr(totalDestPlates)
            ReDim barcodeLabelsArr(totalDestPlates)
            ReDim destNumLabel_arr(totalDestPlates)
            Dim i As Integer
            For i = 1 To totalDestPlates
                Set destNumLabel_arr(i) = .Add("Forms.Label.1")
                    With destNumLabel_arr(i)
                        destNumLabel_arr(i).FontBold = True
                        .Top = h_curr + ((i - 1) * pad_barcodeField)
                        .Left = left_destNumLabel
                        .Caption = "#" & i
                        .Name = destNumLabel_arr(i)
                    End With
                Debug.Print destNumLabel_arr(i)
                
                UserForm2.barcodeArr(i) = .Add("Forms.TextBox.1")
                'Set barcodeArr(i) = .Add("Forms.TextBox.1")
                With barcodeArr(i)
                    .Top = top_barcodeField + (i - 1) * pad_barcodeField
                    .Left = offset_barcodeField
                    .Width = width_barcodeField
                    .Name = "barcodeString" & i
                    barcodeLabelsArr(i) = "barcodeString" & i
                    
                End With
                
            Next
            
                            
            ' Determine where the last barcode position is.
            h_curr = destNumLabel_arr(totalDestPlates).Top
            
            Debug.Print "WHERE WE NOW?!?" & h_curr
        
            ' Add a Frame for Option buttons.
            Set fraRadioOption = fraMain.Controls.Add("Forms.Frame.1")
            With fraRadioOption
                .Top = 30
                .Left = 280
                .Height = 45
                .Width = 135
                .Caption = "TBD Option"
                
                With .Controls
                    ' Add two option boxes.
                    Set optFemale = .Add("Forms.OptionButton.1")
                    With optFemale
                        .Top = 10
                        .Left = 7
                        .Width = 57
                        .Height = 18
                        .Caption = "Yes"
                    End With
                    
                    Set optMale = .Add("Forms.OptionButton.1")
                    With optMale
                        .Top = 10
                        .Left = 70
                        .Width = 57
                        .Height = 18
                        .Caption = "No"
                    End With
                End With
            End With
            
            
        ' UNITS SECTION
        
            ' Volume Units
            Set label_VolUnits = .Add("Forms.Label.1")
            With label_VolUnits
                label_VolUnits.FontBold = True
                h_curr = h_curr + pad_div
                .Top = h_curr
                .Left = left_colMargin
                .Caption = "Volume Units"
                .Name = "label_VolUnits "
            End With
            
            ' Add combo box.
            Set combo_volUnits = .Add("Forms.ComboBox.1")
            With combo_volUnits
                
                .Top = h_curr
                .Left = label_VolUnits.Left + 84
                .Width = 130
                .Height = 18
                
                .AddItem "uL"
                .AddItem "mL"
            End With
            
        
            ' Concentration Units
            Set label_concUnits = .Add("Forms.Label.1")
            With label_concUnits
            label_concUnits.FontBold = True
                h_curr = h_curr + pad_div
                .Top = h_curr
                .Left = left_colMargin
                .Caption = "Concentration Units"
                .Name = "label_concUnits "
            End With
            
            ' Add combo box.
            Set combo_concUnits = .Add("Forms.ComboBox.1")
            With combo_concUnits
                
                .Top = h_curr
                .Left = label_concUnits.Left + 84
                .Width = 130
                .Height = 18
                
                .AddItem "ug/mL"
                .AddItem "mg/mL"
            End With
            
            
            
            ' Determine where the last unit field position is.
            h_curr = label_concUnits.Top
          
            ' *****
                ' Finally, add the submit button.
            Set btSubmit = .Add("Forms.CommandButton.1")
            With btSubmit
                .Top = h_curr + 55
                .Left = 90
                .Width = 130
                .Height = 25
                .Caption = "Submit"
            End With
        
        End With
    End With
        
 
End Sub
Function GetBarcodes() As Variant
    GetBarcodes = barcodeArr
End Function

Private Sub btSubmit_Click()
    
    'Dim numDestPlates As Integer
    'Dim IsValid As Boolean: IsValid = False
    Dim BarcodeTextString As String
    Dim LocalBarcodeArr() As Variant
    Dim NumBarcodes As Integer
    Dim LandingSheet As Worksheet
    Dim RunSheet As Worksheet
    
    Set LandingSheet = ThisWorkbook.Sheets("FormSheet")
    Set RunSheet = ThisWorkbook.Sheets("Run Info")
    
    Debug.Print "Checking Volume field: " & combo_volUnits.Value
    Debug.Print "Checking first barcode field: " & Me.Controls(barcodeLabelsArr(1))
    
    Debug.Print "Global Num Plates: " & globalNumPlates
    Debug.Print "First barcode field: " & barcodeArr(1).Value
    
    Do
        If Not IsValid(globalNumPlates, Me, RunSheet, LandingSheet) Then Exit Sub
    Loop While Not IsValid(globalNumPlates, Me, RunSheet, LandingSheet)
    
    
    'Will only proceed to this if validated'
    'Unload Me
    
    'Activate Runsheet
    LandingSheet.Activate
    
    'Clear Landing Worksheet Barcode Column
    Rows(3 & ":" & LandingSheet.Rows.Count).Clear
    
    'ReDim LocalBarcodeArr(1 To globalNumPlates)
    'LocalBarcodeArr = GetBarcodes()

    'Populate barcodes
    Dim i As Integer
    For i = LBound(barcodeArr) + 1 To UBound(barcodeArr)
        Debug.Print barcodeArr(i)
        Range("A" & (i + 3)).Value = barcodeArr(i)
    Next i
    
    'Range("B3").Value = BarcodeTextBox.Value
    Range("B3").Value = combo_concUnits.Value
    Range("C3").Value = combo_volUnits.Value
    
 

    'Pass arguments to CreateOriginPlate Sub
    Call CreateOriginPlate


End Sub

Function IsValid(ByVal NumPlates As Integer, ByVal FormImport As Object, ByVal RunInfo As Worksheet, ByVal FormInfo As Worksheet) As Boolean
    
    Dim UserNumBarcodes As Integer: UserNumBarcodes = 0
    Dim BarcodeTextString As String: BarcodeTextString = "EMPTY_STRING"
    Dim LocalBarcodeArray() As String
    
    'LocalBarcodeArray() = Me.Controls(barcodeLabelsArr())
    
CheckBeginning:
    Debug.Print "Checking the form..."
    
    'Retrieve the number of plates there should be based on Run Info sheet
    Debug.Print NumPlates
    
    Debug.Print "Third barcode: " & Me.Controls(barcodeLabelsArr(3))
    
    ' Input Sanitization: Check that barcode TextBox's contain only one barcode and standardize format
    Dim i As Integer
    For i = LBound(barcodeArr) + 1 To UBound(barcodeArr)
    
        With barcodeArr(i)
            'Convert TextBox value to String
            BarcodeTextString = Me.Controls(barcodeLabelsArr(i))
            Debug.Print "Barcode: " & BarcodeTextString
            
            'Parse barcode string. Should return a 1-element array
            LocalBarcodeArray = Split(BarcodeTextString, ",")
            LocalBarcodeArray = Split(BarcodeTextString, " ")
            
            IsValid = CBool(UBound(LocalBarcodeArray) - LBound(LocalBarcodeArray) = 0)
            
            If IsValid Then
                'Standardize all letters to uppercase
                BarcodeTextString = UCase(BarcodeTextString)
                
                'Remove all whitespaces
                BarcodeTextString = Replace(BarcodeTextString, " ", "")
                
                barcodeArr(i).Value = BarcodeTextString
                
            End If
        
        End With

    Next i
      
    
    'Activate Runsheet
    RunInfo.Activate
    
    'Check that number of submitted barcodes equals number of destination plates
    'numDestPlates = WorksheetFunction.Max(Range("I3", Range("I3").End(xlDown)))
        
    'Confirm number of user input barcodes must match number of dest plates
    'IsValid = CBool(globalNumPlates = UserNumBarcodes)
    
    
     'Number of user input barcodes must match number of dest plates
    If Not IsValid Then
        MsgBox "Number of barcodes don't match!" & Chr(10) & _
               "Please check and try again.", 48, "Not Matched"
            barcodeArr(1).SetFocus
        
    Else
        Debug.Print "All Match..."
      
        If combo_concUnits.Value = "" Or combo_volUnits.Value = "" Then
            IsValid = False
            MsgBox "Please select units for the volume and concentration."
        Else
            'Activate Runsheet
            FormInfo.Activate
            
            'Clear Landing Worksheet Barcode Column
            Rows(3 & ":" & FormInfo.Rows.Count).Clear
            
        
            'Populate barcodes
            Dim j As Integer
            For j = LBound(barcodeArr) + 1 To UBound(barcodeArr)
                Debug.Print barcodeArr(j)
                Range("A" & (j + 2)).Value = barcodeArr(j)
            Next j
            
            'Range("B3").Value = BarcodeTextBox.Value
            Range("B3").Value = combo_concUnits.Value
            Range("C3").Value = combo_volUnits.Value
    
            Unload Me
        End If
      
        'Unload Me
    End If
    

End Function

 

[RoryA]

UserForm2.barcodeArr(i) = .Add("Forms.TextBox.1")

should be:

Set barcodeArr(i) = .Add("Forms.TextBox.1")

Also, you should use:

Call Init(Me, OriginFrame)

or just:

Init Me, OriginFrame

rather than:

Call Init(UserForm2, OriginFrame)