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

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

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

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

Private Sub Workbook_Open()
 On Error Resume Next
    Dim sht As Worksheet
    Application.ScreenUpdating = False
    For Each sht In ActiveWorkbook.Worksheets
    sht.ShowAllData
    Next sht
    End
    Sheets(1).Select
    Application.ScreenUpdating = True
End Sub

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

  Dim iResponce As Integer
    iResponce = MsgBox("Are you sure you want to delete?", vbYesNo, "Confirm Delete")
    If iResponce = vbYes Then
        MsgBox "y"
    Else
        MsgBox "n"
    End If

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

Hi,

This might help with 2 out of 4

Should select the first sheet and unfilter ranges

Sub 2_out_of_4
    On Error Resume Next
    Dim sht As Worksheet
    For Each sht In ActiveWorkbook.Worksheets
    ActiveSheet.ShowAllData
    Next sht
    Sheets(1).Select    
End Sub

VBA Noob

Hi,

Sorry should be

Private Sub Workbook_Open()
 On Error Resume Next
    Dim sht As Worksheet
    Application.ScreenUpdating = False
    For Each sht In ActiveWorkbook.Worksheets
    ActiveSheet.ShowAllData
    Next sht
    Sheets(1).Select
    Application.ScreenUpdating = True
End Sub

See link which shows how to get to thisworkbook

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

VBA Noob