Value in userform TextBox incorrect due to initalize option being used

[ipbr21054]

Hi,
I am using the code below.
As you can see its USERFORM_INITIALIZE format.

Obviously when i first open the worksheet & select the userform i see the correct valuer in the TextBox.
If i then add a new row to the worksheet & open the userform again the result is still the same in the TextBox.
If i open another worksheet then come back to open this worksheet the userform then shows the correct value in the TextBox
I know its because im using Initalize but what should i be using so its always updating itself to show the correct value in the TextBox


How do i Initialize the code witrhout having to close & open worksheet

Private Sub UserForm_Initialize()
    With ThisWorkbook.Worksheets("DATABASE")
        Dim data As Variant
        data = .Range("J6:K" & .Cells(.Rows.Count, "J").End(xlUp).Row).Value
    End With
  
    ReDim arr(0 To 1, 0 To UBound(data) - 1) As String
  
    Dim itm As String
    Dim cnt As Long
    Dim i As Long
  
    cnt = 0
    For i = LBound(data) To UBound(data)
        itm = data(i, 1)
        If itm Like "[A-Za-z]###" Then
            arr(0, cnt) = itm
            arr(1, cnt) = data(i, 2)
            cnt = cnt + 1
        End If
    Next i
  
    If cnt > 1 Then
        ReDim Preserve arr(0 To 1, 0 To cnt - 1)
        Sort2DArray arr()
        Me.ListBox1.List = Application.Transpose(arr())
    ElseIf cnt > 0 Then
        Me.ListBox1.Column = arr()
    End If
   
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("DATABASE")
  
    ws.Range("AB6").CurrentRegion.ClearContents

    With Me.ListBox1
        ws.Range("AB6").Resize(.ListCount, .ColumnCount).Value = .List
    End With
   
    With ThisWorkbook.Worksheets("DATABASE")
    TextBox1 = Range("A1")
    End With
   
End Sub

 

[ipbr21054]

Or is it because of this Activate code

Private Sub Worksheet_Activate()
 Range("A6").Select
 DatabaseOpeningForm.Show
 

Set Rng = Range("AB6:AB500")

Count = 0

For i = 1 To Rng.Rows.Count

If Application.WorksheetFunction.CountIf(Rng, Rng.Cells(i, 1)) = 1 Then

Count = Count + 1

End If

Next i

Range("A1") = Count

End Sub

 

[dmt32]

hi,
untested but see if this update to your code resolves

Private Sub UserForm_Initialize()
    Dim ws          As Worksheet
    Dim data        As Variant
    Dim itm         As String
    Dim cnt         As Long
    Dim i           As Long
    
    Set ws = ThisWorkbook.Worksheets("DATABASE")
    
    data = ws.Range("J6:K" & ws.Cells(ws.Rows.Count, "J").End(xlUp).Row).Value
    
    ReDim arr(0 To 1, 0 To UBound(data) - 1) As String
    
    cnt = 0
    For i = LBound(data) To UBound(data)
        itm = data(i, 1)
        If itm Like "[A-Za-z]###" Then
            arr(0, cnt) = itm
            arr(1, cnt) = data(i, 2)
            cnt = cnt + 1
        End If
    Next i
    
    If cnt > 1 Then
        ReDim Preserve arr(0 To 1, 0 To cnt - 1)
        Sort2DArray arr()
        Me.ListBox1.List = Application.Transpose(arr())
    ElseIf cnt > 0 Then
        Me.ListBox1.Column = arr()
    End If
    
    ws.Range("AB6").CurrentRegion.ClearContents
    
    With Me.ListBox1
        ws.Range("AB6").Resize(.ListCount, .ColumnCount).Value = .List
    End With
    
    Me.TextBox1 = ws.Range("A1")
    
End Sub

Note:
UserForm_Initialize event only triggers when the form is first loaded into memory.
It does not trigger if you hide & then show the userform at a later stage. If this is a requirement in your application then you should consider placing required part of your code in the UserForm_Activate event

Dave

.

 

[ipbr21054]

No,
This is what i have in use / how i test it.
I click the worksheet & my opening userform is shown with various command buttons.
I select HONDA KEY CODES, the userform in question opens & i see a value in the TextBox.

