Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Code for Excel
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Code for Excel
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 -- Gary''s Student - gsnu2007L " wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Code for Excel
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. " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |