Re-enabling The Command Bar On Exit

Nov 11, 2008

I have a worksheet that I protect from cell deletions by disabling the command function for 'delete' using the following

CommandBars.FindControl(ID:=292).Enabled = False

I execute this code in two subs:

Private Sub Worksheet_Change(ByVal Target As Range)
CommandBars.FindControl(ID:=292).Enabled = False

...
and
...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CommandBars.FindControl(ID:=292).Enabled = False

...
all fine and good, works great only problem is...

once disabled this function is NOT re-enabled when I leave the workbook.

When the sheet deactivates I run this:
______________________________________________________________
Private Sub Worksheet_Deactivate()
CommandBars.FindControl(ID:=292).Enabled = True
End Sub
____________________________________________________

that works fine while I am changing worksheets within the workbook.

But it doesn't do for this function to remain disabled even after I close the workbook and then it's disabled in all the other workbooks.

What I need is to have the code re-enable this function when the workbook closes or when I activate a different workbook so that the function is only disabled within the specific worksheet of the specific workbook and it remains enabled everywhere else ESPECIALLY after I close that sheet and workbook where I have it disabled!

I tried this:

______________________________________________________________
Private Sub Workbook_Deactivate()
CommandBars.FindControl(ID:=292).Enabled = True
End Sub
____________________________________________________

View 9 Replies


ADVERTISEMENT

Enabling A Command Button

Feb 17, 2007

I have a userform with, amongst other controls, a ListBox1 and a CommandButton1.

I have set the CommandButton1 to enabled = False in the properties and need it to be enabled when at least 1 item in the ListBox is selected (the ListBox is multiselect).

I have the following code, but it is not working.

Private Sub ListBox1_Click()
CommandButton1.Enabled = True
End Sub

Both controls are in the same userform and the names correct.

View 6 Replies View Related

Enabling / Disabling Worksheet Command Buttons?

Mar 25, 2012

I have some command buttons on a worksheet that I would like to disable, preferably make not visible, until a specific person logs in. I already have code to display worksheets, or hide them, depending on who is logged in. I would like to just add this code to the end of the login procedure, to make these buttons only visible when that person is logged in.

View 3 Replies View Related

Encourage Enabling Of Macros: Disable Macros When Opening Then The Worksheet Menu Bar And Other Command Bars Are Still Available

Aug 26, 2009

I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.

View 6 Replies View Related

Exit Function- The Result Returned To Me And Exit (all) Open Functions

Apr 24, 2009

I would like to call upon a function until a certain criterion is fullfilled. Then, I would like to have the result returned to me and exit (all) open functions. For value1 = 1 and value2 = 10 I expect value1*value2 = 100. Instead, the routine returnz zero. What is the logical flaw in the code below.

Function testfunction(value1, value2)
If value1 = value2 Then
'Calculating the difference
testfunction = value1 * value2
Exit Function
ElseIf value1 < value2 Then
value1 = value1 + 1
Call testfunction(matrix1, matrix2)
End If
End Function

View 9 Replies View Related

Enabling Macros

Feb 11, 2010

I recently reformatted my hard drive. When I re-installed Office 2007, I was unable to run a Workbook that has a Macro in it. First, I went to Add-ins and added the Solveradd-in. When I opened the workbook, I enabled Macros. However, all I get is a maessage saying that the MVB did not compile. I know there are some things you have to do to solve this, but I cannot remember the procedure I went thru 2 years ago to get this to work. Does anyone know what steps I must take?

View 8 Replies View Related

Pop Up All The Time When Enabling Macros

Feb 10, 2010

I have a workbook with that contains macros on a worksheet. When I open the worksheet, I sometimes forget to enable the macros.

Questions

1.Sometimes I get the message about macros being in the file that I'm opening and sometimes I don't - I don't understand why it doesn't pop up all the time and be consistent.

2. If I don't get the pop up screen or if I do but forget to enable the macros, I get reminded when I try to run a macro. For example, I have a check box and try to check it but I get the message that the macro has to be enabled. So I enable it, but the macro doesn't run. I have to uncheck and then recheck the box to get it to run.

