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),
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?
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 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")
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.
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.
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
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?
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.
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?
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 .
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).
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.
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.
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.
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
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.
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
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.
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.
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?
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
