Count Sheets based on Cell Values

[SimmonsDeux]

Guys,
I have a userform (frmNavigation) with labels that I want to tally up the sheets based on the value of cell b6. This cell will always be one of three values (Cat Parts, Manufacturing, Imports) and I have 3 Labels (Label 9, label 10, label 11) that I would like to display these counts.

 

[Roderick_E]

How do you know which total B6 is showing? Cat Pats, Manfc, or Imports?

 

[SimmonsDeux]

How do you know which total B6 is showing? Cat Pats, Manfc, or Imports?

I manually Entered each cel value. I'm looking for a CountIF equivalent in VBA that counts how many sheets contain "Cat Parts" in cel B6, How many sheets contain "Imports" in B6, and How many sheets contain "Manufacturing" in B6.

 

[Roderick_E]

Why not just loop through all the sheets within the workbook and check it that way?

Option Compare Text 'ignore text case
Dim cparts as long
Dim manf as long
Dim impts as long
Dim Current As Worksheet
For Each Current In Worksheets
'cat parts count
if instr(Current.range("B6"), "Parts") then
cparts = cparts + 1
endif
'manf count
if instr(Current.range("B6"), "Manu") then
manf = manf + 1
endif
'impts count
if instr(Current.range("B6"), "Imp") then
impts = impts + 1
endif
next
Me.Label9.caption = cparts
Me.Label10.caption = manf
Me.Label11.caption = impts