View 9 Replies View Related

Enabling Macro Via Password

Mar 23, 2007

I have a shared workbook with four buttons executing different macros. Problem is that some of the users should only be able to use two of the buttons. To avoid that they accidently press the "restricted buttons" i would like a password (or keyboard shortcut) to enable these.

View 9 Replies View Related

Enabling Actual Pivot Editing

Feb 14, 2014

I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editing.

How to enable this.

View 2 Replies View Related

Why Enabling Macros When I Open Some Of My Files

Apr 21, 2009

i am trying to work out why i always have to enable macro's when i open some of my files that i have macros in, as i save them as enable macro file. I run excel 2007
as there are files i want to open without clicking on the enable button.

View 3 Replies View Related

Enabling Macros In Multiple Session

Feb 17, 2010

I am using the code below to open multiple workbooks in their own instance of Excel.

On my system, as well as most of the ones I've tested it on, enabling macros in the "parent" workbook, i.e. the one that contains the code, leaves them automatically enabled in the new session of Excel, as though the settings are carried over.

However, recently I've found a few systems where this is not the case. Enabling macros in the parent workbook opens the others, and then I have to enable macros again in each of the new sessions. Is there a way to fix this? All of the systems mentioned above have macro settings set to "disable with notification" and this cannot be changed.

View 6 Replies View Related

Enabling Macros Without Re-opening Worksheet

Aug 25, 2006

if you open a workbook & choose not to enable macros, is there any way to later change your mind & set macros to enabled without having to close & reopen the workbook?

View 9 Replies View Related

Excel 2013 :: Enabling ActiveX Controls

Apr 4, 2014

I bought a Surface 2 tablet. When I go to "settings" in the Trust Center it does not show any option to enable Active X.

View 3 Replies View Related

Using A User Created A Form In Other PC Without Enabling Macros?

Jun 17, 2014

Is it essential to enable macros in whatever the pc i use, if i am interested in using a form which i created in a different pc with macros. is there a way to avoid this. because each and every time i dont want to change the settings in excel in other pc. the form which i create will be filled by my colleagues and they have to send it back to me.

View 2 Replies View Related

Enabling Cut / Copy And Paste Option In 1 Worksheet?

Mar 3, 2013

Here i am attaching the excel sheet where macro is enabled. I have added macros in last spread sheet (Consolidated) everything is working fine. Whats messing is i have copied a macro for the entire workbook, which will disable Copy/Pase/Cut option to all engineers. But problem is its disabled for me as well, every time i have to delete the code for copy/cut options. I just want cut/copy option in the last worksheet (Consolidated) where i can get all the result with out touching the rest of the sheets.

View 3 Replies View Related

Enabling / Disabling Form Checkboxes Based On Value In A Cell

Apr 2, 2014

I have a sheet with around 3,000 rows of data. I need to be able to enable/disable different comibinations of three checkboxes based on the value of a cell in column D as follows:

If Band says £0k - £75k then all three check boxes should be disabled
If Band says £75k - £250k then the Step 1 check box should be enabled but the Step 2 and Step 3 check boxes should be disabled
If Band says £250k - £500k then the Step 1 and Step 2 check boxes should be enabled and the Step 3 check box should be disabled
If Band says £500k plus then all three check boxes should be enabled

When a checkbox is disabled I'd like it to appear visible but greyed out so it is obvious the option is not available, maybe via conditional formatting. I don't know if this can be achieved within any code rather than having to manually add conditional formatting afterwards?

I've attached a sample to demonstrate. Is this possible to achieve? I'm open to alternative approaches as well if this makes things easier to do. The mere idea of adding 9,000 plus check boxes if already scary!

View 8 Replies View Related

Excel 2013 :: Enabling Undo After Running A Macro?

Mar 18, 2014

A coworker just ran something by me that i'm quite frankly baffled by. After running a macro, the undo function becomes disabled up to the point of when the macro was ran. I've done a quick search through the forums and can't seem to find this question so i thought i'd ask.