So i dont have to keep closing & opening the worksheet i also have a command button that opens the same userform HONDA KEY CODES
So at present the value in the TextBox is 99

I add a new row to my worksheet values & in cell J384 i type Z123 & in cell K384 i type 123456-654321
It is now at this point im expecting things to update as ive added another value.
So on the worksheet i click the command button & the userform HONDA KEY CODES opens BUT the value is still 99 where im expecting it to be 100

I click the command button / close userform many times BUT still value is 99

I open another worksheet then i go back & open this worksheet in question again.
MY opening userform is shown, i click the command button to open the HONDA KEY CODES but the value is 99
I close this userform & press the command button on the worksheet,the userform opens & 100 is shown.

Ive also noticed that if i delete Z123 & 123456-654321 from the worksheet,save then close the worksheet im expecting the value to be back to 99
I open the worksheet, select from opening userform but i see 100, close this userform & use command button on worksheet where i then see 99

 

[dmt32]

If you are unloading your userform from memory & the update did not resolve your issue, then I can only suggest with a complex project, that you place copy of your workbook in a file sharing site like dropbox & provide a link to it - maybe others here can have a look for you

Dave

 

[ipbr21054]

Maybe look at it a different way then as i was attempting to fix this myself so lets go back a step / day or two.
Attached image of the userform.

You will see values shown in the Listbox,some a repeated once or more so im trying to get a exact value which doesnt include duplicates.
Worksheet column J has various values BUT im only intereste in the ones like shown in Listbox. 1 letter follwed by 3 numbers.
The code in use populates the Listbox perfect with the codes BUT like i say it also includes some duplicates using the same number off my worksheet.
So im just after the true unique value as looking at the screen shot you will see the code K237 is shown twice,reason being K237 is on my worksheet twice thus the TextBox value is 99 BUT should be 98 etc

Maybe you know a code which we could use to ONLY populate the ListBox with the value ONCE no matter how many times its on my worksheet.

Many Thanks.

I was trying myself to fix it so yes maybe to complex because i attacked it thw rong way as i could get unique value into ListBox

Current codes on userform.

Private Sub UserForm_Initialize()
    Dim ws          As Worksheet
    Dim data        As Variant
    Dim itm         As String
    Dim cnt         As Long
    Dim i           As Long
    
    Set ws = ThisWorkbook.Worksheets("DATABASE")
    
    data = ws.Range("J6:K" & ws.Cells(ws.Rows.Count, "J").End(xlUp).Row).Value
    
    ReDim arr(0 To 1, 0 To UBound(data) - 1) As String
    
    cnt = 0
    For i = LBound(data) To UBound(data)
        itm = data(i, 1)
        If itm Like "[A-Za-z]###" Then
            arr(0, cnt) = itm
            arr(1, cnt) = data(i, 2)
            cnt = cnt + 1
        End If
    Next i
    
    If cnt > 1 Then
        ReDim Preserve arr(0 To 1, 0 To cnt - 1)
        Sort2DArray arr()
        Me.ListBox1.List = Application.Transpose(arr())
    ElseIf cnt > 0 Then
        Me.ListBox1.Column = arr()
    End If
    
    ws.Range("AB6").CurrentRegion.ClearContents
    
    With Me.ListBox1
        ws.Range("AB6").Resize(.ListCount, .ColumnCount).Value = .List
    End With
    
    Me.TextBox1 = ws.Range("A1")
    
End Sub

ALSO

Sub Sort2DArray(ByRef arr() As String)

    Dim tmp1 As String
    Dim tmp2 As String
    Dim i As Long
    Dim j As Long
   
    For i = LBound(arr, 2) To UBound(arr, 2) - 1
        For j = i + 1 To UBound(arr, 2)
            If UCase(arr(0, i)) > UCase(arr(0, j)) Then
                tmp1 = arr(0, i)
                tmp2 = arr(1, i)
                arr(0, i) = arr(0, j)
                arr(1, i) = arr(1, j)
                arr(0, j) = tmp1
                arr(1, j) = tmp2
            End If
        Next j
    Next i
   
End Sub