Hide sheets using vba

Hi all

I have a sheet initally hidden(Contact) and the rest visiable, what I am 
trying to do is when today() = 1-11-06 it automatically unhides the Contact 
sheet and hides all the rest.

I am trying to do it with having a button assigned to a macro - Having no 
luck...

Any help welcome.

Thanks
Paul

tried...
Sheets("Menu").Range ("A1") >= "01/11/2006"
GoTo hide
Sub hide()
Run
    Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT 
WW", "Summary") _
        ).Select
    Sheets("Health Dec").Activate
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Contact").Visible = True
    Sheets("Long stay").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub 


0
9/23/2006 10:01:18 AM
excel 39879 articles. 2 followers. Follow

3 Replies
896 Views

Similar Articles

[PageSpeed] 56

Sorry - Trying to do it WITHOUT using a macro button.

"PJ" <paul.thomas5@ntlworld.com> wrote in message 
news:OB7Rg.11484$2g5.7651@newsfe7-win.ntli.net...
> Hi all
>
> I have a sheet initally hidden(Contact) and the rest visiable, what I am 
> trying to do is when today() = 1-11-06 it automatically unhides the 
> Contact sheet and hides all the rest.
>
> I am trying to do it with having a button assigned to a macro - Having no 
> luck...
>
> Any help welcome.
>
> Thanks
> Paul
>
> tried...
> Sheets("Menu").Range ("A1") >= "01/11/2006"
> GoTo hide
> Sub hide()
> Run
>    Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT 
> WW", "Summary") _
>        ).Select
>    Sheets("Health Dec").Activate
>    ActiveWindow.SelectedSheets.Visible = False
>    Sheets("Contact").Visible = True
>    Sheets("Long stay").Select
>    ActiveWindow.SelectedSheets.Visible = False
> End Sub
> 


0
9/23/2006 10:02:56 AM
Sussed it...with

Private Sub Workbook_Open()
If Sheets("Menu").Range("A1") >= "01/11/2006" Then
    Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT 
WW", "Summary") _
        ).Select
    Sheets("Health Dec").Activate
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Contact").Visible = True
    Sheets("Long stay").Select
    ActiveWindow.SelectedSheets.Visible = False
End If
End Sub


"PJ" <paul.thomas5@ntlworld.com> wrote in message 
news:kD7Rg.11486$2g5.8192@newsfe7-win.ntli.net...
>
> Sorry - Trying to do it WITHOUT using a macro button.
>
> "PJ" <paul.thomas5@ntlworld.com> wrote in message 
> news:OB7Rg.11484$2g5.7651@newsfe7-win.ntli.net...
>> Hi all
>>
>> I have a sheet initally hidden(Contact) and the rest visiable, what I am 
>> trying to do is when today() = 1-11-06 it automatically unhides the 
>> Contact sheet and hides all the rest.
>>
>> I am trying to do it with having a button assigned to a macro - Having no 
>> luck...
>>
>> Any help welcome.
>>
>> Thanks
>> Paul
>>
>> tried...
>> Sheets("Menu").Range ("A1") >= "01/11/2006"
>> GoTo hide
>> Sub hide()
>> Run
>>    Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", 
>> "AT WW", "Summary") _
>>        ).Select
>>    Sheets("Health Dec").Activate
>>    ActiveWindow.SelectedSheets.Visible = False
>>    Sheets("Contact").Visible = True
>>    Sheets("Long stay").Select
>>    ActiveWindow.SelectedSheets.Visible = False
>> End Sub
>>
>
> 


0
9/23/2006 10:31:51 AM
Sub yqr()
For Each aaa In ActiveWorkbook.Worksheets
If aaa.Name <> "Contact" Then aaa.Visible = False
Next
End Sub