Is there an option to toggle somewhere or 3rd party addon that keeps Undo enabled after running a macro? On a similar topic for my own curiosity, what's the reason that it disables?

View 3 Replies View Related

Enabling Excel File With Password For Copying In External Drive?

May 8, 2012

I'm looking for enabling an excel file with a password that prevent it to be copied on an another drive.

View 1 Replies View Related

Enabling Manual Calculation On One Specific Workbook But All Other Open Workbooks Remain On

Mar 24, 2014

I want a specific workbook to be always on manual but when I open other workbooks I want them to remain on automatic even though the first workbook is set on manual through vba code. Is that possible to be done?

This is the code I run:

Private Sub Workbook_Activate()
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False

[Code] .....

I know that Application. Calculation refers to all open workbooks but I don't know the code to specify the manual calculation to this workbook only while others are open.

View 7 Replies View Related

Press Command But And Execute The Code Assigned To Another Command Button

Apr 14, 2009

Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).

View 9 Replies View Related

Shell - Adding Another Command To Forfiles Command?

Apr 19, 2014

I am playing around with the Forfiles command (being called from Excel via Shell)..

I can't for the life of me get it apply a second command (such as getting the file size of each file)..

For example:

VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d

[Code]....

The end result being the filename and the filesize shown..

View 4 Replies View Related

Excel 2007 :: Using Search Command With IF Command

Jun 20, 2013

Excel 2007

I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")

Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned

how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.

View 2 Replies View Related

Exit Sub Alternative

Aug 3, 2009

Exit Sub alternative. I have the following

View 5 Replies View Related

Exit On Error

May 20, 2009

I have some code that expects the user to select a file to open. If they don't select a file it posts an error message "Stopping because you didn't select a file" then it exits. However then Excel pops a debug window. How can I get it to cleanly exit and not pop the debug window?

View 9 Replies View Related

Exit Without Saving

Jul 21, 2006

how to set up a macro that will close my workbook without saving.

View 3 Replies View Related

InputBox - Exit Sub When Cancelled

Aug 30, 2012

How to use Exit Sub when I push Cancel with InputBox. Heres my code

VB:
Dim fName As String
Dim strMessage As String
strMessage = "Select .csv File"

MsgBox strMessage

[Code] ....

Its working at the moment when I push cancel, but when I select a csv file to import it comes up with an incompatible error.

View 2 Replies View Related

How To Exit Out Of Alert Errors

Jan 13, 2014

I entered an activex object command thing.. and now I'm not sure how to exit out of it.. it just keeps alerting errors messages like reference is not valid and other ones depending on what I type in the formula box.

I just want to remove the object, but I can't get past the error alerts.. they just keep coming every single time I click somewhere on the workbook trying to exit out of it.. so I'm not sure how to even get out of it.. I don't want to open up Task Manager to exit out of the entire program because I didn't save my workbook.

View 2 Replies View Related

If Statement - If One Item Is X And Another Is Not Y Then Exit Sub?

Aug 15, 2014

I have a userform that allows users to choose their name (cboName) and an item (cboItem) from comboboxes.

Now, 2 specific items can only be selected from certain people

Lets say there's ItemA, ItemB and ItemC, and the people are named Joe, Bob and John

Now, only Bob and Joe can select ItemA or ItemC. How do I formulate If statements that prevent John from selecting ItemA or ItemC? Something like:

[Code]....

View 1 Replies View Related

Exit Sub Is Deactivating All Other Macros

Nov 24, 2008

I have the code below assigned to a commandbutton. I also have 4 or so other private sub macros on this worksheet. When the activecell is in range 2 and I push the button, it exits the sub routine like it's supposed to. But then it deactivates all the other macros and they won't work again until I close the workbook. How can I get this code to exit the macro if the first if statement is true and to not affect any other macros?

View 3 Replies View Related

Tidy Way To Exit Sub On Cancel

Jul 18, 2009

I have an input box which has OK and cancel buttons If the user clicks cancel I would like to routine to end, which I can do with exit sub. However this particular sub routine has been called from another so the rest of the code continues. Is there a way of exiting all the routines on cancel -- or a neater way than exit sub ?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved