Force To Save / Protect Macro
Dec 17, 2009
I've created a file where our accountmanagers can mace price calculations. But eversince i've added a macro to force them to fill the form completely, they have gone and tried to hack the macro (and with succes).
I now would like to add a protection for the macro, so they can only access VBA when "sheet1" is unprotected. How can i do this?
I also would like to create a macro so they only can save the file as a Macro Enabled Excel file (.xlsm),
how can i do this best? .....
View 9 Replies
ADVERTISEMENT
Feb 16, 2014
I've looked through the threads and have seen some Save As posts, but nothing that seems to fit my task. All I want to do is force the Save As file type to .xls (for some reason on our network, xlsm causes save errors... After looking through various posts, I found this:
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
Exit Sub [code]....
The problem is this causes the Save As dialog box to show up twice... How I get the Save As dialog box to show up once?
View 4 Replies
View Related
Apr 1, 2009
I have created a tool in excel to facilitate different requests from our sales force. A drop down list in the first worksheet lists the request types. Once the type is selected the user clicks a command button that sends the user to the corresponding worksheet specific to the request type.
My problem is that I need to verify that the cells on the specific worksheet have all been completed. I tried to verify when saving in the workbook module, but I don't know how to only check the selected request worksheet.
One thought was to insert save command buttons on each worksheet and only allow saving through those buttons. (So I would need to remove any saving functionality from the file menu/toolbar.) Then I could attach the verification code to that button on each worksheet. Preventing saving until the requested data was entered.
If this methodology will work I need to know how to do the following:
1) remove alternative saving methods
2) Once those methods are removed, how do I code a command button to check that specific cells are not null and then "save as".
3) if cells are null then, stop save and show msgbox.
I am working in Excel 2003.
View 9 Replies
View Related
Jan 9, 2008
I need to know the line or two of code to disable the
FILE and CLOSE buttons to force saving to the same file.
I know they exist, because I obtained and tested them in
an excel sheet, but somehow managed to lose the code
and the link from which I obtained them.
View 4 Replies
View Related
Mar 28, 2014
I am trying to save my workbook to a file on my shared drive.
When I change the default save location in the options menue it works fine for the first instance but when I return and open the file ... even the one from the shared drive it faults back the C: drive location on my laptop. My excel version is 2010 and my code for file name is below ... I just dont know how to force the path/destination in VBA
Code:
Dim namefile As Variant
With Worksheets("Panelas")
namefile = .Range("V3") & "_" & .Range("C5") & "_" & .Range("P3") & "_" & Format(Now, "yyyy-mm-dd") & ".xlsm"
End With
namefile = Application.GetSaveAsFilename(namefile, fileFilter:="Excel Files (*.xlsm), *.xlsm")
[code]....
View 7 Replies
View Related
May 1, 2009
When you click File>Save As, you get a save as window that shows the current file name to "Save As"
I would like to create a macro that shows the values in a couple of cells (lets use Range("A1") & Range("B2")) instead of the current file name.
View 4 Replies
View Related
Mar 15, 2008
I have a workbook with several Sheets. I wish to automatically protect all sheets with specific user permissions, i.e. Select Unlocked Cells, Insert Rows, Use Auto Filter and Edit Objects, as I would have available when using the manual approach. Also, I wish to invoke a Password in the process.
Additionally, I need this code to operate when the file is Saved or Closed... transparent to the user.
View 9 Replies
View Related
Oct 30, 2006
I want to find a code to lock all the cells with data once a button is pressed and with the same button click i want the workbook to be saved.
View 5 Replies
View Related
Mar 4, 2009
I have Excel file that is linked to Access Database. Everytime the user opens the file, there is pop up message that warns the user we there to enable the macro or to disable it. I want to force the user to enable the macro or even better if I can make disappear this warning and automatically enable the macro. See attached for more details.
View 3 Replies
View Related
Mar 22, 2012
I have this running and Section A is working but Section B is not. I basically want it so that if conditions a, b, and c occur, excel pops up with that messagebox.
Condition A: Cells(8, 3) changes via formula referencing an outside cell
Condition B: The new value of Cells(8,3) is a numeric value.
Condition C: The change in it's value is not more than a given threshold
change as defined in Cells(8, 13)
why the second section is not popping up with a msgbox when the value of Cells(8, 3) changes.
Public bbb as long
Sub Other()
bbb = Cells(8, 3)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bb As Integer
Dim cc As Integer
[code]....
View 4 Replies
View Related
Aug 6, 2007
I have a sheet which is protected but people are still able to change cell formats etc if they use the standard paste method. I want people to be able to paste data in but it forces paste special values to stop people pasting formats etc over already formatted and locked cells.
I know I could write a macro and run it using the shortcut keys ctrl-v but then the users lose the ability to undo if they make a mistake.
Is there a way to force paste special or allow users to run a macro but be able to do undo if they make a mistake?
View 9 Replies
View Related
Sep 12, 2009
I have a spreadsheet that has a button named Exit Requisition Program on it to close the application with. When the button is used everything is wonderful. All of the data on the sheet is saved and the application closes. I have a problem with some users clicking the red X without saving changes and that creates a big problem.
What I need to do is any one of several things.
1. Remove the red X completely.
2. Deactivate the red X.
3. Have a message box pop up instructing the user to use the Exit Button when the red X is used. Any one of the three methods listed above would work for me.
View 2 Replies
View Related
Feb 8, 2007
I have a workbook that pulls in index data from Bloomberg, most data is updated realtime, but one index we track is only updated late at night. I want to write a macro that will refresh all of the data in the worksheet and then save a snapshot of the workbook. I have all the code for saving the snapshot, but getting the data for the one index to refresh is proving difficult. None of the standard ways I know to refresh external data (both Excel commands via tools menu, and Bloomberg specific refresh commands) seem to work on these cells. The only way so far I've found to get these cells to pull in current data is to delete them and rewrite the formulas.
suggestion for a Macro that would force a refresh of this data?
View 7 Replies
View Related
Sep 6, 2012
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.
View 1 Replies
View Related
Oct 22, 2008
I'm trying to have a macro that opens the save as window, places the name in cell f5 and then allows you to save the workbook with cell f5 as the filename. I have managed to get the save as window to come up and the cell f5 as the name, but when I press save is doesn't. Here is what I have (also, is there a way to direct this to a specific folder).
View 4 Replies
View Related
Feb 18, 2010
I have been using excel for years but have always managed to resolve issues using formula's etc but now i have had to step into the realms of macro's. I am slowly learning but urgently need help with the below problem which is bound to be a simple solution.
I have compiled a workbook for users to administrate on a daily basis. (each worksheet is a different day of the month but the same template) There are a few hidden worksheets as the info is extracted from these to run reports.
The issue/s i have is that administrators use cut, copy and paste to repeat data opposed to retyping, this messes formats, formulas and validation up. I have used a macro from the net to remove this option and it works fine. Problem is that if they do not enable macro's this will cease to be an option.
I understand that you cannot remove the option of enabling macro's but you can force. Again used a macro from the web that if you don't enable it only shows a welcome page saying that macro's need to be enabled to use. (all others are hidden) if macro's are enabled the welcome page disappears and the worksheets 1-31 reappear. Again this worked fine.
I tried putting both macro's into the same workbook but kept coming up with errors, each one i resolved led to another.
So short of it i need a instructions/macro to force macros and remove cut copy and paste.
View 14 Replies
View Related
Jan 17, 2010
I have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
View 10 Replies
View Related
Jan 17, 2013
Some sensitive data is held on worksheets that are used by people that do not have access to see the data.
It would be most convenient to keep the data in these worksheets but hide it from view of the users.
Is there a way to password protect a users ability to unhide a column?
View 2 Replies
View Related
Jan 6, 2006
How can I protect a macro from other people changing it?
View 6 Replies
View Related
Dec 4, 2008
I have button on a worksheet that has a macro assigned. I need other people to see the workbook, but I want the button DISABLED for them to click, but able for me to click it. I have written in the macro to protect the sheet once it has run, but the button is still pressable.
View 2 Replies
View Related
Oct 24, 2007
I currently have a macro which i run by selecting tools, macro, macro and then selecting the one i want and pressing run. if i press run I want a box to appear asking me to input a password and if password matches then run that specific macro, can this be done
View 2 Replies
View Related
Oct 22, 2008
Is there a way to password protect the macro code, so that someone can't open the coding?
View 4 Replies
View Related
Mar 20, 2008
I have a workbook with some sheets password protected and other sheets not protected.
I have a macro that unprotects the sheets, but it requires the password to be typed multiple times.
I want to put the password into the macro so it doesn't require manual entry of the password lots of times.
Each sheet has the same password, so I would expect to enter it for the first sheet only.
What I have came from a simple macro recorder selecting each sheet in turn:
Sheets("wages").Select
ActiveSheet.Unprotect
Sheets("Comparative").Select
ActiveSheet.Unprotect
View 9 Replies
View Related
Sep 16, 2008
i use a macro to protect several sheets in a workbook.
When i protect the sheets, i only want to be able to cells and nothing else. How should i mdify the code below to do this? I tried record the macro and un checking the "select locked cells" box which is one of the only 2 boxes checked. It works, but then when i save the wb and re-open it it allows me to select locked cells.
Sheets("FF - WC").Select
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("FF - UR").Select
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
View 9 Replies
View Related
Aug 21, 2008
Greetings I have a workbook with many sheets and i want a macro to protect all the worksheets with certain settings except for one sheet, sheet 3.
I have borrowed some code from this page that I cannot get to work and I would appreciate any help. the code should also have an if statement that unprotects sheet 3 but i don't know how to do that either. I just get syntax errors
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="secret", DrawingObjects:=True, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _
True _
.EnableSelection = xlUnlockedCells
.EnableOutlining = True
Next wSheet
End Sub
View 9 Replies
View Related
Feb 5, 2007
My spreadsheet has 32 worksheets, I've recorded a macro to do the job "save, save as with specific name", but what I want is, when save as, I wanted the file name increase in one number, and the date in a specific range "H8:J8" the date should be the next saturday.
here is part of the code.
Range("D11:J65").Select
Selection.ClearContents
Range("J44").Select
Sheets("NsFri").Select
ActiveWindow.SmallScroll Down:=-12
Range("A12:A21,D12:I21").Select
on this example I want the file saved as "PrA4W05.xls", being the next week "PrA4W06.xls", and so on.
and on "H8:J8" the next saturday.
View 9 Replies
View Related
Oct 23, 2008
I am looking for a way to protect and unprotect all worksheets with a password through a macro. I have excel 2007.
View 2 Replies
View Related
Sep 10, 2009
I have recorded the following macro and wish to add unprotect (with password), run the macro - which formats the worksheet and then password protect all cells with the exception of range E15:H413 which I want to leave editable by users.
View 9 Replies
View Related
Jan 4, 2010
I created excel form with calculated formula and macro. Everything worked fine. To protect spreadsheet formula I protected file. But after protection when I ran file I get macro error message. Then I remove protection become work without error messgae. How to fix that problem?
View 6 Replies
View Related
May 9, 2006
I have two macro buttons on a page that hide and unhide columns. When I got to protect the sheet .. the buttons no longer work and ask me to DEBUG. Is there a way to have macro buttons performing tasks AND have the sheet still protected?
In the protect sheet window .. am I have ticked is
Select Locked Cells
Select Unlocked Cells
But these are ticked as a default.
View 2 Replies
View Related