"PJ" <paul.thomas5@ntlworld.com> д���ʼ�
news:OB7Rg.11484$2g5.7651@newsfe7-win.ntli.net...
> Hi all
>
> I have a sheet initally hidden(Contact) and the rest visiable, what I am
> trying to do is when today() = 1-11-06 it automatically unhides the
Contact
> sheet and hides all the rest.
>
> I am trying to do it with having a button assigned to a macro - Having no
> luck...
>
> Any help welcome.
>
> Thanks
> Paul
>
> tried...
> Sheets("Menu").Range ("A1") >= "01/11/2006"
> GoTo hide
> Sub hide()
> Run
>     Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU",
"AT
> WW", "Summary") _
>         ).Select
>     Sheets("Health Dec").Activate
>     ActiveWindow.SelectedSheets.Visible = False
>     Sheets("Contact").Visible = True
>     Sheets("Long stay").Select
>     ActiveWindow.SelectedSheets.Visible = False
> End Sub
>
>


0
yqr1212 (4)
9/23/2006 10:41:30 AM
Reply:

Similar Artilces:

Excel prints some sheets very small
When I try to print multiple sheets of the same workbook at the same time, one of them (a different one each time) prints very small (about an inch tall) in the upper left hand corner. I am printing from a network file but I don't know if this has anything to do with it or not. Please HELP!!! ...

saving the spreed sheet by clicking on a cell?
I have made an Excel spreed sheet for my students that they need to fill out with some data. They also enter their ID number in a specific cell. I want to create a cell (let call it "Save" button) so that when the students click on it, it automatically save the file with their ID number which was entered in the specific cell in my directory. Thanks for your help in advance. Dori Hi! You can simply click on the "Save" icon on the Standard toolbar (if you display that toolbar). It looks like a floppy disk. Biff "DORI" <DORI@discussions.microsoft.com> ...

Proper use of categories in invoicing?
I am a freelance web developer. I use Money to print and track my invoicing. Being a freelancer, I often have a wide variety of clients: there is a continual influx of new clients, as well as a stable of regular clients. The regular clients often bring their own clients with them, and in this case, my role is only to develop the site. As a result, I will often send out 10 or 20 invoices to one of these regular clients over the course of a year, covering work that I may have done on 20 or 30 sites. As it is now, I can sort for invoices to the REGULAR CLIENTS, but I cannot search/sort for i...

Hyper link to another sheet
Hi there, I have a list of items in the main sheet. For each item there is a respective sheet named for it. Is it possible to create a hyperlink whenc licked leading you to that particular sheet. (I know this can be done through a command button, but I want this to be done through a hyperlink). Thanks for your help, Brian Hi Brian, > I have a list of items in the main sheet. For each item > there is a respective sheet named for it. Is it possible > to create a hyperlink whenc licked leading you to that > particular sheet. > Use the hyperlink function. Say cell A1 ...

Journal Tracking Access use
How can I keep Outlook 2002's journal from tracking everytime I use Access 97 to work on a database? I always am deleting these entries in my journal. Is there a way to stop journal from tracking this use of Access? ...

Macro Help
Hi All I have a workbook which contains >400 sheets. What I need to do is to insert a "cover" sheet and then create a macro to pick up the same four cells from each worksheet (B2, G9, B14 and B9) and copy and paste the values into the Cover Sheet going progressively down a column for each different sheet. so i need the macro to go to Sheet1 get the value from B2 and paste it on the cover sheet in A2, then get the value G9 and paste it on the cover sheet in B2 etc... then go to Sheet2 get the value from B2 and paste it on the cover sheet in A3, then get the value G9 and...

SSRS for use in letter templates
I have a .NET program that allows for storing of nvarchar text for use in letter templates. My program correctly shows text that had been copied from a Word document into a database location. If I attempt to use that same text in a textbox in an SSRS document, it appears that it loses all of the formatting such as paragraph indentation. Is there something I am not setting correctly in the document? ...

Result Sheet in a School
Hi everybody, I want to make a result sheet for the school class; i am facing problem in logical formulas like how to assess position, how to assess GPA etc. Can any one help? Best Regards Basharat Basharat - Microsoft has some instructions for an "electronic gradebook" at http://office.microsoft.com/en-us/excel/HA100690701033.aspx - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Basharat" <Basharat@discussions.microsoft.com> wrote in message news:D3D16E7A-1338-482F-845A-10C46814E61C@microsoft.com... > Hi everybod...

