miconian
09-18-2004, 03:45 PM
Hello.
Someone on mrexcel.com, I think Nimrod, gave me the following code:
Select Case Sh.Name
Case "apple sheet": ColName = "apple"
Case "orange sheet": ColName = "orange"
Case "grape sheet": ColName = "grape"
Case "pear sheet": ColName = "pear"
Case Else
ColName = Sh.Name
End Select
With Columns("A:A")
Set c = .Find(ColName, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With
The idea was this: in column A, the user will enter words corresponding to several worksheets (same workbook). When any given worksheet is indicated by an item in column A, then that worksheet should appear. If there is no corresponding text in column A, then that worksheet should not appear. For example, if the word "apple" is entered into any cell in column A, then the apple worksheet will become visible. If that same cell is overwritten, and there are no other incidents of "apple" in column A, then the apple sheet will disappear.
The code works great. The problem is this. There is one renegade worksheet that corresponds to any of nine different items that might be entered into column A. For clarity, let's call it the "vegetable" worksheet, and say that it ought to become visible if, and only if, any of the following appear in column A: carrots, celery, onion, sprout, lettuce, broccoli, asparagus, cale, or turnip.
Now, maybe I was taking the wrong approach, but I had an idea that I could simplify this problem by creating a "vegetable verification" column, column R. If the name of a vegetable appears in column A, then the word "vegetable" appears in column R. Otherwise, it doesn't. Obviously, then, if the vegetable verification column contains even one instance of "vegetable," then the vegetable worksheet should be visible (and otherwise, it shouldn't).
However, I cannot figure out how to control the visibility of the vegetable worksheet as appropriate, and at the same time still use the code above for the other worksheets. That code will cycle through the sheets, find the vegetable sheet, see that there is no corresponding entry in column A, and make the vegetable sheet invisible, whether it should be or not.
I tried doubling the second part of the code to make it check both column A and the vegetable verification column:
With Columns("A:A")
Set c = .Find(ColName, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With
With Columns("R:R")
Set c = .Find(ColName, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With
...but that doesn't work; the second instance of "Sh.Visible=xlSheetHidden" will hide all the fruit worksheets.
So, any suggestions would be greatly appreciated, and I would be glad to provide any additional info that may be necessary or helpful.
Thanks.
Someone on mrexcel.com, I think Nimrod, gave me the following code:
Select Case Sh.Name
Case "apple sheet": ColName = "apple"
Case "orange sheet": ColName = "orange"
Case "grape sheet": ColName = "grape"
Case "pear sheet": ColName = "pear"
Case Else
ColName = Sh.Name
End Select
With Columns("A:A")
Set c = .Find(ColName, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With
The idea was this: in column A, the user will enter words corresponding to several worksheets (same workbook). When any given worksheet is indicated by an item in column A, then that worksheet should appear. If there is no corresponding text in column A, then that worksheet should not appear. For example, if the word "apple" is entered into any cell in column A, then the apple worksheet will become visible. If that same cell is overwritten, and there are no other incidents of "apple" in column A, then the apple sheet will disappear.
The code works great. The problem is this. There is one renegade worksheet that corresponds to any of nine different items that might be entered into column A. For clarity, let's call it the "vegetable" worksheet, and say that it ought to become visible if, and only if, any of the following appear in column A: carrots, celery, onion, sprout, lettuce, broccoli, asparagus, cale, or turnip.
Now, maybe I was taking the wrong approach, but I had an idea that I could simplify this problem by creating a "vegetable verification" column, column R. If the name of a vegetable appears in column A, then the word "vegetable" appears in column R. Otherwise, it doesn't. Obviously, then, if the vegetable verification column contains even one instance of "vegetable," then the vegetable worksheet should be visible (and otherwise, it shouldn't).
However, I cannot figure out how to control the visibility of the vegetable worksheet as appropriate, and at the same time still use the code above for the other worksheets. That code will cycle through the sheets, find the vegetable sheet, see that there is no corresponding entry in column A, and make the vegetable sheet invisible, whether it should be or not.
I tried doubling the second part of the code to make it check both column A and the vegetable verification column:
With Columns("A:A")
Set c = .Find(ColName, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With
With Columns("R:R")
Set c = .Find(ColName, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With
...but that doesn't work; the second instance of "Sh.Visible=xlSheetHidden" will hide all the fruit worksheets.
So, any suggestions would be greatly appreciated, and I would be glad to provide any additional info that may be necessary or helpful.
Thanks.