Archive of Mr Excel Message Board

Stan,

I am assuming that "MenuSheet" is the actual name of a worksheet in your work book.
If you have declared newName like this:

dim newName as Worksheet

then your code will have to be something like this:

Set newName = Sheets("MenuSheet")
newName.Visible

If this doens't work/help then repost with some of your code.

I've made the change but I get an Object required error at the line containing "newName.Visible = True". Here is some of my code:

****************
Private Sub Workbook_Open()

Dim newName As Worksheet

Set newName = Sheets("MenuSheet")
Call unhideSheets

*****************
Public Sub unhideSheets()

Dim count As Integer
Dim counter As Integer

newName.Visible = True
count = ActiveWorkbook.Sheets.count

For counter = 1 To count
If Sheets(counter).Name <> newName Then
Sheets(counter).variable = False
End If
Next counter

End Sub

Stan, you need to declare the variable newName as a public variable. Put:
Public newName As Worksheet
before your worksheet code. That is, before the line:
Private Sub Workbook_Open()

Regards,
Barrie
Barrie Davidson

Barrie

I made the change but at the line - "unhideName.Visible" = False" - I get the error "Object variable or With block variable not set".

Sheets(counter).variable = False

are you trying to say

Sheets(counter).VISIBLE = False

??

Juan Pablo

Cheers

Stan

Stan, I don't see a line "unhideName.Visible = False" in your posted code. Am I missing something?

BarrieBarrie Davidson

Barrie

I meant to say "newName.Visible". "unhideName" slipped into this because I am using it in my actual code as a result of your much appreciated help yesterday (showing me how to hide sheets).

Stan

Stan, do you have a sheet named "MenuSheet" (as per Mark's comments above)?

Barrie
Barrie Davidson

Yes I do Barrie. Would it help if I emailed you the file (I am only just starting so it's not that big - yet)?

Cheers

Stan

Okay, send it to me. I won't be able to have a look until tonight, however.

Regards,
BarrieBarrie Davidson

Barrie

Thanks. In the meantime, here is how the some of code that's causing the problem looks so far:

***********************
Public unhideName As Worksheet
**********************
Private Sub Workbook_Open()

Set unhideName = Sheets("MenuSheet")
Call unhideSheets
*********************
ublic Sub unhideSheets()

Dim count As Integer
Dim counter As Integer
Dim unhideName As Worksheet

unhideName.Visible = True
count = ActiveWorkbook.Sheets.count

For counter = 1 To count
If Sheets(counter).Name <> unhideName Then
Sheets(counter).Visible = False
End If
Next counter

End Sub

Cheers

Stan

Stan, how about changing your syntax from:
unhideName.Visible = True
to:
Sheets("MenuSheet").Visible = True

It would do what you want, no? And, I can't see the need to use a variable when you are using a static name ("menuSheet").

Barrie
Barrie Davidson

First, you declar unhideName as a Public Variable. : ***********************

Then you set it to the Sheets("MenuSheet") (By the way, can't you do this in the unhideSheets module ?

And then you declare unhideName AGAIN... just delete this Dim, and it should work... : Public Sub unhideSheets() : Dim count As Integer
-->

Juan Pablo

I did as you suggested Juan but I still get an error where the program is looking for an Object for "unhideName.Visible = True".

Barrie has made a logical suggestion but perhaps I should explain what I am trying to do. In my program, depending upon what Command button is pressed, Option button is selected, etc. I want to make a specific WorkSheet visible. Now what I could do is write something like:

Dim count As Integer
'Application.ScreenUpdating = False
'Count = Sheets.Count
'For i = 1 To Count
'If Sheets(i).Name = "MenuSheet" Then
'i = i + 1
'End If
'Sheets(i).Visible = False
' Next i

But I would have to rewrite this code everytime I want to unhide a specific Sheet, depending on what action I take. So what I am trying to do is to give a variable name to a Worksheet that I want to unhide (say, when I click on cmdMenuSheet) and then Call a Module (in this case, Sub unhideSheets). That way I am not rewriting the same code all the time and only changing the reference to the Sheet Name.

Thanks for your input Barrie and Juan. Any further thoughts?

Stan

Here is what I think may work for you.

I am assuming that you only want 1 sheet visible at any time. You want something that will make a specific sheet visible depending upon what option is chosen (I'm guessing these options define exactly what sheet needs to be made visible.

Based upon the code you posted, you can make a generic subroutine that will go through all of the sheets in the workbook and find the specific sheet that you want to be made visible. In the process it will make all other sheets invisible.

Here is the code, it is two routines.

Public Sub SheetGuide()
' Declare Variable
Dim sht As String
' Initialise Variable
' You can put a Select Case statement here for all of your options.
sht = "Sheet3"
' Call Routine that makes sheet visible
SheetVisible sht

End Sub

Private Sub SheetVisible(ByVal sht As String)

Dim shtVisible As String

Application.ScreenUpdating = False
For i = 1 To Sheets.count

' Excel always needs to have at least 1 sheet visible
If Sheets(i).Name = Sheets.Item(1).Name Then
If Sheets(i).Name <> sht Then
Sheets.Item(1).Visible = True
i = i + 1
End If
End If

If Sheets(i).Name = sht Then
Sheets(i).Visible = True
Else
Sheets(i).Visible = False
End If
Next i

If Sheets.Item(1).Name <> sht Then
Sheets.Item(1).Visible = False
End If

End Sub

How it works:

SheetGuide - This will call the SheetVisible routine telling it which sheet we want to make visible.

SheetVisible - This goes through every sheet in the workbook making our target sheet visible and all others invisible. the only trick here is that at least one sheet has always got to be visible in an Excel workbook or else you'll get 1004 errors.

I suggest using a Select Case to determine which sheet should be visible based upon whatever options have been selected by the user.

Phew!!!!

Stan, I have tested this and it will work as well.

Put this in the workbook code:

Private Sub Workbook_Open()
Dim unhideName As String
unhideName = Sheets("MenuSheet").Name
Call unhideSheets(unhideName)
End Sub

and this in your module code:
Public Sub unhideSheets(unhideName As String)
Dim count As Integer
Dim counter As Integer
Sheets(unhideName).Visible = True
count = ActiveWorkbook.Sheets.count
For counter = 1 To count
If Sheets(counter).Name <> unhideName Then
Sheets(counter).Visible = False
End If
Next counter
End Sub

Regards,
Barrie

PS - this was a good challenge.Barrie Davidson

Mark

Works like a charm. I really like how you got around the problem of always have one sheet visible.

All the Best

Stan

Barrie

I agree. Very much appreciate all of your input into this posting.

All the Best

Stan