A challenge for you

[buntykins]

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

 

[buntykins]

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

 

[Tom Urtis]

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

 

[buntykins]

Thanks Tom, you little sweetheart

xx

 

[KniteMare]

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

 

[Afcb]

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

 

[KniteMare]

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