Forecasting using SUMPRODUCT and dates
I am having a problem with counting data that is within a certain date. for example, I have 4 columns. SERVERNAME, LOCATION, TYPE LEASEENDDATE. I am currently using SUMPRODUCT to count the number o servers I have for a particular location and type. A B C D SERVERNAME LOCATION TYPE LEASEENDDATE Server1 MN Web 6/30/2004 Server2 CA App 8/15/2004 SUMPRODUCT((B2:B200="MN")*(C2:C200="Web")) On another sheet, I would like to forecast of how many servers I hav that are not expired...

Use worksheet number instead of name in formulas
Good morning everybody, I have looked through a few groups and I haven't been able to find what I need to do, so here goes: is there a standard function/formula that allows you to reference the work sheet number rather than the worksheet name when referencing a cell in a different worksheet than you are currently in? i.e.: instead of this: "='My worksheet name'!A1" do this: "=sheet(1)!A1" If there is no standard, then I will just make the VBA, but no use doing that if it is not necessary Tim Only through VBA. Gord Dibben MS Excel MVP On Fri, 9 May 200...

Using a Public Variable in a Query
I have read many posts (and found code in a book) referencing how to used a "dummy" function to call a variable into a query. But it's not working for me. Can someone tell me what I'm doing wrong? In a Public module (starting with Option Compare Database), I have defined a variable called UserArea: Public UserArea As Long I have then written a function to return the value of UserArea Function GetActiveArea() Dim ActiveArea As Long ActiveArea = UserArea End Function Then, in my Query design view, I have the criteria line for the Are...

Reset Sheet Count -Second Request Any Help Appreciated
Hopefully just a quick question, I have some code that gets run everyday and it inserts sheets in a workbook and renames each sheet to specific value. The next day the same code gets run and it deletes the worksheets that i added the previous day and adds some new worksheets, again changing the name of the sheets to a specific value and so on and so forth each day of the week. This works fine, however when the code inserts the new worksheet I have noticed that the default name that excel gives the sheet(before i rename it) is getting higher and higher. When i insert a new sheet now the defau...

can i use autotext in publisher 2003?
i find auto text to be very helpful in word. is there a way to enable it in publisher? ...

Macro for copy sheet
I am a novice when it comes to macros...but I have to copy a bunch of spreadsheets...both copying to the same workbook and copying to a new workbook...I'd like to streamline the process and rather than copy/move, etc., I wanted to either make a macro button or a shortcut key. I can't find anything on the web to help me. I have to do this every week on different sheets...so I'd like to have a macro that works for all the named sheets. Can this be done? Thanks! Lynn Lynn Bartling wrote: > > I am a novice when it comes to macros...but I have to copy a bunch of &g...

Syncing contacts to a 2nd computer using a treo phone
I can sync my contacts list to my treo phone but when I tried to transfer the contacts to a work laptop my phone would not let me transfer them to the laptop. My calender and asks sync over but the phone won't let me check to allow the contacts. Is this something on the original outlook computer or should I be questioning Palm Treo? -- MJA Sync issues are never Outlook issues. -- Russ Valentine "MJA" <MJA@discussions.microsoft.com> wrote in message news:53277189-0AC0-4838-9CC3-4AF597ACFA82@microsoft.com... >I can sync my contacts list to my treo phone...

Can't use Sql server when using forms authentication
Hello! I do some exercise from a book which work if I run from VisualStudo(VS) but doesn't work when run from IIS. This exercise will use forms authentication Here is some background information: I use windows XP pro with a Sql Server express 2005 database. I have also Sql Server Management Studio Express I use VisualStudio 2005 Here is what I have done ******************* I have created a file system web site named Northwind that is using Development Web server. In this web site I have created the following files. A LoginForms.aspx and LoginForm.aspx.cs with one Login c...

Which port isMsxml2.DOMDocument using in server to server requests
I am using Msxml2.DOMDocument to pull xml fragments from different sibling websites. This works off the net, however during testing on my localhost, it fails if called from an external website. Could this be due to the firewall? If the external site calls for a webpage on localhost, or a js file (from IE running on localhost) however, the Msxml2.DOMDocument doesn't? I would appreciate any thoughts or info related to this, such as the port number used by Msxml2.DOMDocument <xml.setProperty("ServerHTTPRequest", true);> ...

