Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: A challenge for you

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

    Default


    I dont think this is possible, but if it is, could someone tell me how to do the following:

    I need it so that when someone tries to activate the sheet tabs in the Tools>Options menu, a msgbox comes up saying "No, don't you dare you 'orrible slag!" and the sheet tabs don't come on

    Any ideas guys?

    Janie
    xxxxxx

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

    Default

    Further to this, is there any way of just disabling the sheet tabs option? That would do. The dirty 'orrible slag part was my friend Tom's suggestion, and I've gone off it already

    Janie
    xx

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's one option to get you started. This does not stop someone from selecting the Sheet tabs Window option via Tools > Options > View (which I think is part of your second post), but this code will make those tabs disappear with your 'orrible slag message.

    This code is triggered by a Workbook level event, when any cell is selected in any sheet after the tabs are displayed. You can augment this approach by copying the code in other workbook events, such as SheetActivate, SheetCalculate, and BeforePrint, etc.

    The advantage to this approach, which you touched on, is to give a message to the 'orrible slag users that their attempt to display the sheet tabs is not successful because of your intentional workbook design, instead of them thinking their computer doesn't work properly.

    Anyway, here's a start at getting what you want.

    Right click on your workbook icon (to the left of the File menu option near the upper left corner of the monitor screen), left click on View Code, and paste this in:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveWindow.DisplayWorkbookTabs = True Then
    MsgBox "Sheet tabs may not be accessed.", 16, _
    "Back off you 'orrible slag !!"
    ActiveWindow.DisplayWorkbookTabs = False
    End If
    End Sub

    HTH

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

    Default

    Thanks Tom, you little sweetheart

    xx

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Paste this into the ThisWoorkbook Module in the VBE it will disable control 13 which is the Options Control on the Tools Commandbar.
    Make sure you turn it back on with the deactivate command as well so it is available to other open instances of EXCEL


    Private Sub Workbook_Activate()
    Application.CommandBars("Tools").Controls(13).Enabled = False
    End Sub

    Private Sub Workbook_Deactivate()
    Application.CommandBars("Tools").Controls(13).Enabled = True
    End Sub

    Yours in EXCELent Frustration
    KniteMare

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 06:11, KniteMare wrote:
    Paste this into the ThisWoorkbook Module in the VBE it will disable control 13 which is the Options Control on the Tools Commandbar.
    Make sure you turn it back on with the deactivate command as well so it is available to other open instances of EXCEL


    Private Sub Workbook_Activate()
    Application.CommandBars("Tools").Controls(13).Enabled = False
    End Sub

    Private Sub Workbook_Deactivate()
    Application.CommandBars("Tools").Controls(13).Enabled = True
    End Sub

    Yours in EXCELent Frustration
    KniteMare

    The Options item is not necessarily control number 13 of the Tools menu (it is not on my computer - it happens to be 15).

    Suggest the following instead :-

    Application.CommandBars("Tools").Controls("Options...").Enabled = False


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To: Afcb
    Re: Thanks!
    You Wrote:

    Application.CommandBars("Tools").Controls
    ("Options...").Enabled = False

    COOL! That is much better. I have a bunch of these "DisAble" commands I am going to reWrite to Caption instead of index. Your response was an AH-HA moment. I could not get these to work with Captions in the past, I was leaving out the trailing periods (...) Thanks for tip.
    Yours in EXCELent Frustration
    KniteMare

    [ This Message was edited by: KniteMare on 2002-04-19 06:49 ]

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
  •