Results 1 to 3 of 3

restrict a search to either text entries or date entries when loading dynamic userform

Threaded View

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    restrict a search to either text entries or date entries when loading dynamic userform

    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
    Attached Files Attached Files
    Last edited by nigelog; 09-28-2017 at 06:08 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Display column entries in row as vertical entries in a userform listbox
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-22-2016, 08:10 AM
  2. Replies: 1
    Last Post: 05-13-2016, 04:59 AM
  3. [SOLVED] Restrict entries in textboxes on userform
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-25-2015, 02:19 PM
  4. [SOLVED] Creating a dynamic array to search for specific entries based on multiple criteria
    By Travisty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2015, 12:30 AM
  5. [SOLVED] How to search for cells with no entries in userform?
    By auswtz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-07-2015, 07:28 AM
  6. Use Userform TextBox Entries to Search Worksheet
    By jasperhuang93 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2012, 09:16 AM
  7. Restrict entries according to date in another cell
    By Canajun in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-10-2008, 12:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1