Looping through textboxes in userform controls to count and identify empty textboxes

[IsraelVow]

Hi ?



I might not express the problem as I should but pls bare with me, here it goes



I'm trying to create a user data entry form to capture services per patient and save entry to my spreadsheet (database)
The patient details gets populated automatically without user entry (prior to a pre-entry form, created in the app)
In the database, There's a single column to capture every service entered
So I decided that when a user enters multiple services and clicks on save
The spreadsheet (database) will capture the patient details per every service
That is, on one row .... there will be patient detail and then a single service
On the second row... the details will repeat but the service will be changed based on the second service provided by the user
And so on

Problem

(I don't know how to loop through the empty textboxes to identify the number of rows patient details will be repeated when user clicks save.

If I get that, then I'll work my way to populate the services by each count of rows)

Here's the code:

Ps: returned an error on "for each ccont In Me.Controls"

Private Sub Save_Click()

Dim zh As Worksheet
Dim LaastRow As Long
Dim x As Integer
Dim ccont As Controls
Dim y As Long

Set zh = ThisWorkbook.Sheets("PublicDatabase")
LaastRow = [Counta(PublicDatabase!A:A)] + 1

For Each ccont In Me.Controls
If Me.txt_QtyAccomodation.Value = "" Or _
Me.txt_QtyConsultation.Value = "" Or _
Me.txt_QtyHaematology.Value = "" Or _
Me.txt_QtyAccomodation.Value = "" Or _
Me.txt_QtyHistopathology.Value = "" Or _
Me.txt_QtyNursingcare.Value = "" Or _
Me.txt_QtyOthers1.Value = "" Or _
Me.txt_QtyOthers2.Value = "" Or _
Me.txt_QtyMicrobiology.Value = "" Or _
Me.txt_QtyReviews.Value = "" Then
Exit For

Else

y = y + 1

End If

For x = 1 To LaastRow + y

With zh
.Cells(LaastRow, 1) = LaastRow - 1
.Cells(LaastRow, 2) = "DEFENCE HEALTH MAINTENANCE LTD (PUBLIC)"
.Cells(LaastRow, 3) = PubDefense.Txt_NameOfpDEFENSE.Value
.Cells(LaastRow, 4) = "Nil"
.Cells(LaastRow, 5) = PubDefense.Txt_RefferrinProviderDefense.Value
.Cells(LaastRow, 6) = PubDefense.Txt_NHISNoDefense.Value
.Cells(LaastRow, 7) = PubDefense.Txt_AuthorizCodeDefense.Value
.Cells(LaastRow, 8) = PubDefense.txt_HmoCodeDefense.Value
.Cells(LaastRow, 9) = PubDefense.Txt_DateOftreatmentDefense.Value
.Cells(LaastRow, 10) = PubDefense.Txt_DateOFadmDefense.Value
.Cells(LaastRow, 11) = PubDefense.Txt_DateOFdisDefense.Value
.Cells(LaastRow, 12) = "Nil"
.Cells(LaastRow, 13) = "Nil"
.Cells(LaastRow, 14) = "Nil"
.Cells(LaastRow, 15) = "Nil"
.Cells(LaastRow, 16) = "Nil"
.Cells(LaastRow, 17) = PubDefense.Txt_DiagnosisDefense.Value
.Cells(LaastRow, 18) = PubDefense.Txt_PatAddressDEfense.Value

End With

Next x
Next ccont

MsgBox "entered"

End Sub

 

[JEC]

This is how you count emtpy textboxes

 For Each ctr In Me.Controls
   If TypeName(ctr) = "TextBox" Then
     If ctr.Value = "" Then x = x + 1
   End If
 Next
 MsgBox x

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Vba: Looping through userform controls to count and identify empty textboxes
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[IsraelVow]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Vba: Looping through userform controls to count and identify empty textboxes
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thank you sir, my apologies for the omission

Certainly won't happen again ?