how to deliver pop3 emails to public folder using pop3 connector
Can someone provide me with a link on how to get pop3 connectors to deliver to a public folder using SBS2003 (not R2)? Currently the pop3 connector is delivering to a users mailbox but I want it to deliver to an exchange public folder. The pop3 connector is getting mail from me@here.com from our ISP. I have setup a public folder in system manager with an exchange address of me@here.com. My default recipient policy has an SMTP entry of "@here.com". I know there has got to be an easy to follow document out there somewhere but I cant seem to run across it in my searching...

File in use Dialog box
When I open a file that is opened on the network by someone else it is not giving me the correct name of who has the file open. Where does the name it is giving me come from? I believe that it is the value in Tools>Options>General - User name. To make any use of this you have to ensure people properly maintain it. -- HTH RP (remove nothere from the email address if mailing direct) "Chip" <Chip@discussions.microsoft.com> wrote in message news:8B0DDB45-95F7-4BF4-9D0B-838603A7ECFA@microsoft.com... > When I open a file that is opened on the network by someone els...

Copying and hiding issues...
Hi, I need to create a macro which will copy A5:A500 on all grouped sheet and paste them onto column A in a hidden worksheet "Shipped". The har part for me is one, I need to omit the blank spaces, and two, thi macro will be ran numerous times so I need it to continue from the las cell containing data the next time it is ran. Awaiting some muc needed help. Best Regards, Mjac -- Message posted from http://www.ExcelForum.com Something like this??? Option Explicit Sub testme01() Dim ShippedWks As Worksheet Dim wks As Worksheet Dim DestCell As Range Set Shipp...

Make a field null using IF function
Is there a function that will make a field Null, or change the format of a field from time to general? I'm calculating time differences for which some fields do not have a time. I also need an average of these results, but the fields without a time are returning 0:00 (because the field format is HH:MM) which are causing the averages to miscalculate. I'd like my formulat to work something like this: =IF(OR(ISBLANK(D7),ISBLANK(F7)),[Make Field Blank] ,(F7-D7)) You cannot change the format of one cell from a function in another cell. If you wish to average a column of numbers excludi...

Printing Excel sheets
Hi, On my Excel sheet I have only 10 rows in use, but for some reason the totaal sheet on my screen is about 1000 rows, from which 990 are empty. But what's worse, when I print the sheet I get 1000 rows paper output, in stead of 10 rows. How can I get rid of all these empty rows. Not only for printing but also on my screen. Some help would be nice ! Thanks ! This problem is caused when a spread sheet which had say, 1000 columns by 1000 rows is reduced by deleting columns and rows. For some reason deleting columns and rows doesn't remove the internal flag of the position of t...

Formula Based on Multiple Sheets
I have a workbook with a summary sheet for 25 other sheets. On these 25 sheets there is a cell that is the same cell on every sheet, but may contain different values based on formulas within the specific sheet. In other words, cell D17 on sheet '1' will be different from D17 on any other sheet. On the summary sheet row 1 would refer to sheet 1. I need the cells in row 1 of the summary sheet to look for specific values in a specific cell on sheet 1 and simply copy the value. When I simply enter ='1'D17 it works, But when I try to copy that formula down the col...

We are using eSafe
We have an eSafe appliance by Aladin filtering spam and viruses in and out. It is doing a good job for my 4000 users. Convince the boss not to quarenteen messages. Their support was lacking when we first got the box but after a long conversation with a VP I think they have made great strides fixing that problem. Fred ...

Linking sheets which must update the date first Excel 2007
I have 50 clients, each with an Accounts Recievable worksheet. Past due is determined by using the =now and =today functions. This information is then linked to Master sheet which displays all 50 clients at one time. The problem is: When I open the Master sheet, the dates on the 50 'feeder' sheets do not update the date. Only if those sheets are opened, does the date update. Is there a command to open and close those 50 sheets or a recalculate command or perhaps, a macro is needed to open then close the 50 sheets. Any suggestions? I posted the same question about...