Function count if for text in textboxes VBA Excel

[fher9728]

Hi,
I am doing a userform where I have 8 textboxs named ddc, example: ddc1, ddc1...ddcn.
then I have another textbox name quantcoup, that is the textbox I want to receive the count if of everyone of the ddcs that have text in it throughout a command button. Also, I want that the count if detect it as text, but the vba code I used is not working for me, the code is the next:

       Private Sub CommandButton2_Click()


On Error Resume Next
quantcoup.Text = Abs((ddc1.Value = "*") + (ddc2.Value = "*") + (ddc3.Value = "*")+ (ddc4.Value = "*")+ (ddc5.Value = "*")+ (ddc6.Value = "*")+ (ddc7.Value = "*")+ (ddc8.Value = "*"))

End Sub

I´m new at vba and Ive been doing research in the internet but I can´t get a working method for this, if anyone could help it would be great.
Thanks

 

[DanteAmor]

Hi and welcome to the board!

How about:

Private Sub CommandButton2_Click()
  Dim i As Long
  quantcoup.Value = ""
  For i = 1 To 8
    quantcoup.Value = Val(quantcoup.Value) + Abs(Me.Controls("ddc" & i).Value <> "")
  Next
End Sub

 

[fher9728]

Hi and welcome to the board!

How about:

Private Sub CommandButton2_Click()
  Dim i As Long
  quantcoup.Value = ""
  For i = 1 To 8
    quantcoup.Value = Val(quantcoup.Value) + Abs(Me.Controls("ddc" & i).Value <> "")
  Next
End Sub

Thank you so much, is working perfect, could you explain why do you use val(quantcoup.value) & Abs(Me.Controls("ddc" & i).Value <> "") or what is the logic beneath this part of the code.
thanks.

 

[DanteAmor]

Thank you so much, is working perfect, could you explain why do you use val(quantcoup.value) & Abs(Me.Controls("ddc" & i).Value <> "") or what is the logic beneath this part of the code.
thanks.

This returns 1 or 0
Abs(Me.Controls("ddc" & i).Value <> "")

This returns the numerical value of the textbox
Val(quantcoup.Value)


e.g.
dd1 = "x"
dd2 = ""
dd3 = "y"

sequence:

dd1 = "x"
i = 1
quantcoup.Value = Val(0) + Abs(Me.Controls("ddc" & i).Value <> "")
quantcoup.Value = Val(0) + 1
quantcoup.Value = 0 + 1
quantcoup.Value = 1

dd2 = ""
i = 2
quantcoup.Value = Val(1) + Abs(Me.Controls("ddc" & i).Value <> "")
quantcoup.Value = Val(1) + 0
quantcoup.Value = 0 + 1
quantcoup.Value = 1

dd2 = "y"
i = 3
quantcoup.Value = Val(1) + Abs(Me.Controls("ddc" & i).Value <> "")
quantcoup.Value = Val(1) + 1
quantcoup.Value = 1 + 1
quantcoup.Value = 2
________________________________________________

 

[fher9728]

This returns 1 or 0
Abs(Me.Controls("ddc" & i).Value <> "")

This returns the numerical value of the textbox
Val(quantcoup.Value)


e.g.
dd1 = "x"
dd2 = ""
dd3 = "y"

sequence:

dd1 = "x"
i = 1
quantcoup.Value = Val(0) + Abs(Me.Controls("ddc" & i).Value <> "")
quantcoup.Value = Val(0) + 1
quantcoup.Value = 0 + 1
quantcoup.Value = 1

dd2 = ""
i = 2
quantcoup.Value = Val(1) + Abs(Me.Controls("ddc" & i).Value <> "")
quantcoup.Value = Val(1) + 0
quantcoup.Value = 0 + 1
quantcoup.Value = 1

dd2 = "y"
i = 3
quantcoup.Value = Val(1) + Abs(Me.Controls("ddc" & i).Value <> "")
quantcoup.Value = Val(1) + 1
quantcoup.Value = 1 + 1
quantcoup.Value = 2
________________________________________________

Great explanation, thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.