Macro Works, Function or Sub with Parameters doesnt. Suggestions Please.

[Andy Kveps]

I am trying to build a function or Sub that will hide or unhide a
specific
sheet based on the information I pass in, as follows:

Sub HideSheet(SheetName as string)
Worksheet(SheetName).Visible = False
end sub

This executes without error, but does not hide the sheet.
A macro without the parameter passing works fine:

Sub HideSheetOne()
Worksheet("Sheet1").Visible = False
end sub


It is not really practical to write a hide routine for every sheet I
have as a single selection by the user will hide several sheets in a
many Sheet Workbook and a different selection may hide a different
subset of the sheets.

I was hoping to get a function that would be used in a cell like this:

= HideSheetList(C11, "Sheet1, Sheet3, Sheet45, Sheet46")

where C11 would be a user filled out cell (such as true or false, 1 or
0 ...)

A true (or 1) in C11 would hide the list of sheets given.

Doesnt seem like asking for to much...

Does it?

Any suggestions?

Thanks,

Andy Kveps

 

[Robin Clay]

Sub HideSheet(SheetName as string)

Worksheet(SheetName).Visible = False
end sub

This executes without error, but does not hide the sheet.
A macro without the parameter passing works fine:

Sub HideSheetOne()
Worksheet("Sheet1").Visible = False
end sub

Just a thought -
I don't think you can hide the ActiveSheet, so if Sheet1
is active when the routine is called, nothing will happen.

BIMBW


RClay AT haswell DOT com

 

[BrianB]

Here is something to get you started. You will note that the line o
code in your Sub HideSheet() is incorrect :-

Code
-------------------

'-----------------------------------------------
Sub test()
Dim SheetList As Variant
Dim MySheet As String
'----------------------------
MySheet = ActiveSheet.Range("A1").Value
SheetList = Array(MySheet, "Sheet2", "Sheet3")
For s = 0 To 2
HideSheet SheetList(s)
Next
End Sub
'------------------------------------------------
Sub HideSheet(SheetName)
Worksheets(SheetName).Visible = False
End Sub
'------------------------------------------------

 

[Andy Kveps]

It doesnt seem to matter what sheet I try to hide. None of them will
hide (or show) using the function.

ak

 

[Andy Kveps]

Brian,

Thanks for pointing out the error, I was writing out the routine from
memory

It should be:

Sub HideSheet(SheetName as string)
Worksheets(SheetName).Visible = False
End Sub

Your example shows a possible way of hiding a sheet, but still does
not allow flexibility in getting the data into the subroutine. Here
you are still tied to cell "A1" for some type of input.

ak