I have 4 questions, van you help?

Originally Posted by jimmisavage

I have a few questions here, some easy, some notso easy i think.

1. When i open my excel workbook i always want it to open on the front page, but it currently opens on what every page it was saved on. Is it possible to always open on the front page?

With an entry in the ThisWorkBook module, what is the name of the front page?

2. Following on from question 1. When i open my workbook i want all filters to refresh. Currently it saves whatever filters were used previously.

filters on all sheets?

3. I am thinking it may be useful for me to have a button which will clear whatever is in selected cells on a sheet. You know, rather than deleting cell by cell.

are you still referring to filters? - or does the Del key not already do this?

4. Is it possible to lock (protect) certain cells on a sheet? I dont want to protect the whole sheet, just headings and things like that.

Select all cells and Format Cells untick the lock, then select the required cells and Lock those, then protect the sheet.

If i can get these 4 things going my workbook will be complete (or just about!!). Any help or advice would be appreciated very much.

Oh and to help me in the future, does anyone know where i can learn all about excel and macro's for free?

You can start at http://www.danielklann.com/tutorials/tutorial_index.htm

hth
---

Hi,

This might help with 2 out of 4

Should select the first sheet and unfilter ranges

Please Login or Register  to view this content.

VBA Noob

Hi,

Sorry should be

Please Login or Register  to view this content.

See link which shows how to get to thisworkbook

http://www.mcgimpsey.com/excel/modules.html

VBA Noob

Originally Posted by jimmisavage

1. The front page is called 'front page'. original hay?

2. Yeh filter on all sheets please. there are about 130 sheets.

3. Sorry, This is nothing to do with filters. I have a sheet with info that is only needed for a month. I know i could just use the del button, but i want to make it as user friendly as possible. i'm thinking a 'refresh' button would be a nice touch. When i say 'refresh' button, i mean a button that would delete all the information on that page which is no longer needed. All at the touch of a button.

4. Thanks for this answer. Easier than i thought!

HI,
Using VBA Noob's code, in ThisWorkBook put

Please Login or Register  to view this content.

and for the button, View, Control Toolbox, in Design Mode make a button that links to code that you can generate with Macro Record whilst you delete the area required, however, a Shortcut key from a Macro would be a better idea if it is to apply to multiple sheets.

hth
---

Sorry,

Correct code should be

Please Login or Register  to view this content.

VBA Noob

Originally Posted by jimmisavage

brilliant thanks sooooo much.

ok, i'm gunna sound a bit stupid now so please forgive me.

I only need the button for 1 page, but i really dont know how to put a button in, use design mode or very much about macro. To be honest i never even used excel untill about 2 months ago.

i dont mind learning myself if you want to piont me in the right direction. or, if you dont mind, can you tell me how to use design mode? and how i would put my button in, and make it clear these cells of mine?

ok - select

View, Toolbars and tick the Control Toolbox

first icon there toggles Design Mode, select design mode

select a button and draw a shape on the sheet

rightmouse the shape and 'Properties'

amend Caption

amend Backcolor, dropdown, Palette and select the top (light) Blue

x that window to close

rightmouse the button and select View Code

in there put

Please Login or Register  to view this content.

exit design mode, and then test the button

Let us know how you go.

---

added, also make sure you use VBA Noob's amended code, but change Sheets(1).Select to Sheets("Front Page").Select if the front page is not the tab on the extreme left.

Hi,

This links should help

http://www.meadinkent.co.uk/xlnavig.htm

http://www.meadinkent.co.uk/xlnextwindow.htm

VBA Noob

Originally Posted by jimmisavage

Hi Bryan Hessey,

sorry, but the code for the button does not appear to work. The cells i want to clear are D6:w11 and D16:W22. Any idea what is wrong?

Thanks for the links VBA NOOB, i'll have a look at them when i get home. stupid work wont let me go onto those sites!

Hi,

try

Range("D6:W11").Value = ""
Range("D16:W22").Value = ""

and if that is a problem just post your code here.

hth
---

Originally Posted by jimmisavage

Agh, sorry, this code will not work. when i open my workbook i want it to open on the first page, instead of whatever page it is saved on. here is the code i have....

Private Sub workbook_open()
On Error Resume Next
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
ActiveSheet.ShowAllData
Next sht
Sheets("Year").Activate
End Sub

"Year" is the fron page of my workbook. Any ideas?

Hi,

the code you show is not the code advised by VBA Noob at post #6

The code shown needs to be amended to reflect post #6 and post #10 re which sheet is first displayed.

---

also, did you put that code into the ThisWorkBook module?
---

Originally Posted by jimmisavage

Sorry Bryan,

I'm not getting this. I am putting the code in the right place, but its not working. Exactly what should the cade be? Really sorry to be a pain!

At post #4 VBA Noob supplied some code, then updated that at post #6, your code should, I presume, look like that supplied at post #6

I hsve not tested the code, but presume that VBA supplied working code, the only amendment I suggested was that if your required display sheet were not the extreme left tab (sheets number 1) that the sheet name be used, that name is apparently either 'front page' or "Year" or similar name.


Try a
Msgbox "here"

in the code to see how far the code progresses, move this down to test further, and check the worksheet name in the
Sheets("Front Page").Select
line.

---