My dad wrote me a VB programme to save me time entering repetitive data into an excel template for my business. At first it didnt work untilll i reduced security to low. Sice then I fried my computer and have had to reformat it with a diffirent version of windows, vista service pack 3. Now even on low security it doesnt work.
The little button in the worksheet to make the macro happen isnt visible or doesnt exist.
I wondered if I could pick your brains and hopefully get some answers to my problem. I have recently been using the Personal.xls worksheet to make all my Macros available to all open workbooks so that I don't have to cut and paste Macros each time I wanted to use them.
Now I know you sometimes have to make certain adjustments to Macros in order to make them work globally. I wondered if I could find out how to amend the codes below to make them work properly. Thanks.
The first code Macro below deletes all worksheets in a workbook except for the currently active sheet. However it no longer works.
I was playing around with Macros on an excel file. I created a few and ran them. In the end, I didn't like the way they ran, so I deleted them all. However since I ran them in the excel file, every time I open the file it gives me a warning saying
"Macros have been disabled."
Is there any way to remove the possible Macros ties/links from my file? I know I can disable the warning, but if I just want to remove the Macros ties to my file.
I would like the security warning not to pop up when the workbook is opened. I have been able to turn it off for myself but I had someone else test it and each time it's opened the security warning pops up with the disable, enable messages. Is there a way to turn this off permanently so not matter who opens it it won't pop up?
View 9 Replies View Relatedi want my application which i build though VBA in excel run on every computer without prompting for any Macros security message so is there any solution?
View 9 Replies View RelatedI just loaded Excel 2007, and then opened a workbook I wrote that made pretty extensive use of macros and code. I knew enough to first set up Excel via the "Trust Center" to allow ALL macros to run, against MS's, or more likely against MS's lawyer's, recommendations. btw, does anyone have a clue as to what "Trust access to the VBA project module" is all about? Apparently MS doesn't, for I have searched Help and the KB for it in vain. Anyway, I brought the old xls workbook in, then saved it as a macro-enabled spreadsheet, .xlsm. Then I open it, and uhg, a most distasteful surprise. Even though I specifically went in and set Excel to run all freaking macros, someone at MS has decided I am too stoooopid to make this decision, and all macros have been automatically disabled. OK, so let us click where it says "Options" on this warning bar.
A window pops up telling me that this file contains encrypted macros that have been disabled...". Do a search on that, again, MS, MS kb, even Google, and you are not going to find it (In quotes). There is ONE freaking option with a radio button now, telling me "Help protect me from unknown content" - again, I try a Google search, an MS search and an MS kb search... nothing directly addressing it; but I do the logical intuitive thing - there is a selected radio button there, so click it to unselect it. Click the idiot thing until the cows come home, darn it, it won't do a thing! I even tried an Alt-p as they have the "p" underlined - all that does is select the statement, it remains selected, tap the space bar, hit the space bar, stand on the space bar... First they disable all the macros. OK, I can live with going in and fixing that, but then deciding that I am too dumb to make that choice so don't allow me to turn them on in my own freaking code? Yes, it has been several hours and I am still pissed off beyond belief.
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'm using Excel 2003 and recently when I open a file, it prompts out a Security Warning message about Marcros which at the end contains 3 choices: Disable Macros, Enable Macros, and More Info. I then click Enable Macros to open the file. Once open, I go to Tools > Marco > Run Marco (Alt+F8). A Macro box prompt out but the Macro list is empty. I try different choice inside the pull down menu "Macros in" but the Macro list is still empty.
Where can I find out the macro which triggers the warning message of Security Warning when opening the file? And how can I delete the macro if found useless.
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 recently took a macros my friend did and adjusted it so it would work for my problem.
It works fine but there is one thing i don't understand about it.
The initial problem was that i needed a macros that could copy a range and paste it 1 cell space to the left and then delete the last cell of that range, so it would look something like this:
FROM THIS:
HIJKLM2NIVEL
3
21
TO THIS:
HIJKLM2NIVEL3
21
so this is the macros i am using which works perfectly:
Range("H1").Select
Selection.End(xlDown).Select
Selection.Offset(0, 2).Select
RC = Cells(Columns.Count, ActiveCell.Row).End(xlUp).Column
Range(ActiveCell, Cells(RC, ActiveCell.Row + 11)).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 4).Select
Selection.ClearContents
What i don't understand is why i have to use the +11 in order for it to move 4 spaces to the right.
Is it possible to only allow a macro to work in certain cell?
View 1 Replies View RelatedIf i have a macro, is it possible to have that macro work on a specific sheet and not the whole work book.
I.e I have two sheets one where you can insert a line and other where you cant insert a line, at the moment i can insert in both sheets.
I've been trying to create an excel macro to calculate shortest route path using floyd algorithm..
Problem is, i can't figure out why this macro won't work with decimal numbers..
I'm pretty sure there's a VERY simple explanation for this..
Probably has something to do with excel's formating
See the attachment and press Run button, it won't produce the correct result, but if if you multiply all the numbers in "input" sheet by 100.
I need to lock the macros in spreadsheets at work.
If I go to Developer>Macros I can edit any of the macros.
So to protect them I went to Visual Basic>Tools>VBA Project
What I don't understand is that there is already a password there.
How it is possible that I can edit any of the macros under Developer>Macros when there is a password which I haven't entered under Visual Basic>Tools>VBA Project
I have the follwing macro that is used to hide certain rows: ......
View 11 Replies View Related1. I've written a few macros using MSExcel 2003 VBA that I've tested successfully on Office 2007, but with 2003 workbooks only. Since the filename extensions for Excel 2007 workbooks are different from those for 2003, what changes are required in my vba code to ensure that it will work with 2007 workbooks too?
2. Will changes to vba code be necessary even for changes in the operating system (e.g. Vista / Windows 7)? I have worked with my vba codes on Win XP only.
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies View RelatedUsing Excel 2010.
I've created a spreadsheet at work that has two summary tabs which contain hyperlinks to around 30 separate sheet tabs.
On each sheet tab there is a list of unique values in column A (and other information relating to each value in columns B to D which are repeated for more than one unique value). In column E, users enter a test script name against each unique value they wish to 'reserve', and the macro picks out the unique test script names and via the COUNTIF formula counts the frequency of each test script name for each of the different values in column B.
My problem is that the macro seems to work fine if the workbook is not shared, but errors if the workbook is saved as shared. The error is 'Run time error 1004 - Unable to select the MergeCells property of the Range class'.
Here is the macro code:
Sub Get_Policies_Per_Script(updCol As Long, ShtName As String)
Dim rowctr As Long
Dim tgtrow As Long
Const ppsformula As String = "=COUNTIFS($A$3:$A$65000,I$24,$E$3:$E$65000,$G"
If updCol = 5 Then 'test name column has been modified
[Code] ..........
When you enable macros the sheets 2, 3, 4 ect are visible but if you disable macros, you only see sheet 1 and and you can place a message on the sheet saying this will only work with macros enabled.
View 2 Replies View RelatedI 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.
I have a VLOOKUP formula and if it looks and does not find a match it will either put a "0" or a N/A. Is there something I can add to it to leave the cell blank if it does not find a match?
View 9 Replies View RelatedBasically I have a =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) And sometimes it doesnt find the number its looking up, so theres no data to display, so it just shows #N/A is there anyway I can make the =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) display "not found" if it doesnt find anything?
also sometimes if it does find the number its looking up, and the cell next to it is blank, it just displays 0. I could filter it.. and then just replace all the 0 and #N/A with not found, but im just wondering if i could do it in the code too.
I've developed a VBA application for excel in my english version. Now I sent to the client, wich is dutch and use dutch office. they say it gives errors at simple lines like TextBox1.Left=100. It seems there is no Left property. I installed their office but Left is there for me. I suspect their configuration have some version of VBA with translated code language... like using Links instead Left... wich is same thing for dutch. What could cause VBA translation of properties so I can have same configuration as they do?
View 4 Replies View RelatedI have rows of data that repeats every now and again and codes next to them What I need to do is have same row of data (without it repeating) and relevant codes next to these across the page. Tried doing Pivot table and failed. Example and proposed format enclosed.
View 4 Replies View RelatedI am using Excel 2007. I have read that if you open an excel 2003 document, that has external links, the links will be recacluated automatically. How do I change the settings in '07 so that the links are not recalculated when I open a 2003 excel file?
View 9 Replies View RelatedI have a spreadsheet with rows as budget line item and columns for the months they occur.
I have a cell on each row (in column B) displaying the total of that row for all the months. (columns C to Z)
I want to be able to hide certain months (columns), and have the total not include those hidden columns; but the formula for the totaling cell ( "subtotal(109,C3:Z3)" ) appears to continue counting all the columsn, even the hidden ones
I am making a spreadsheet and would like the "placement" tab to look at the "years in position" column and go to another tab and find the corresponding data then put that information into the cell that the formula is in. I have concatenated and the value carries out to about 6 decimal and I cant change the formating. I would like it to display as a currency with 2 decimal places.
If you look at the placement tab cell H7 you can see what I am trying to do. If you change the value in d7 to a value that is on tab 06-09 step plan (b9-b23) then the value is put in other wise noting is returned.
=SUMIF('[Weekly Sheets.xlsx]W1'!$A$8:$A$10000,$C$1,'[Weekly Sheets.xlsx]W1'!$K$8:$K$10000). It works fine but I need it to also lookup cell B8 if B8 has text named "ABS" dont count whats in cell K8
View 2 Replies View RelatedThe first one works fine but the "Description" and "Customer" paste over the top of each other and dont find the empty cell. im sure im missing something!
This submits the following details into the "Stored" table. It also finds an empty cell.
I have spread sheet that adds meter readings. As you enter the reading it subtracts from previous month's and gives you net value. But this always shows a negative value in the following month until you enter the current reading.
Also I would like to be able to total these net values as a running total. Is there a simple way to not show the negative values and or use those when getting the running total?