| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Editing tool bars
I posted the other day and got some direction. Thank you Peter Ellis. Now I
need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Opps, at "Application.CommandBars("Worksheet Menu Bar").Enabled = True
This should be "False" "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Mike
Take the code out of the standard module and place it in the Thisworkbook module. To disable the right-click menu....... Application.CommandBars("Cell").Enabled = False Gord Dibben Excel MVP On Wed, 7 Dec 2005 19:15:03 -0800, "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Gord, Thanks a ton...it works!!! (but you knew it would)
One more question, and I know there may not be an answer. The formula bar can be removed via tools/options etc. If the user has this selected for their perferences can I un-select it some how when running my application and the put it back when my application is closed. "Gord Dibben" wrote: Mike Take the code out of the standard module and place it in the Thisworkbook module. To disable the right-click menu....... Application.CommandBars("Cell").Enabled = False Gord Dibben Excel MVP On Wed, 7 Dec 2005 19:15:03 -0800, "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Mike
In the Sub workbook_open add this line Application.DisplayFormulaBar = False Change to = True in the Sub workbook_beforeclose Gord On Thu, 8 Dec 2005 14:15:03 -0800, "Mike Rogers" wrote: Gord, Thanks a ton...it works!!! (but you knew it would) One more question, and I know there may not be an answer. The formula bar can be removed via tools/options etc. If the user has this selected for their perferences can I un-select it some how when running my application and the put it back when my application is closed. "Gord Dibben" wrote: Mike Take the code out of the standard module and place it in the Thisworkbook module. To disable the right-click menu....... Application.CommandBars("Cell").Enabled = False Gord Dibben Excel MVP On Wed, 7 Dec 2005 19:15:03 -0800, "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Gord that works perfect!!!!
Now I am trying to follow the syntax. Would I Add the line "Application.DisplayStatusbar = False" to remove the status bar on this form? Where do you look to find the proper syntax for writing this stuff? I have looked in Help and think sometimes I am to inexperienced to understand it. You know, you need some experience to be able to ask an intelligent question. Hey, thanks a million for your help, I really do appreciate it. Mike "Gord Dibben" wrote: Mike In the Sub workbook_open add this line Application.DisplayFormulaBar = False Change to = True in the Sub workbook_beforeclose Gord On Thu, 8 Dec 2005 14:15:03 -0800, "Mike Rogers" wrote: Gord, Thanks a ton...it works!!! (but you knew it would) One more question, and I know there may not be an answer. The formula bar can be removed via tools/options etc. If the user has this selected for their perferences can I un-select it some how when running my application and the put it back when my application is closed. "Gord Dibben" wrote: Mike Take the code out of the standard module and place it in the Thisworkbook module. To disable the right-click menu....... Application.CommandBars("Cell").Enabled = False Gord Dibben Excel MVP On Wed, 7 Dec 2005 19:15:03 -0800, "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Gord,
I just noticed that when I close with the "X" in the upper right corner, ( I have no other way as all the tool bars are gone) as soon as I click the "X", the formula bar and the status bar return, and if I cancel out they remain. Whats up with that? Mike "Gord Dibben" wrote: Mike In the Sub workbook_open add this line Application.DisplayFormulaBar = False Change to = True in the Sub workbook_beforeclose Gord On Thu, 8 Dec 2005 14:15:03 -0800, "Mike Rogers" wrote: Gord, Thanks a ton...it works!!! (but you knew it would) One more question, and I know there may not be an answer. The formula bar can be removed via tools/options etc. If the user has this selected for their perferences can I un-select it some how when running my application and the put it back when my application is closed. "Gord Dibben" wrote: Mike Take the code out of the standard module and place it in the Thisworkbook module. To disable the right-click menu....... Application.CommandBars("Cell").Enabled = False Gord Dibben Excel MVP On Wed, 7 Dec 2005 19:15:03 -0800, "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Mike
If you turned those back to True in your BeforeClose sub this would fire when you closed Excel by using the "X". I assume you are trying to keep user-interaction as minimal as possible. You have to allow for things like users closing then cancelling when asked if they want to save changes etc. I'm not really the guy you should be talking to about this. My VBA skills are minimal and have to poke and prod my way around much like you are doing. Perhaps copying some of this thread and moving over to the microsoft.public.excel.programming group would get you better service, although most of the regulars monitor all Excel groups so may jump in when they see I have bailed out<g. Gord On Thu, 8 Dec 2005 22:47:01 -0800, "Mike Rogers" wrote: Gord, I just noticed that when I close with the "X" in the upper right corner, ( I have no other way as all the tool bars are gone) as soon as I click the "X", the formula bar and the status bar return, and if I cancel out they remain. Whats up with that? Mike "Gord Dibben" wrote: Mike In the Sub workbook_open add this line Application.DisplayFormulaBar = False Change to = True in the Sub workbook_beforeclose Gord On Thu, 8 Dec 2005 14:15:03 -0800, "Mike Rogers" wrote: Gord, Thanks a ton...it works!!! (but you knew it would) One more question, and I know there may not be an answer. The formula bar can be removed via tools/options etc. If the user has this selected for their perferences can I un-select it some how when running my application and the put it back when my application is closed. "Gord Dibben" wrote: Mike Take the code out of the standard module and place it in the Thisworkbook module. To disable the right-click menu....... Application.CommandBars("Cell").Enabled = False Gord Dibben Excel MVP On Wed, 7 Dec 2005 19:15:03 -0800, "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Disable Editing tool bars
Hey Gord, I understand what you are saying, and you are right, I am just
trying to keep user interaction minimal. So this really works for what I am doing. I hope to some day be able to "poke and prod" as well as you. I am taking advantage of each opportunity to learn from as many of you guys as I can, so I really do appreciate your help and direction, you have been great... Thanks again Mike "Gord Dibben" wrote: Mike If you turned those back to True in your BeforeClose sub this would fire when you closed Excel by using the "X". I assume you are trying to keep user-interaction as minimal as possible. You have to allow for things like users closing then cancelling when asked if they want to save changes etc. I'm not really the guy you should be talking to about this. My VBA skills are minimal and have to poke and prod my way around much like you are doing. Perhaps copying some of this thread and moving over to the microsoft.public.excel.programming group would get you better service, although most of the regulars monitor all Excel groups so may jump in when they see I have bailed out<g. Gord On Thu, 8 Dec 2005 22:47:01 -0800, "Mike Rogers" wrote: Gord, I just noticed that when I close with the "X" in the upper right corner, ( I have no other way as all the tool bars are gone) as soon as I click the "X", the formula bar and the status bar return, and if I cancel out they remain. Whats up with that? Mike "Gord Dibben" wrote: Mike In the Sub workbook_open add this line Application.DisplayFormulaBar = False Change to = True in the Sub workbook_beforeclose Gord On Thu, 8 Dec 2005 14:15:03 -0800, "Mike Rogers" wrote: Gord, Thanks a ton...it works!!! (but you knew it would) One more question, and I know there may not be an answer. The formula bar can be removed via tools/options etc. If the user has this selected for their perferences can I un-select it some how when running my application and the put it back when my application is closed. "Gord Dibben" wrote: Mike Take the code out of the standard module and place it in the Thisworkbook module. To disable the right-click menu....... Application.CommandBars("Cell").Enabled = False Gord Dibben Excel MVP On Wed, 7 Dec 2005 19:15:03 -0800, "Mike Rogers" wrote: I posted the other day and got some direction. Thank you Peter Ellis. Now I need some help with what I found. I found a set of macros that should disable on opening, and enable on closing. The problem is they don't work and I am not literate in macro syntax. I have researched and all I have found says they should work. Here is what I am trying to work with: Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = False Application.CommandBars("Formatting").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Standard").Enabled = True Application.CommandBars("Formatting").Enabled = True End Sub This is in a standard module. I also nee to encorporate code to stop "copy & Paste from working, or at least the window that come from a right mouse click. Mike Rogers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Disabling tool bars | Excel Discussion (Misc queries) | |||
| Disable Customize option next to a tool bar from VB | Excel Discussion (Misc queries) | |||
| Lost all my menu bars and tool bars | Excel Discussion (Misc queries) | |||
| How do I restore my menu and tool bars in Excell? | Excel Discussion (Misc queries) | |||
| Worksheets open behind tool bars | Excel Discussion (Misc queries) | |||
Linear Mode
