Bizarre behavior: Populating A Userform from a Range only displays data in textboxes every other time the userform is opened

[Cyberchipz]

I've created many userforms for various things... and I've never seen this before...
I've created a simple form of textboxes from which I populate it from a named cell (which I use as a range)... the first time the userform opens... it's blank... I close it, open it again, and the 2nd time it will populate the textboxes..
I can repeat this and everytime it's the same pattern... nothing 'shows" in the text boxes... anything I type does not get placed back into the range... the 2nd time, everything works exactly as I expect...
I'm clueless... The code is simple... I've used similar in some very complex forms... and never had this before... Here's the only code in the form.

To populate the textboxes 1-52 when I first open the form:

Private Sub UserForm_Initialize()
    Debug.Print "ENTER: In frmNotes Initialize"
    Dim txtName As String, strN As String, intN As Integer
    Dim txt As Range, ctr As Control
    Set txt = Range("Notes!Notes")
   
    For Each ctr In frmNotes.Controls
        txtName = ctr.Name
        If Left(txtName, 7) = "TextBox" Then
            If Len(txtName) = 8 Then 'one number, right 1, else right 2
                strN = Right(txtName, 1)
            Else
                strN = Right(txtName, 2)
            End If
            intN = Trim(CStr(strN))
            ctr.Value = txt(intN)
            DoEvents
            Debug.Print "txt: "; txt(intN)  <=Here is the code that puts the data in the range into the textbox.
        End If
    Next
    Debug.Print "EXIT: In frmNotes Initialize"
    DoEvents
   
End Sub

Then when I close the form I rewrite all the boxes back into the range...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Debug.Print "In frmNotes Activate"
    Dim txtName As String, strN As String, intN As Integer
    Dim txt As Range, ctr As Control
    Set txt = Range("Notes")
   
    For Each ctr In frmNotes.Controls
        txtName = ctr.Name
        If Left(txtName, 7) = "TextBox" Then
            If Len(txtName) = 8 Then 'one number, right 1, else right 2
                strN = Right(txtName, 1)
            Else
                strN = Right(txtName, 2)
            End If
            intN = Trim(CStr(strN))
            txt(intN) = ctr.Value   <= Here is the same code but putting it back into the range
        End If
    Next

End Sub

I call the open form routine, which I use for everything... and its during testing phase linked to a button on a sheet I use...
The first time I show the form... it is always empty...
the 2nd time I show the form... it is always populated with the data in the range. and puts any changes back...
Here's the startup code...
******************************************************************************************************************************

Sub GetNotes()
'
' GetNotes Macro
'
    ShowForm "frmNotes", vbModeless
'
End Sub

******************************************************************************************************************************

And ShowForm is the following code I've been using for about 4 months, and works great!

I know this code works... but am including it for clarity... it's not my problem...

******************************************************************************************************************************

Sub ShowForm(FormName As String, Optional Modal As FormShowConstants = vbModeless)
        Dim obj As Object 'This will be the object Form
        Dim Status As Variant
       
        'If Form is loaded, show it, otherwise, load it and intiialize the form
       
        For Each obj In VBA.UserForms
            If StrComp(obj.Name, FormName, vbTextCompare) = 0 Then
                ' Put any pre-opening code here
                obj.Show Modal
                Exit Sub
            End If
        Next obj
       
        'Not loaded, then add and load
        ' If we make it here, the form named by FormName was
        ' not loaded, and thus not found in VBA.UserForms.
        ' Call the Add method of VBA.UserForms to load the
        ' form and then call Show to show the form.
       
        With VBA.UserForms
'            On Error Resume Next
            Err.Clear
            Set obj = .Add(FormName)
            If Err.Number <> 0 Then
                If Err.Number = 424 Then GoTo LoadForm 'Not already loaded, so skip load and initialize
                MsgBox "Err: " & CStr(Err.Number) & "   Description: " & Err.Description
                Stop
                Err.Clear

                Resume Next 'You pick Chip... argh, same ole problems after crash
                Exit Sub
            End If
