Archive of Mr Excel Message Board


Back to Errors in Excel archive index
Back to archive home



Error 1004

Posted by Stan on September 27, 2001 5:33 AM
In a Private Sub I have set newName = "MenuSheet", where newName has been declared as Global in a module. Then I call a module named chngSheet where I am attempting to set Sheets(newName).Visible = True. But I get the following 1004 Error:

Method 'Sheets' of object '_Global' failed.

Any ideas as to what the problem is?

Cheers

Stan


Re: Error 1004

Posted by Mark O'Brien on September 27, 2001 6:29 AM

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.



Re: Error 1004

Posted by Stan on September 27, 2001 6:47 AM

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


Re: Error 1004

Posted by Barrie Davidson on September 27, 2001 7:23 AM

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


Re: Error 1004

Posted by Stan on September 27, 2001 7:30 AM

Barrie

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


Re: Error 1004

Posted by Juan Pablo on September 27, 2001 7:33 AM
Last line reads:

Sheets(counter).variable = False

are you trying to say

Sheets(counter).VISIBLE = False

??

Juan Pablo


Re: Error 1004

Posted by Stan on September 27, 2001 7:37 AM
Sheets(counter).variable = False are you trying to say Sheets(counter).VISIBLE = False ??
Juan - you're right but it has nothing to do with my problem below

Cheers

Stan


Re: Error 1004

Posted by Barrie Davidson on September 27, 2001 7:40 AM

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

BarrieBarrie Davidson


Re: Error 1004

Posted by Stan on September 27, 2001 7:50 AM

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


Back to Mark's question

Posted by Barrie Davidson on September 27, 2001 7:55 AM

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

Barrie
Barrie Davidson


Re: Back to Mark's question

Posted by Stan on September 27, 2001 8:10 AM


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


Re: Back to Mark's question

Posted by Barrie Davidson on September 27, 2001 8:23 AM

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

Regards,
BarrieBarrie Davidson


Re: Emailing

Posted by Stan on September 27, 2001 8:31 AM


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


A thought

Posted by Barrie Davidson on September 27, 2001 8:39 AM

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


Re: A thought

Posted by Juan Pablo on September 27, 2001 8:45 AM
I think i saw the problem

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


Re: Yes, but....

Posted by Stan on September 27, 2001 9:09 AM
I think i saw the problem First, you declar unhideName as a Public Variable.

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


Re: Yes, but....

Posted by Mark O'Brien on September 27, 2001 10:04 AM
OK,

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!!!!


Re: Yes, but....

Posted by Barrie Davidson on September 27, 2001 10:16 AM
OK, 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()

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


Re: Thanks Mark - Problem Solved!

Posted by Stan on September 27, 2001 10:31 AM
OK, 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()

Mark

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

All the Best

Stan


Re: Thanks Barrie!

Posted by Stan on September 27, 2001 10:36 AM
OK, : 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()


Barrie

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

All the Best

Stan




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.