The attached workbook loads data from a named range in a worksheet "BNM" and when there was only date entries the rows included in the count to size the userform worked correctly and restricted the row number to just the dates ignoring blanks for every column in the named range tblEmployeeData9
It is now required to have a seperate userform which would display who has not been inducted to a site
I tried setting the Isdate to look for blanks instead but then it counted every blank row. To restrict it to the relevant column I added "no". This works fine.
Click Bord na Mona and the list of inducted is shown - close and then select checkbox before click again and the "no's" are shown
AS you can see by the message box though, the row count is counting for every cell with an entry, whether date or text, and making the form too large
Any ideas to restrict the criteria to count JUST dates or text entries so the row count is correct
Load code is in userform F18_BNM "Create labels" and area to focus on is yellow in worksheet "BNM"
Any ideas most appreciated, in code you will see I've tried a few things
Private Sub CreateLabels()
Dim iPitch_Column As Integer
Dim iEmployeeNo As Integer
Dim iColumnNo As Integer
Dim iColumnNo2
Dim iPitch_Row As Integer
Dim iRowNo As Integer
Dim txt As MSForms.TextBox
Dim lbl As MSForms.Label
iPitch_Row = miCONTROL_HEIGHT + miSEPARATION
iPitch_Column = (miWIDTH_LABEL + miSEPARATION + miWIDTH_TEXTBOX) + miSEPARATION_COLUMN
For iColumnNo = miCOLUMN__SITE_1 To miCOLUMN__SITE_5
iRowNo = 0
For iEmployeeNo = LBound(mvaEmployeeData, 1) + 1 To UBound(mvaEmployeeData, 1)
''''+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Dim txt2 As Variant
'txt2 = (mvaEmployeeData(iEmployeeNo, iColumnNo))
'If Not IsNumeric(txt2) Then txt2 = 0
'''''++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim isMatch As Boolean
Select Case True
Case bnmoP_1.Value
isMatch = IsDate(mvaEmployeeData(iEmployeeNo, iColumnNo))
Case bnmoP_2.Value
isMatch = (mvaEmployeeData(iEmployeeNo, iColumnNo)) = "no"
End Select
If VarType(mvaEmployeeData(iEmployeeNo, iColumnNo)) = vbString Then
''''''''''''++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If isMatch Then
iRowNo = iRowNo + 1
Set lbl = Me.Controls.Add(bstrProgID:="Forms.Label.1")
With lbl
.Caption = mvaEmployeeData(iEmployeeNo, 1)
.Height = miCONTROL_HEIGHT
.Width = miWIDTH_LABEL
.Left = miLEFT__FIRST_COLUMN + (iPitch_Column * (iColumnNo - 2))
.Top = miTOP__FIRST_ROW + (iPitch_Row * (iRowNo - 1))
End With
Set txt = Me.Controls.Add(bstrProgID:="Forms.TextBox.1")
With txt
.TextAlign = fmTextAlignCenter
.Locked = True
.Height = miCONTROL_HEIGHT
.Width = miWIDTH_TEXTBOX
.Value = Format(mvaEmployeeData(iEmployeeNo, iColumnNo), "dd mmm yyyy")
.Left = lbl.Left + miWIDTH_LABEL + miSEPARATION
.Top = miTOP__FIRST_ROW + (iPitch_Row * (iRowNo - 1))
End With
End If
End If
Next iEmployeeNo
Next iColumnNo
End Sub
Bookmarks