Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: How do i unhide sheet tabs, scroll bars, row and column head

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi im in really really big trouble with a macro i always thought would be easy. I need to be able to apply the following to always happen to all sheets when i open a workbook in Excel:

    -Have all Sheet tabs hidden
    -No Row and Column Headers visible
    -Have all sheet protected so that only unlocked cells can be selected.

    When the system closes and opens each times it needs to be like this! Please Please Please help me
    Thanks very very much

    Daniel

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    does ne one know anything that can help me with this?

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    -Have all Sheet tabs hidden

    I don't think this is possible. It would make the workbook ridiculous.

    -No Row and Column Headers visible

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWindow.DisplayHeadings = True
    End Sub

    Private Sub Workbook_Open()
    ActiveWindow.DisplayHeadings = False
    End Sub


    -Have all sheet protected so that only unlocked cells can be selected.

    No practical way to do this and I can't see the point. Why shouldn't the user be able to select locked cells if they can't change the contents?
    You can protect the sheet manually, but if you need code to do this use the "search" on this website to find it.

    Why do you need to do this? I used to asked for stupid crap like this from my boss when he was too cheap to purchase a GUI package. I get the feeling you're up against something similar.

    HTH

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What i basically want is an auto_open macro that brings up the Main interface worksheet of the spreadsheet, and has the sheet tabs set to not be visible (you know when you can go tools options sheet tabs and click it to not be visible). Also i want no toolbars visible apart from the basic worksheet menu bar (file, edit, view, etc).

    A second macro will if what the user types into a specified cell is correct (an If... Then statement) unlock the system by running a macro that makes the standard, formatting, forms, and drawing toolbars visible on the screen and puts up the sheet tabs again.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh yeah is it possible to have Excel save a workbook then save it as a pre-named workbook e.g. "year10archive", without having the Are you sure you want to overwrite the existing workbook message box popping up?

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, thanks for pointing out the nametab in the options. I missed that before.

    You're looking for something like this then:


    Public Sub main()

    Dim cmdbar As CommandBar

    For Each cmdbar In Application.CommandBars
    cmdbar.Enabled = False
    Next

    With ActiveWindow
    .DisplayHeadings = False
    .DisplayWorkbookTabs = False
    End With

    End Sub


    Write another sub with the stuff set to True should reset the workbook.

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 11:15, Daniel Cremin wrote:
    Oh yeah is it possible to have Excel save a workbook then save it as a pre-named workbook e.g. "year10archive", without having the Are you sure you want to overwrite the existing workbook message box popping up?
    Use:

     Application.DisplayAlerts = False


    Are you sure you want to be able to see Excel at all?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark

    Out of intrest IE allow choisk opening of HTML so yes Excel could be totally hidden!


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can set the worksheet so that only unlocked cells are selectable by doing this:-

    Sheets("Sheet name").EnableSelection = xlUnlockedCells

    HTH,
    D

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cheers mate for the last bit of code there! It means instead of having to go to every worksheet and then do the protection i can just set each worksheet to be like that!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •