Count number of textbox with specific name

[dandelion]

Hi, is there any way to count number of textbox with specific name in Excel? For example, I have a large of design containing many trays (pics are illustration only). I would like to count number of Racks in this sheet? The textboxes names are Textbox 1, textbox 2....

 

[Domenic]

Assuming that the relevant textboxes are named TextBox 1 through TextBox 7, you can use the following custom function to get your count. First add the following code to your workbook in a regular module (Visual Basic Editor >> Insert >> Module)...

Function GetRackCount(ByVal sheetname As String, ByVal criteria As String) As Long

    Application.Volatile

    Dim rackCount As Long
    Dim i As Long
 
    rackCount = 0
    For i = 1 To 7
        If UCase(Worksheets(sheetname).TextBoxes("TextBox " & i).Text) = UCase(criteria) Then
            rackCount = rackCount + 1
        End If
    Next i
 
    GetRackCount = rackCount
 
End Function

Then you can use any of the following worksheet formulas...

=GetRackCount("Sheet1", "Rack")

=GetRackCount("Sheet1", A2)  'where A2 contains Rack

=GetRackCount(A2, B2) 'where A2 contains Sheet1 and B2 contains Rack

Change the sheet name and cell references accordingly. Note that in order to update the formula you'll need to press either Shift+F9 to calculate the worksheet or F9 to calculate the workbook. Also, the formula will automatically update when there's a change in any cell within the workbook.

Hope this helps!