VB Code for Excel

[jason.ecenbarger]

Hello,

I have 2 questions

1) I need help writing code wihin excel. I want to write code that
everytime I open the spreadsheet it takes me to the current date. For
example I have a workbook that I create every month that has multiple
sheets in it. Each sheet has the days of the month numbered as
follows 1, 2,3,4,5 etc. I want the sheet to open to the current day
when it opens. Example. if today is the 26th I want the sheet named
26 to be opened first. Excel by default will open to the last sheet
that was changed when the save was made. Please help.

2) Also, I would like to know how to hide a sheet within excel using
macros. The workbook has multiple sheets in it and I need a specific
sheet to always be hidden unless a specific user opens the workbook.
For example: if I open the workbook I want to be able to see the
sheet, but if another user opens the workbook the sheet will be hidden
but all other sheets will be visible. I'm not sure if it matters or
not but I need to give access to 4 users to be able to see this sheet
but all other users that open the workbook wont be able to see it.

 

[Gary''s Student]

The first part is easy (so I'll offer an answer). Put this event macro in
the workbook code area:

Private Sub Workbook_Open()
Dim s As String
s = Day(Date)
Sheets(s).Activate
End Sub

So if today is the 26th, so will open to sheet named 26. Because it is
workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (workbook code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Sheeloo]

Ans to Q2:

You can put the following inside an IF to Hide/Unhide the sheet based on
User name;
'To Hide
Worksheets("Sheet2").Visible = xlSheetVeryHidden
'To Unhide
Worksheets("Sheet2").Visible = xlSheetVisible

See http://support.microsoft.com/default.aspx/kb/161394 to get the code for
getting the user name

You will have to password protect your VBA project so that other users can
not change the code.