Macro To Disallow Delete Action?
Mar 25, 2013
So I have this worksheet full of formulas that I do not want users to accidentally delete. Now, I know I can use the standard "Protect Sheet" option to not trigger any accidental changes but I would like an additional safety check (since management users will have access to unlock the sheet) via a macro to prompt the user with a message box (with Yes / No options) which essentially asks the user if they are sure they want to delete a cell if the "Delete" button is chosen? If the answer is No, nothing will happen. But if the answer is Yes, it will delete the contents in the cell.
View 1 Replies
ADVERTISEMENT
Jun 30, 2007
The below piece of code is from a macro that creates a summary sheet based upon the name of the worksheet. My problem is as follows: I have another macro which copies the summary sheet and renames it in the format dd-mm-yy and I want to exclude this from the macro that constructs the summary sheet. How do I modify line 4 to capture the exclusion of all sheets with format ##-##-##? Note: The current line is my poor attempt at doing it.
If (A$ = "Template") Then Goto 10
If (A$ = "Create New Sheet") Then Goto 10
If (A$ = "Summary") Then Goto 10
If (A$ = Like "00-00-00") Then Goto 10
' Process the current sheet
Range("A" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C15"
Range("B" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C16"
Range("C" + Format(j)).FormulaR1C1 = ""
Range("D" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C18"
Range("E" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C19"
Range("F" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C20"
Range("G" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C21"
Range("H" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R4C22"
j = j + 1
10 Next i
View 10 Replies
View Related
Jun 18, 2014
I'm trying to prompt user when he tries to delete a value from a cell so he can confirm is action but not the same way to all cells.
Let's say that I have cells A1:F10 with values, and user should be able to change this values but not to delete them (can't be a blank cell) and I want to prompt him that info (something like "Cell can't be blank!").
In other range a want a different thing: let's say cells H1:K20. In this cells user should be able to change cells values and also delete them but I want to ask him for a confirmation (something like "are you sure").
View 7 Replies
View Related
Nov 19, 2008
I am trying to loop through an action within a macro and require some assistance.
I have the following
View 2 Replies
View Related
May 30, 2009
I have a spreadsheet that when I am running an specific macro and userforms, this one sheet is visible, when the macro is not running, then, the sheet is hidden.
Now, that happens automatically the way that I set up my macro; however, if the macro is aborted, the sheet doesn't hide.
View 7 Replies
View Related
Nov 23, 2007
I have a macro that is working fine. It calculates some values based on a SQL database and then copy the values and paste them into another spreadsheet.
However, I need to repeat the same procedure 1296 times.
Please see below the macro.
Sheets("QUERIES").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("D5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("C5:G5").Select
Selection.Copy
Sheets("AWB RANGE").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I need to increment the line #3 (ActiveCell.FormulaR1C1 = "2") that should start with 2 and end with 1296.
Also, I need to increment the line #11 (Range("K2").Select) that should start with K2 until K1296.
The other parts of the macro will not change.
Would you be able please to give me a hand on this explaining how to create a counter to start on 2 and stop with 1296 ?
View 9 Replies
View Related
Jun 7, 2006
I am trying help protect a project from all my colleagues that have a version of password breaker that is actually an add-in. I have already managed to disable or grey-out all of the other commandbar options that I don't want them to have access to during their use of the model but can't figure out how to disallow them from accessing the "Add-ins" option from the "Tools" menu. I can, of course, disable the entire tools menu but don't want to do that yet.
View 9 Replies
View Related
May 13, 2014
I have an excel Workbook made up of sheets that are named after months ("May 2014", "Jun 2014" etc). I want to pull data from each sheet that comes after a user specified date.
View 3 Replies
View Related
Jul 29, 2014
I need the tabs of a project action log to auto-populate and auto-delete in a master log. (So when something is added or deleted in a tab it is added or deleted on the master) I use excel a little bit for work and personal finance purposes but I have zero experience with macros or VBA.
View 1 Replies
View Related
Jul 12, 2014
I have some excel files which should be saved in Excel 97-2003 Workbook format . I am trying to write vba code for that i am struggling at starting . problem is i have 6 excel files which should be saved as Excel 97-2003 Workbook format i don't know how i give 6 files path to code .
View 2 Replies
View Related
Dec 13, 2009
Is there a way to disallow users from using fromulas in a spreadsheet? I have an issue in which users are using formulas to concat data as a shortcut to typing in data but they are constantly referencing the incorrect cells.
View 6 Replies
View Related
Feb 23, 2010
x = InputBox("Please Enter the Loan Amount:")
If x < 0 Then MsgBox ("please enter a loan amount greater than 0.")
but then how do I bring up the input box again until it is filled in correctly?
I tried Do until a few times but made a mess of it, I also need to disallow alphabetic and symbol characters.
View 9 Replies
View Related
Apr 11, 2012
I am trying to make an extensive action items log for work.
On Sheet 1, rows 1-3 are blank. Row 4 contains column headers.
Column A: Item #
Column B: Description
Column C: Drawing #
Column D: Conforming
Column E: Nonconforming
Columns F, G, H don't really matter
What I am wanting is to be able to put an "x" or check mark in either column D or E (conforming or nonconforming). Then I want to run a macro with a push button (I know how to make these) that when pushed scans the spreadsheet row by row until it finds the "x" or check in Column E. I want it to copy every instance of this and send it to an Action Item Page that I have temporarily called Sheet 2. (This way I can go here to see all nonconforming issues). I do not want it to remove the information from Sheet 1.
Then, I want to be able to change the "x" or check on the Action Item Page to Column D (back to conforming) and the macro (push button preferably) copy and send that information back to the original spreadsheet in the SAME row it was.
So, in short I want this to be a fluid way of checking off a punchlist of items.
To complicate things even further, if possible, I would like to have several worksheets send information to the one Action Item List rather than just the one.
View 2 Replies
View Related
Jul 26, 2014
I'm using macros to apply some complex sorting criteria. It is also applying some cell formatting specific to the sort criteria being applied.
At the start of each macro, I'm resetting all cell formatting to remove any formerly applied formatting that is not correct for the new sort criteria. Basic stuff.... works fine.
Now I need to ensure all formatting created by any macro-generated sort gets removed if a user applies a sort using any sort function from the Ribbon. (Reset all formatting prior to any 'manually applied' sort).
I can create the macro to reset the formatting, just not sure how to trigger it upon user action.
Is it possible to trigger a short macro to reset cell formatting when a user applies a sort using the Data>Sort, Data>Sort Ascending, or Data>Sort Descending commands from the Ribbon?
View 9 Replies
View Related
Feb 6, 2010
I am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern.
If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.)
Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?
View 9 Replies
View Related
Mar 10, 2014
Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
Attached File : LoopingVBA.xlsx
View 3 Replies
View Related
Sep 27, 2012
I am about to write a small macro to do a iteration calculation. I would like though for the user to select me a cell so that the macro uses the value in that cell to start calculations. Now, what I think would be the problem is how do I code a "time" or notice an action such as clicking in a cell to continue with the macro.
Would it be by inserting a pause in the code of a certain time so that gives the user time to click and wait for the code to start? If so, how would much time is enough? Is there another thing that could restart the code such as "noticing a click in a cell" command in VBA?
View 1 Replies
View Related
Sep 17, 2013
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
[Code]....
View 8 Replies
View Related
Jun 19, 2009
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete
View 4 Replies
View Related
Jul 16, 2009
I am using Data Validation on some fields to create a drop down list from a named range! These fields however allow you to enter values that are not in the list.
Is there a way to make the cell have to be an entry from the data validation list?
View 6 Replies
View Related
Apr 27, 2009
How to set a password before executing any action.
For example, I have "cancel" button in my userform. If i click "cancel" it unloads the form. Instead of that i need a password prompt. so when i click the "cancel" button ,it should ask for a password. only if i provide the correct password then only the form should be unloaded.
View 6 Replies
View Related
May 28, 2014
JSON import to Excel - Stack Overflow
I understand most of what this post is saying, but I don't understand how to put it together in an excel module or class module.
View 5 Replies
View Related
Jul 18, 2006
I have a database in excel which I want only selected users to have edit rights & others should have readonly rights. I have written the following code where a pop up would appear in selected cells warning user for editing the said cell. What I want is when a user clicks yes he should be able to edit it & when he clicks no the cell should get protected.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rTriggerCell As Range
If Not Intersect(Target, Range("D1:D100")) Is Nothing Then
Set rTriggerCell = Target
Application.EnableEvents = True
If MsgBox("Edit Cell?", vbYesNo)
End If
On Error Goto 0
Exit Sub
End If
View 6 Replies
View Related
Oct 21, 2008
How do you set the code so that, if a condition is met, then several cells are filled with values?
So far, my code is
HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If
View 11 Replies
View Related
Aug 13, 2009
If I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.
Can this code be modified so that the message box does the following...
1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.
2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.
I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.
3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this).
View 4 Replies
View Related
Feb 1, 2012
I have created vba which I want to comment out. I totally stumped how to create this action on vba. My current vba code which I want to comment out using vba
Code:
Option Explicit
Sub YearlyInput()
End Sub
'Speeding Up VBA Code
With Application
[Code] .......
View 8 Replies
View Related
Aug 24, 2012
I have a for which consists of
2textbox
1 listbox
1 textbox.
While entering the data, the cursor does not stop in the list box and goes directly goes to the next textbox. Autotab is set to true.
View 4 Replies
View Related
Feb 25, 2008
Last friday my personal.xls file went missing. I cannot find it anywhere which means I am missing about 10 key macros. While it is not the upset of the century, my problem is that now when I go into Record Macro and try to "Store New Macro in:" -> "Personal Macro Workbook" and then click OK, Excel brings up a msg box that says:
Personal Macro Workbook in the startup folder must stay open for recording.
Click OK and Excel says:
Unable to Record
View 9 Replies
View Related
Aug 14, 2008
What i have is a userform which contains textboxes a user can enter or change data in.
What i'd like to do is to have a button called something like "undo last" whereby a user can 'swap' back the last change they made.. does anyone have any idea how i might go about it?
View 9 Replies
View Related
Feb 20, 2009
I have the following problem to solve:
Cell x in col K is filled with a col number e.g. 'V' (validated via validation list).
Cell x in col L is filled with an amount.
How do I get the amount of cell Lx into cell Vx? This to be done only when the amount Lx not equal to zero.
Can this be done with a formula, or do I have to use a macro? I tried to find the solution for both options, but sofar I have nothing that works. Via the Excel help I could not find the solution. I know how to create a macro activated via a button. I prefer automatic filling by Excel.
View 9 Replies
View Related
