string used to hide sheets

[mc136355]

Hi guys

looking for a hint on what my problem is with the following code.
I searched all worksheets and pulled back 3 sheets beginning with 1234567 which are in a string
I need to now unhide sheets with the names from the string
e.g 1234567 CAT to unhide sheet called 1234567 CAT

any help appreciated
thanks
MC


Function Show_Sheet(ByVal msg As String) As Boolean
Dim kitten As Worksheet
Dim cat As Variant
' cat is equal to string of names 1234567 CAT, 1234567 CATTY, 1234567 CATNIP
cat = Mid(msg, 1)

'MsgBox Mid(ws, 1)
MyOrder = "Sheet Exists! Do you wish to show records?"
Ans = MsgBox(MyOrder, vbQuestion + vbYesNo, "???")
If Ans = vbNo Then
MsgBox "Not show records" 'end
Else
For Each kitten In cat
cat.Visible = xlSheetVisible 'need to unhide all sheets where name is 1234567 CAT, 1234567 CATTY, 1234567 CATNIP
Next

End If
End Function

 

[jproffer]

What are you expecting it to do? Do you know the difference between a Function and Subroutine? A function has certain rules it has to follow, one of which is it can't hide/unhide sheets. IF it were a true function, it still wouldn't show any result because you don't ever define that result.

Anyhow, what are you trying to do? Someone can help you but what I'm thinking you need is a subroutine, not a function.

 

[NoSparks]

Is this what you're trying to do ?

Sub ShowSheets()

    Dim MyOrder As String
    Dim Ans As Integer
    Dim cat As String
    Dim ray As Variant
    Dim i As Integer

' cat is equal to string of names 1234567 CAT, 1234567 CATTY, 1234567 CATNIP
cat = "1234567 CAT, 1234567 CATTY, 1234567 CATNIP"

MyOrder = "Sheet Exists! Do you wish to show records?"

Ans = MsgBox(MyOrder, vbQuestion + vbYesNo, "???")

If Ans = vbNo Then
    MsgBox "Not show records" 'end
    Exit Sub
Else
    ray = Split(cat, ",")
    For i = LBound(ray) To UBound(ray)
        MsgBox ray(i)
        Sheets(Trim(ray(i))).Visible = True
    Next i
End If

End Sub

 

[mc136355]

Hi thanks for replies

Took my code as you suggested and put it into sub as per nosparks code. (will learn more about functions and sub routines as I go)

The code is ideal

Thanks to both for your help

Know more now than I did before
MC

 

[mc136355]

Hi All
Just 1 further problem with my code. When I get to unhiding the sheets it seams to unhide all but then attempts to unhide an extra one which is blank (blank input box). is there anyway I can use -1 in the code so as not to bring this last box back.

Thanks in advance

Private Sub CommandButton1_Click()
Worksheets("INVENTORY").Activate
If WorksheetFunction.CountIf(Range("C5:C15"), Range("Q17")) = 0 Then
MsgBox "Item not in list, choose from list"
Else
MsgBox "Valid Choice"
myArr = Worksheets("INVENTORY").Range("Q17")


Dim MyOrder As String
Dim Ans As Integer
Dim ray As Variant
Dim i As Integer
Dim Element As Variant
Dim msg As String

With ThisWorkbook.Worksheets("test3")
fileArray = .Range("A1:A" & .cells(.Rows.Count, "A").End(xlDown).Row).Value2

End With

For Each Element In fileArray
If Element Like myArr & "*" Then
msg = msg & Element & ", "

End If
Next

MyOrder = "Sheet Exists! Do you wish to show records?"
Ans = MsgBox(MyOrder, vbQuestion + vbYesNo, "???")
If Ans = vbNo Then
MsgBox "Not show records" 'end
Exit Sub
Else

ray = Split(msg, ",")
For i = LBound(ray) To UBound(ray)
MsgBox ray(i)
Sheets(Trim(ray(i))).Visible = True
Next i
End If
End If
End Sub