Is there anyway to change the security settings in Excel 2007 using VB macros? I have tried to record the macro as I change the settings but nothings is recorded. Basically I have two workbooks, and I want to run one of the macros within workbook 1 from workbook 2. This is straight forward, however why I try to run it, excel says that the macros are disabled in the other workbook, but I know they are not, they are all enabled (bottom option)
I was hoping to find some way of fooling Excel into thinking the macros were enabled by changing the security settings (actually keeping them the same, but making excel think that i've changed them) before I try to the macro from the other sheet. I don't know if i've explained myself very well here, I hope I have, if not let me know and i'll try to go into more detail.
I am trying to use a shell command to dig into a specified directory (and ideally all sub-directories) and open every pdf file and re-save with security settings changed (adding a password to prevent printing). I can't seem to get the shell command to run from where it is:
Sub OpenPDFFilesAndSave() 'opens every pdf file in a directory Dim FSO As Object, Fld As Object, Fil As Object Dim MainFolderName As String, i As Integer Dim RetVal As Variant
Set FSO = CreateObject("Scripting.FileSystemObject") MainFolderName = "C:Test" Set Fld = FSO.GetFolder(MainFolderName) For Each Fil In Fld.Files i = i + 1 RetVal = Shell("C:Program FilesAdobeAcrobat 7.0 _&AcrobatAcrobat.exe MainFolderName & " " & Fil. Name") Next 'have to add save the file, and change the print settings but not figured that out yet End Sub
Although not a complete novice, I am fairly new to EXCEL/VBA. Is there a script that can be embedded into an EXCEL workbook that checks the Macro security of the PC, and if set to HIGH, shows a message that states that "Macro security is too high to open this workbook". With security set to high, Excel will open my w/ book, but not run any of my checks that are set in the module. So a "catch 22" really, I need the macros to run to that it is being opened on an authorised computer etc, but if security is set to HIGH, then they don't run, similarly, if I put a macro in to check the macro setting, it won't run unless the security is set to medium.
I have a sheet that will not let me use macros. I can record them, but when I go to use them it says that the Macro security level is too high. I change it to Medium or Low but it doesn't make a difference.
The same spreadsheet works with Macros on another computer, but not my main one.
The way i have been creating macros is by going to the tools menu.....macro....then..... record new macro.
I have a file which I have re-formatted using a macro as described above however because i receives files every month to do updates every time i open a new file and try to perform that same macro it either wont work or it wont format the correct rows.......is VBA the solution to this????
If I have a cell which has been validated and has a drop down list of 4 names in it. I need it to prompt the user that when they choose there name and select, a window pop up or similar window appears asking for a 4 digit(or similar) code.
This is to stop others using peoples names against jobs they have completed so there is accountability if there are issues with information.
For each line I need it to re-prompt the user to enter the 4 digit pin so it cannot be tampered with.
Can anyone help with sending through a form and VB code possibly which I can copy into my spread sheet or a method I can use!
when sending an email in code from Excel through Outlook, the following Microsoft Office Outlook dialog appears during code execution in Office releases since 2002:
A Program is trying to automatically send e-mail on your behalf. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No".
Two buttons: [Yes] [No]
Has anyone used or developed code that bypasses this prompt, and if so, would you please post an example of your code here. What I would like is a macro in VBA and / or API that you know from your own first-hand experience has worked for you, that emails an Excel attachment from Excel, using Outlook (not Outlook Express) as the email client, which bypasses the above prompt.....................
I have used the following Macro that deletes the Macro code. It then saves the file and quits excel.
************************************* Sub removeAllCode() Dim awi Dim awcl As Integer Dim count As Integer Dim i As Integer 'loop counter On Error Resume Next count = ActiveWorkbook.VBProject.VBComponents.count For i = 1 To count Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i) awcl = awi.CodeModule.CountOfLines awi.CodeModule.DeleteLines 1, awcl Next i Set awi = Nothing ThisWorkbook.Save Application.Quit End Sub *************************************
However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists
If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.
I have tried various examples listed in different forums/sites but nothing works.
I have a spreadsheet that I regularly have to clear the data and start over on, so i recorded a macro for clearing this rather long sheet with only one cllick.
My problem is that i am not the only person who will use this sheet, if They click the control button by mistake they will clear their work and you cannot undo it. I was wondering if i could make it ask "are you sure" kind of like the save option when closing an unsaved document.
I have a large file of data and the data looks like this repetitively, however, there are also useless data. But I figure out that the repeat data looks the same compare to the useless data. I need to extract the data that I only need. Is there a way to set a macro to search for a String from the beginning of the excel file, when the string is found, it will keep the 2 rows above and 56 row below it, then loop.
Here is an example of it:
A1 USELESS DATA A2 USELESS DATA A3 USELESS DATA A4 USELESS DATA A5 GOOD DATA
The macro will start from A1 then go down till find STRING, then will keep 2 row above it which is A5+56 row below it which is A63.
Then continue at A64, then find STRING at A66 and do the same which is copy A64 (2 row above)+till A122 (which is 56 row below), then continue.
Since A123 to A155 does have any STRING, they will all be deleted..
I've written code into excel that sends workbooks to email recipients. This works fine except for use on one friends computer. Of course it's the friend that needed the code in the first place. What happens is that when the macro attempts to mail a spreadsheet an outlook popup occurrs warning that a macro is attempting to email a message. And so nothing gets emailed out!
I have 4 worksheets in a workbook: “choose security”, “download”, “MC” and “Lending”,
In the choose security” worksheet in CELL A2 is an HEX code, i.e. stock code.
2. In MC worksheet is raw data of all holdings related to various p’folios and securities, the information we need from this worksheet is,
a) p’folios, which are in column A, b) Holdings which is Col B c) Security code i.e. stock code which is in Col D
I need a macro or a formula to look at what security code is in “choose security” worksheet CELL A2, then look for this security in worksheet “MC” Col D, once matched, copy the data for this security to worksheet “download” and paste in CELL A3, I only need p’folios and holdings to be copied to “download” so data in Col A and B.
Once data has been copied to worksheet “download”, I then need another macro or formula to repeat step 2, but this time the macro will be looking at worksheet “Lending”,
a) P’folios, which are in Col B b) Security code is in Col D c) Holdings is in Col H
Once the macro or formula has found a match of security code, it will then copy the data from Col B and H to download worksheet CELL E3.
I have created a workbook. That has 35 worksheets within it. I have created a series of buttons and put in a macro code so that when the user clicks on the button it changes the active worksheet to what they clicked on.
So basically when they click button b it changes to worksheet b and if they click button c it changes to worksheet c, ETC.
The problem I have is that since it is a macro it works fine on my computer but when it is sent to other users, because of security settings on their pcs it will disable the macro [thus the buttons will not work or show up]. I have created a digital trust certificate for it. However it is alot of steps to install this for the basic user. Is there a way to create a button that will do what I want without using a macro code. In that it will be able to go to many different users and wont have to worry about the buttons not working because of security settings?
Right now i have two worksheets: "900 - Reel" "OverRides"
On the "900 - Reel" sheet i have a userform ("userform4") open when double clicking a specific cell. Upon opening it uses the following code to go to sheet "OverRides" to find two specific values and imputs them into "textbox1" and "textbox2" on "userform4"
I have a list of worksheet names in a combobox, when selected using the drop down arrow it takes you to that worksheet. The problem is say I select "ABC" from the list and it takes me to the "ABC" worksheet but when I go back to the summary page the "ABC" is already selected in the drop down box and I cannot click it any more. Is there anyway to reset the combo box so that it goes back to the first selection of the list?
Private Sub AFISGBox_Change() Dim strSheet As String If AFISGBox.ListIndex > -1 Then strSheet = AFISGBox Sheets(strSheet).Select End If End Sub
is it possible through vba code to determine the macro security level of Excel 2007 and inform the user. I tried the code below , it is executed when the workbook is opened, but it doesn't work.The code below is executed only when the macro security level is "Low". My goal is at least to inform , with a msgbox function, to change manually the security level.
Dim secAutomation As MsoAutomationSecurity Dim zLevel As String secAutomation = Application.AutomationSecurity Select Case secAutomation
I'm working on a mac and have a macro that will go through all the sheets in my workbook and save them as PDFs to a specific location.
Sub CreatePDF() For sh = 19 To Sheets.Count Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "/Users/mikemahoney/Desktop/Publisher Payables/Statements/June 2014" & Sheets(sh).Name & "June 2014 Revenue Share Statement" & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False Next End Sub
The part in bold is obviously where I am saving the PDFs but it is also being included in the naming of the file. Need replacing the red text in the naming of the file with the contents of cell B9? I would still like to save the PDF to "/Users/mikemahoney/Desktop/Publisher Payables/Statements/June 2014" I just don't want the file-path to be included in the name of the file.
Also, this code seems to run into errors if a sheet is hidden, hence starting at sheet 19. Any way to tweak this code to skip over hidden sheets?
I just wonder if we can have the code to exit if G2 returns an even number, as 2, 4, 6 etc?
If ActiveSheet.Index 1 Then Exit Sub Application.ScreenUpdating = False
With Sheets(1) .Unprotect "mypsw" .Range("G2").Value = ActiveSheet.ListBoxes(Application.Caller).Value With .ChartObjects("Chart 2").Chart.Axes(xlValue) If VBA.VarType(Sheets(1).Range("D1").Value) = VBA.vbError Then Exit Sub If VBA.VarType(Sheets(1).Range("E1").Value) = VBA.vbError Then Exit Sub
I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.
I've inserted a module and produced this code;
Sub ClearContents() Application.Calculation = xlCalculationManual Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents Application.Calculation = xlCalculationAutomatic End Sub
Because of all the 3 summary page formulas the code is taking a few seconds to run.
I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.
I'm trying to find out if there is a way to call the security alert to enable a macro. Let me explain...I was working on a sheet with a macro that I hadn't enabled yet. After working for awhile the option to enable it was gone and I found that I had to close the fine and re-open to enable the macro. I'm wondering if there is a ribbon or short cut icon to put in my access toolbar that will give me the option to enable/disable the macro. Seems strange that in 2003 I could to this, but in 2007 I have to close the file.