CODEWITHSUNDEEP
vbaexcelvbscript
nandesh kalyankar
nandesh kalyankar

Reputation: 302

How to Display a filtered value in a text box of userform in vba at runtime

Thank you for reviewing my question :) I am creating a text box on a userform at run time & wants to display filtered data on it. I am running to many run time errors for following code. for the first text box, I want to show a fatalcount, for second text box, it should be Majorcount & for last text box, it should be minorcount. Can someone direct me on right path? thanks in advance

Private Sub UserForm_Initialize()

Set sh = ThisWorkbook.Sheets("Testing")

sh.Range("F21").Activate

With sh

    fatalcount = WorksheetFunction.CountIf(Range("F:F"), "Fatal")
    'MsgBox fatalcount
    Majorcount = WorksheetFunction.CountIf(Range("F:F"), "Major")
    'MsgBox Majorcount
    Minorcount = WorksheetFunction.CountIf(Range("F:F"), "Minor")

    'MsgBox Minorcount

'Add a text box at run time

Dim txtB1 As Control

Dim i

For i = 0 To 5
    Set txtB1 = Me.Controls.Add("Forms.TextBox.1")
    With txtB1
        .Name = "chkDemo" & i
        .Height = 20
        .Width = 100
        .Left = 12
        .Top = 15 * i * 2
        .Text.i = fatalcount    ' problem lines 
        .Text.i 1 = Majorcount
        .Text.i 2 = Minorcount

    End With
Next i
End Sub

Upvotes: 0

Views: 472

Answers (1)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Hope this is what you looking for.

Private Sub UserForm_Initialize()
    Set sh = ThisWorkbook.Sheets("Testing")
    sh.Range("F21").Activate
    With sh
        fatalcount = WorksheetFunction.CountIf(Range("F:F"), "Fatal")
        Majorcount = WorksheetFunction.CountIf(Range("F:F"), "Major")
        Minorcount = WorksheetFunction.CountIf(Range("F:F"), "Minor")
    End With
    Dim txtB1 As Control
    Dim i
    For i = 0 To 2
        Set txtB1 = UserForm1.Controls.Add("Forms.TextBox.1")
        With txtB1
            .Name = "chkDemo" & i
            .Height = 20
            .Width = 100
            .Left = 12
            .Top = 15 * i * 2
        End With
    Next i
    Dim tbox As Control
    For Each tbox In UserForm1.Controls
        If tbox.Name = "chkDemo0" Then
            tbox.Value = fatalcount
        ElseIf tbox.Name = "chkDemo1" Then
            tbox.Value = Majorcount
        ElseIf tbox.Name = "chkDemo2" Then
            tbox.Value = Minorcount
        End If
    Next
End Sub

Upvotes: 1

Related Questions