LoadForm:  'Form is now loaded you can work with it.
            Err.Clear
           ' Load (Obj)
 '           if DMode then debug.print  "Working with "; Obj.Name
           
            'Trying add data to form before showing
'            Status = VBA.UserForms.Add(FormName).Show
'            UserForms(FormName).UserForm_Initialize

            'These must be common to all forms
            
             Select Case FormName
                Case "frmGetTeams"
                    InitializeNames obj
                Case "frmGetDamage"
                    InitializeNames obj
                Case "frmHelp1"
                    'No Data
                Case "frmMainMenu1"  'Main Menu"
                    InitializeNames obj
                Case "frmMainMenu2"  'Main Menu"
                    InitializeNames obj
                Case "frmNotes"
                   ' No external code to form needed...
                Case "frmPickHero" 'Main Hero Selection Form
             Case Else
                    MsgBox "This form is in test mode. Please Save before trying out your initialization code"
                    Stop
                    'Enter name of test subs below here
            End Select



            Select Case Modal
                Case vbModal
                    'other code for modal form enter here
                    obj.Show vbModal
                Case vbModeless 'default
                    'other code for modeless form enter here
                    obj.Show vbModeless
            End Select
              
        End With
        DataSaved = False
        Exit Sub
       
HandleError:
       
        MsgBox "Err: " & CStr(Err.Number) & "   " & Err.Description
        Stop
        Err.Clear
        Resume Next

    End Sub

******************************************************************************************************************************


If anyone has ever seen this behavior and figured it out... please... let me know... I'm stymied!

I click a link to the open form listed above: (image with button below)
The first time the userform opens the textboxes are empty! (image below)
The 2nd time it is populated... (image below)
Don't know why; and yes, the code is as simple as shown..
I tested it by adding a new text line... into a textbox shown.. when blank... it does not get saved to the Range.
when the data shows... the textboxes work as expected... as does everything else... the data is saved to the range...
Any idea what is happening???

 

[Cyberchipz]

In case anyone asks... yes... the cycle repeats... Open Blank... then open populated... rinse repeat... over and over... nothing changes as far as I can see... so nothing to debug..

 

[Cyberchipz]

Well, as usual, I found a workaround... and... well, I guess I'll just have to get used to this quirky kind of behavior from now on.....
solution.... Ha! What else.... Textbox#.controlsource
But, what I hate about control source was the need to put names to everything... and until now... using addresses meant things could change... but.... alas no... only one Named Range... the first cell of the range I want to use... and now I can just stick that anywhere... and Yay! It works... but I still have to use .ControlSource. That's OK, cause in the end I now really like control source... and assigning it on the fly. Yipee, no more naming a thousand boxes. Not that I ever did that... lol

Private Sub UserForm_Initialize()
    If DMode Then Debug.Print "ENTER: In frmNotes Initialize"
    Dim txtName As String, strN As String, intn As Integer
    Dim txt As Range, ctr As Control, tmpControl As String, strAddr As String
    On Error GoTo HandleError
    
    Set txt = Range("MyNotes!Notes")
    
    For Each ctr In frmNotes.Controls 'after hitting here.... always starts Initialization and runs starting code again, returning to next command on return
        txtName = ctr.Name 'why Microsoft why?  Surely all this looping... (sigh) I should probably just ignore it; but, it does cause problems!  Like no one can help if I change the wrong thing! :-/
        If Left(txtName, 7) = "TextBox" Then
            If Len(txtName) = 8 Then
                strN = Right(txtName, 1)
            Else
                strN = Right(txtName, 2)
            End If
            intn = Trim(CStr(strN))
            strAddr = txt(intn).Address
            ctr.ControlSource = "MyNotes!" & strAddr
        End If
    Next 'And another thing... why can't we order the dang controls in the order we want them... or at least something that makes sense.. why Microsoft why?
        GoTo GracefulExit
        
HandleError:
        MsgBox "Err: " & CStr(Err.Number) & "   " & Err.Description
        Stop
        Err.Clear
        Resume Next
GracefulExit:
   If DMode Then Debug.Print "EXIT: In frmNotes Initialize"



End Sub