Disable Red X But Keep Enable File - Close Working
Dec 24, 2008
I want to hide all toolbars and disable the red X upon opening of the spreadsheet, and to restore the main toolbars upon closing. That worked perfectly. (when I used the black code -see below)
I added in the red code as i wanted to disable the red X as well to ensure that users will always close the EXCEL worksheet via a Macro button.
The Macro will save the spreadsheet and close the worksheet and the Private sub should restore the toolbars.
View 7 Replies
ADVERTISEMENT
Feb 22, 2010
i want to dis able and enable this button using three criteria, and auto paste the comment to other sheet.
View 2 Replies
View Related
Mar 2, 2014
Is it possible to enable/disable a texbox in excel which has been assigned a macro. To make it clear I'm not talking about a textbox within a userform. I understand I should really be using command buttons but I prefer the look of textboxes.
I have the following code to change the colour of the textbox (name: txtCreateInvoice) but the final line to disable the textbox doesn't work.
Code:
Sheets(1).Shapes.Range(Array("txtCreateInvoice")).Select
With Selection.ShapeRange.Fill.ForeColour
.ObjectThemeColor = msoThemeColorText1
.Brightness = 0.5
End With
Selection.Enabled = False
View 4 Replies
View Related
Apr 24, 2009
On several of my worksheets, I'm asked if I want to disable/enable the macros but there are none associated with that particular worksheet- matter of fact, none of my worksheets have macros unless pivot tables count, so why am I being prompted in that way?
View 9 Replies
View Related
Dec 27, 2013
I have 3 checkboxes on a worksheet. I have a command button to copy/paste data that is captured from a device.
I want the data to go to the ranges that are selected by referring to the true/false driving mechanism of the check boxes.
Obviously if more than one button is checked at one time, the whole shooting match will blow up.
So....how can I allow only one check box to be active at any given time? I'm thinking perhaps a select case statement that refers to the possible conditions of true true true, true true false, true false true, etc....If that would work, how could the code be simplified?
Let's call them cb1, cb2 & cb3. Is this even possible??
View 5 Replies
View Related
Apr 17, 2008
Whenever I open an Excel file that has macros in it, a message always pop up and ask if I want to enable or disable the macros.
Is there a way to get rid of the message?
View 9 Replies
View Related
Oct 13, 2008
I started to write mini codes but still much to learn.
Private Sub ComboBox3_Change()
If Range("A1") = "TRUE" Then
ComboBox3.Enabled = 1
ElseIf Range("A1") = "FALSE" Then
ComboBox3.Disabled = 1
End If
End Sub
View 9 Replies
View Related
Feb 12, 2009
how to make txtBox1 only useable when optButton1 has been selected only.
I have 4 Textboxes
txtBox1
txtBox2
txtBox3
txtBox4
The following Option buttons are within a Frame called fraFrame1
optButton1
optButton2
optButton3
optButton4
View 9 Replies
View Related
Jun 21, 2008
I have a checkbox (checkbox1) on a userform that functions to disable two listboxes (monthListbox & yearListbox) when checked. For this, I have the following
Private Sub CheckBox1_Click()
MonthListBox.Enabled = Not CheckBox1
MonthListBox.BackColor = &H8000000B
MonthListBox.Locked = True
YearListBox.Enabled = Not CheckBox1
YearListBox.BackColor = &H8000000B
YearListBox.Locked = True
End Sub
This works fine and dandy. However, when testing the userform, when I "unchecked" the checkbox, it did not undo the actions. So my question is, is there a sub to undo the actions that the above code does after I "uncheck" the checkbox. Additionally...under what event would I write this sub since I've already completed the sub for Checkbox_Click()
View 3 Replies
View Related
Sep 30, 2008
I need a macro to disable all cut options (ctrl^x, right click cut, cut button in ribbon above), disable drag and drop, but still allow copying and pasting (the cutting affects cell references). I've already locked the spreadsheet, but users need to be able to input information and paste information in from other workbooks. This code must also only apply to THIS WORKBOOK and not affect others. Please help with a macro for this, as well as where to paste and how to properly configure macro security settings. I'm a big excel user, but this is my first time ever with VB and macros so talk to me like I'm dumb! I've pasted links to the two codes I tried, and I think one of them enabled a macro that affected all workbooks and now whatever code I put in won't save upon re-opening. It's there when I navigate to it, but it is ineffective like it's not even there. In macro security, I've enabled all macros and have the "trust VB programming" box checked. So maybe do some damage control before re-programming. I am getting a new computer in less than a month, but I want to be able to try out the code and have it work first, and I don't want to transfer the harmful code to the new computer.
P.S. This first code allows copying and cutting, but then just disables pasting, which is not what I want. The second one works great, but disables the right click menu altogether, which is no good either. And neither allow for pasting from the outside. And this is for moderate excel users, they won't try to erase the macro. I just need to keep them from making mistakes with cutting.
http://www.ozgrid.com/VBA/disable-cut-copy.htm
http://www.mrexcel.com/archive2/75500/87639.htm
View 14 Replies
View Related
Oct 14, 2009
How do I Enable/Disable Command Buttons from a Sheet?
I am using a Command Button in a Sheet to copy and paste the Data from one Sheet to another with the help of macro..
As I am not well-versed with VBA dont know all the syntaxes of VBA.
I need help for the command button..
First and Foremost, I double click a Command Button form the Control tool-box and paste it on the Sheet, I dont know how to get the name of this command button , I mean where do i get it?
Based on a condition like a value in a cell I want it to be Enabled and Disabled?
Any ideas...please I am not able to follow even after googling a lot as I dont know what's the name of the command button control I have used.
If the value entered in a particluar cell is more than the 1000 difference between two cells then the command button should be disbaled...
The Application part:
The command button is used to transfer the data in a cell lets say $I$4 to another sheet Cell J2,J3,J4 so on so forth..by incrementing the ROW number.
Now The balance gets depleted with every new Debit Entry and we need to disallow the user from entering such an amount which will reduce the balance more than The Minimum Account Balance of a bank...
View 14 Replies
View Related
Oct 23, 2008
I am attempting to modify a spreadsheet so that it will enable a range on Sheet3 (I9:K42) if a check box is checked on Sheet2. If it is not checked, it will lock that range on Sheet 3 and grey (or gray, if you prefer) out that range.
View 9 Replies
View Related
Jun 13, 2009
I have collected some data from survey respondents, and I have the following array formula:
=MIN(
IF($C$1:$C$400="happy",
IF($G$1:$G$400="tall",
IF($H$1:$H$400="american",
$F$1:$F$400
))))
What I'd like to do is make the 3rd criterion -- $H$1:$H$400="american" -- dependent on whether the string value in cell A1 ("Evaluate Americans only?") is "yes" or "no".
Because, my actual situation is considerably more complex (see below), I'm looking to insert a test within the MIN array formula to check the value of A1, and calculate the answer accordingly.
I'm aware that I could theoretically create two array arguments (one with & one without the American test), and place them inside an IF function that tests the value of A1. However that isn't practical for my real-world situation...
I occasionally see array formulas with all sorts of symbols that I don't really understand in the array context (such as * and | and . ), and I'm wondering if one of those magic symbols might be the doorway to my solution.
View 5 Replies
View Related
Jun 20, 2014
Is it possible to disable and ActiveX listbox, but allow the scrollbar to work?
Or, prevent selections without changing the enabled property?
I'd like to populate my list with items that are for information purposes only.
View 8 Replies
View Related
Oct 20, 2007
I am creating a form that performs several different functions. I would like to force a sequence of these functions by enabling/disabling the controls based on a variable.
For example, a form as two buttons Button1 and Button2. By default, Button1 is ENABLED and Button2 is DISABLED. When Button1 is clicked, a macro is run, where an Enable_Button2variable is initialized to FALSE. The last line of the macro sets the Enable_Button2 variable to TRUE. Then, because Enable_Button2 is now TRUE, Button2 then becomes ENABLED.
I am trying to apply this strategy to a ComboBox, ListBox and CommandButtons. I'm not sure what event to trigger on to poll the Enable_Button2 to enable/disable the control.
View 3 Replies
View Related
Mar 5, 2008
I'm currently building an Excel database. This database has a few comboboxes and checkboxes that are tied to each other. If I select the combobox option of "Years", the "Quarters" combobox must be greyed out. In addition depending on which option is chosen, the relevant check boxes must be enabeled or disabled. I've written some code which runs fine, until I try and activate another macro button or if I add and rename a sheet(which is not linked to anything). What should I do? Here is a sample of my code. I have a feeling it's really obviouse but I cant see it..
Private Sub ComboBox2_Change()
If Sheets("InputSheet").ComboBox2 = "Years" Then
Sheets("InputSheet").ComboBox3.Value = ""
Sheets("InputSheet").ComboBox3.Enabled = False
Sheets("InputSheet").CheckBox6.Enabled = True
Sheets("InputSheet").CheckBox7.Enabled = True
Sheets("InputSheet").CheckBox8.Enabled = True
Sheets("InputSheet").CheckBox9.Enabled = True
Sheets("InputSheet").CheckBox10.Enabled = True
Sheets("InputSheet").CheckBox11.Enabled = True
Sheets("InputSheet").CheckBox12.Enabled = True
Sheets("InputSheet").CheckBox13.Enabled = True
Sheets("InputSheet").CheckBox14.Enabled = True
End If...................
View 3 Replies
View Related
Mar 13, 2008
I am trying to allow uses of a spreadsheet to be able to select one control button which would in turn disable another one via check boxes however the control buttons do not re-enable when the undo check box is selected.
Sub Lead_REC()
' Set screen behaviour
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' Defer error handling
On Error Resume Next
Err.Clear
View 3 Replies
View Related
Jun 19, 2008
I've created a macro to disable cut-copy-paste and drag-and-drop (below), but when the user exits Excel, the drag-and-drop option remains turned off.
The user has to click on the Office button, go to Excel Options, Advanced tab, and re-enable the fill handle manually. How can I have my macro do this automatically when the workbook is closed?
'*** In a standard module ***
Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False ....................................
View 9 Replies
View Related
Oct 4, 2010
Is there anyway to easily disable/dim the Excel Close (X) Button as I want to force my users to close an Excel workbook via a button?
View 9 Replies
View Related
Jul 13, 2006
Is it possible to disable the 'X' close button on inputbox and msgbox
or any way of making the 'X' not close the box
View 2 Replies
View Related
Dec 13, 2006
Is there any easy way to disable the close (X) button at the top right of a form.
I've been trying...
Private Sub UserForm_Terminate()
UserForm1.Show
Unload UserForm2
End Sub
but i get a problem when re-opening the userform2??
View 2 Replies
View Related
Jan 9, 2010
I have a pivot table that's made visible from a "Drill Down" button on the source worksheet.
View 4 Replies
View Related
May 16, 2006
Sample code on disabling the close button in Excel workbook?
I tried the Tool -> Protect -> Check Windows method. But, it will disable the maximize and minimize buttons as well. Moreover, it will minimize the workbook.
View 2 Replies
View Related
Feb 26, 2012
I want to disable the three buttons (minimize, restore and close) excel.
[URL]
View 14 Replies
View Related
Jan 25, 2007
I have a workbook that has a macro that needs to run before it is closed. I have a button on my sheet that runs that macro and then closes the sheet. Is there a way that I can disable the "X" at the top right hand of the workbook and force the user to close the workbook via my button?
View 3 Replies
View Related
Jun 3, 2014
How to disable the default close button in a workbook not to affect in my command button close.
I tried already cancel = true but my command button close is also been canceled.
View 3 Replies
View Related
Jan 26, 2007
how can we disable/enable a cell based on a condition which is dependent on other cell data
View 4 Replies
View Related
Oct 3, 2006
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.
View 2 Replies
View Related
Jul 28, 2008
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
View 9 Replies
View Related
Sep 29, 2009
I am hoping someone can help. I am new to writing macros and have the following in the this workbook page. When opening the macro does exactly what I want and expect, but does not do the "before close" part. If I select the macro in editor and run it, it does just what I want, so dont understand what is going wrong. Do I have to put in some kind of prompt, or manually run this before close - preferably I would like it to just run.
secondly, I have password protected the workbook, but have to put the password in the macro, is there any way of making this invisible to anyone who then reads the macro....
View 9 Replies
View Related
