So I have a Workbook that I want to make read-only/protect after a certain date. I also want to force the user to have macros enabled. So far I have the following script, but despite changing it to read-only it doesn't seem to do anything. I have also tried to enable protection on the sheets after that date, but am getting errors.
View 2 Repliesthe system i'm creating involves many user ... some may know how to decode the system esp. on the startup ... if they click disable macro they can view, edit and delete important data in the system ... can somebody guide me on how to automatically enables macro on excel file on startup???
I've tried:
- setting security to low - but got auto reset by administrator.
I have been using Erik's fantastic "Force Enable Macros" code (exactly as it appears)...
force enable macros & ASK to save changes (SOLUTION)
...with the desired results (and comfort) under Excel 2003.
The code works under Excel 2007. However, if there is another workbook open at the sametime (regardless of whether Workbook two has macros or not), I get an Excel "crash";
Code:
Microsoft Office Excel has encountered a problem and needs to close.
[ ] Recover my work and restart Microsoft Excel There is also a Event ID 7001 in the Event Viewer. I have run Excel diagnostics and there are no issues.
I am utilizing the following code to force the user of my database to enable macros. This is the
View 4 Replies View RelatedI have a spreadsheet sent in an email. When I open it there is a warning bar saying
"Protected View This file originated from an Internet location and might be unsafe. Click for more details." then a button with Enable Editing.
I click the button and can edit the spreadsheet and save my changes. But the next time I open the file I get the same warning again. What is the way to designate that this spreadsheet is safe and prevent the warning recurring whenever I open the file?
is it possible to unlock a chart but then disabling to change the source data?
Background: I want the user to be able to add numbers etc. but he shouldn't see the underlying table...
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 RelatedI have a User Form which i want to use in such a way that editing the worksheet directly is not disabled while the userform is running. I would also like to use the right button mouse menu(cut, cpy,paste) while i am using the user form. I am using two Monitors with my PC and i want to work with User Form on one monitor being able to edit worksheet without having to close my User Form.
View 2 Replies View RelatedI 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?
I have an accounting spreadsheet that is used by 25-30 people. This spreadsheet has a button the user can use to save this spreadsheet allowing the spreadsheet to save itself to a specific folder path base on the dates entered in the spreadsheet.
I have begun tracking certain items in the accounting spreadsheet and have added a section to the save macro that gathers data from the accounting spreadsheet and saves the data to a ‘Spreadsheet Stats’ spreadsheet. Since it was possible that more than one person might try saving the spreadsheet at the same time and try to open the ‘Spreadsheet Stats’ spreadsheet at the same time, I placed a loop in the macro that determines if the 'logging data' spreadsheet when opened is marked as "read-only". If the ‘Spreadsheet Stats’ spreadsheet is opened as "read-only", the macro closes the ‘Spreadsheet Stats’ spreadsheet then reopens the ‘Spreadsheet Stats’ spreadsheet again and again until opened without the "read-only" status. Code seen below.
Do Until Now() = start_time + 0.002082 Or Now() > start_time + 0.002082
Workbooks.Open Filename:= _
"G:EDCUSTSERV1_ACCTGIPL SpreadsheetsBilling SpreadsheetsData DropSpreadsheet Stats.xls"
Set WB = ActiveWorkbook
If WB.ReadOnly = False Then
Exit Do
End If
WB.Close
Loop
The macro works great, however, should a user try to save the spreadsheet and the loop happens to open and reopen the spreadsheet until the "read-only" status is gone, when the macro completes, a window pops up and alerts the user that the ‘Spreadsheet Stats’ spreadsheet is now available for editing.
My question: Is there any way to prevent that pop up window? I don’t want the user to think they need to edit the ‘Spreadsheet Stats’. Remember that the macro has completed when the popup window shows up, so Application.display alerts set to false
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close
Else
End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
I have a set of macros that allows me to call up lotus notes and send emails. However, i am not able to call up the 'prevent copying' function of lotus notes.
View 9 Replies View RelatedWhats the key in editing macros to know that you are not getting compile errors and how do you know when your in VBA which statements are repetitive? Do you always go by the macro recorder?
View 8 Replies View RelatedIs there any code that will automatically enable macros on Auto_Open?
View 6 Replies View RelatedOn 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 RelatedI am trying to use the EnableMacros. zip script located here {url} But I am having a hard time making it work!
1. I have copied the module in EnableMacros.zip and imported it into my workbook.
2. I have placed identical code in my workbook's ThisWorkbook as is in the EnableMacros.zip's ThisWorkbook.
3. I have copied Sheet1 from EnableMacros.zip titled "Info Sheet". In MY workbook it is sheet7, but it is titled the "Info Sheet" in the EnableMacros.zip.
4. I have also copied sheet6 from EnableMacros.zip into my workbook and titled it "LoadScreen". In EnableMacros.zip the sheet is titled "Sheet1"
...
I have a couple spreadsheet Excel 2003 that I have to Email, I use a few macros with these spreadsheets. The Macros are kept in my personal workbook. When I Email these sheets does the end user see the warning enable macros or do they just see the sheet as is. I am mailing the whole workbook but macros are in my personal workbook. If they see the warning is it possible for me to Email so they will not have to contend with that?
View 3 Replies View RelatedWhenever 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?
1) Force user to enable macros.
2) keep three worksheets very hidden all the time.
3) passwrd protect vba
I have been using this code to force user to enable macros but this unhides all sheets other than "macros not enabled" sheet. I need three worksheets to remain very hidden all the time. How do I do this.
Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Macros Not Enabled").Visible = True
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = xlVeryHidden
Next
End Sub
Private Sub Workbook_Open()
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = True
Next
Worksheets("Macros Not Enabled").Visible = xlVeryHidden
End Sub
I have been looking at Dave's Script found in this post Determining if macros are enabled It works just like i want it too. But when I try and use it in my workbook with differnt sheet names it doesnt work. I have even tryed to rename the sheets in the example file and it stops working. My sheets are as follows:
Sheet1 (JI)
Sheet2 (GC)
Sheet3 (ET)
Sheet4 (SV)
Sheet5 (JB)
Sheet6 (P)
Sheet8 (Info Sheet) <----This is where the Notice to turn on Marcos is
Sheet9 (D)
I have the following code in Module2
Public bIsClosing As Boolean
Dim wsSheet As Worksheet
Sub HideAll()
Application. ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.CodeName = "Sheet8" Then
wsSheet.Visible = xlSheetVisible
Else
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet
Application.ScreenUpdating = True...................
I have a spreadsheet with 2 combo boxes - Combobox 1 enables a user to specify if they want to look at the data on the sheet by week or month - it has just 2 options - "select by week" or "select by month"
The list in combobox 2 changes based on the selection in combobox1 - so if you select "select by week" , a date list by week shows up.
Combobox 2 is supposed to run a macro which updates some backend pivot tables. there are 2 types of backend pivottable - weekly tables and monthly tables.
What i am not sure about is how to get the selections in combobox 2 to run either the weekly macro or the monthly macro based on if combobox1 is set to week or month ...?
I'm posting a workbook on our network that I want to enable users to go in and update. I have the workbook designed, with entry fields for the data they need to enter, and they then push "Submit" to submit the new data to a hidden tracking log within the workbook.
Many of the users will be complete Excel newbies, and although I haven't posted the workbook yet, I am trying to anticipate possible issues. The first that comes to mind is that the user opening the workbook may not have macros enabled in their version of Excel, and they will get a message, and can't perform the udpate. Is there a way to add a Workbook Open event that automatically enable macros in the workbook when they open it, even if it is for this workbook only?
What is the code to enable macros upon opening a workbook and avoid receiving the message "Security Warning-Macros have been disabled" in Excel 2007?
View 3 Replies View RelatedIn reference to my other topci here: Userform Error
In this workbook I have successfully made it act like a userform calculator, the user does not ever see the actual sheets in the workbook, their only interface is the Userform calculator I created.
This is exactly how I want it, they do not need to see the sheets, nor do I want them to.
This leads me only to my next problem... Some of the users obviously wont have Macros automatically enabled, what I am wondering is, is there a way to put the normal "Enable Macros" button which appears along the top of a macro containing workbook when opened, I want to put this button into a userform like display without showing the workbook at all.
So here the process I envision:
1. User double clicks the file
2. It opens and automatically goes invisable (like it does already)
3. If the user does not have macro enabled, a userform like box appears and says "Please enable Marcros to user" with a button below for "Enable" and "Exit"
4. If exit is pressed excel closes
5. If enabled is pressed Macros are enabled and then they will automatically see my calculator userform like normal.
As you see, at no point do I want them to see excel or a workbook, just userforms so its not like they are using excel at all.
I have searched around the net and found a few ideas to tackle this, mainly one that has a "Prompt" workbook that shows when macros are disabled, but that is not ideal for me. However perhaps I could just edit this "prompt" code thing for my situation?
Here is the prompt sheet code I talked about:
Code:
Option Explicit
Private Sub Workbook_Open()
With Application
[Code]....
How to use:
Open an Excel workbook Select Tools/Macro/Visual Basic Editor in the VBE window, select View/Project ExplorerSelect the This Workbook module Copy and paste the code above into this ModuleNow select File/Close and Return To Microsoft Excel Don't forget to save your changes...
8. Name one of your sheets "Prompt"
I have a VBA method that goes thru and calculates a bunch of stuff and puts this calculated info into a static data structure. This method is called 'setProjectInfo()'. This method is reliant on values on my worksheets, So as the user makes changes to the worksheet, I want setProjectInfo() to get called so it refreshes the data structure with updated information. I then have a bunch of helper methods to access different parts of this data structure. These helper methods are used in the formulae of a bunch of cells on a worksheet (ie '=getPercentComplete(period)', etc, etc).
This is all working well except for one thing. Sometimes the helper methods get called BEFORE setProjectInfo() gets called.. So, the helper methods return stale information. I can only get around this by manually recalculating the spreadsheet again. setProjectInfo() does enough stuff and takes a long enough time that I don't want to have to call it at the top of each helper method.
I've put the call to setProjectInfo() in a cell formula at the top of the my spreadsheet in hopes that Excel would know enough to call it first.. But that does not seem to work. Net, how can I get VBA to always call setProjectInfo() BEFORE it calls any other VBA methods?
I have made a vba program in excel 2003 that opens a worksheet using Workbooks.Open, and copies all the worksheets out into the program etc. The worksheets that I am opening have macros that automatically execute when the worksheet is opened (opening up some forms ). These macros are running when I open the file using VBA. How can I prevent this from happening. The automatically executed code in the workbook being opened is located in "this workbook" and can be seen below.
Private Sub Workbook_Open()
Dim CfileName As String
'Check Config sheet for template state
If Config.Cells(10, 3) = 1 Then
CustomizeDirInfo.Show
End If
If Config.Cells(10, 3) = 2 Then
GetConfData
GetGrpData
StdFileGen.Show
Else
Exit Sub
End If
End Sub
I am relying on macros for a lot of functionality and security.
Is there a way to stop a spreadsheet from being opened/accessed if a user disables macros at the security prompt?
I don't why the following code is not working. I am trying to force a date format when a user is trying to enter a date in anycell
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsDate(Target.Value) Then
Application.EnableEvents = False
new_date = Format(Target.Value, "dd/mm/yyyy")
Target.Value = new_date
'MsgBox new_date
Application.EnableEvents = True
End If
End Sub
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.
View 5 Replies View RelatedI'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)