Userform textbox code

**CJ-22** · 10-29-2008, 12:13 PM

I have a userform and I want a textbox that counts cells J7,M7,P7,S7,V7. Does anyone know how to write this code and where to put it. All I want is the textbox to show how many cells of these five have data in them and I want it to update automatically.

Thanks for any help.

**royUK** · 10-29-2008, 12:21 PM

This will show how many cells contain data in the range, don't know what you mean by update automatically

Private Sub UserForm_Initialize()
Me.TextBox1.Value = Application.WorksheetFunction.CountA(Range("J7, M7, P7, S7, V7"))
End Sub

**CJ-22** · 10-29-2008, 01:12 PM

Thanks for the reply. That works, but with one problem. I need this to update in real time. This only updates when the userform is opened. Is there a way to update this anytime the data changes?

**royUK** · 10-29-2008, 01:15 PM

How can you change the cells with the userform open, unless it is modeless.

**CJ-22** · 10-29-2008, 01:58 PM

The userform itself changes the cells. Each time I press a button on the userform, the cells update. I need the textbox to update each time the cells update.

**royUK** · 10-29-2008, 02:00 PM

Just add this to the end of your button's code

UserForm_Initialize

**CJ-22** · 10-29-2008, 03:20 PM

That works great, but I have one more question. Is there any way to show blank instead of 0 when the cells are empty?

**royUK** · 10-30-2008, 04:42 AM

The code only needs slight modification

  Dim lChk   As Long
    lChk = Application.WorksheetFunction.CountA(Range("J7, M7, P7, S7, V7"))
    If lChk > 0 Then
        Me.TextBox1.Value = lChk
    Else: Me.TextBox1.Value = vbNullString
    End If