What Changes Are Required For XL2003 Macros To Work With XL2007 Wbks
Dec 11, 2009
1. 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.
View 9 Replies
ADVERTISEMENT
Jan 14, 2009
In 2003 version, user could create a table of data, then go to Data, List and add more data at a later date. If other information was placed directly below the table, Excel would insert new rows when inserted table data approached the lower rows of information. It would not erase the information.
In Excel 2007, the List function no longer exists but was replaced by Tables. However, I cannot get the table to insert new blank rows when it approaches the additional information below the table.
View 9 Replies
View Related
Oct 23, 2009
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.
View 9 Replies
View Related
May 22, 2014
Is it possible to only allow a macro to work in certain cell?
View 1 Replies
View Related
Apr 2, 2009
If 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.
View 3 Replies
View Related
May 8, 2009
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.
View 14 Replies
View Related
Jun 27, 2014
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
View 8 Replies
View Related
Aug 21, 2009
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.
View 13 Replies
View Related
Jun 18, 2008
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.
View 14 Replies
View Related
Sep 14, 2009
I have the follwing macro that is used to hide certain rows: ......
View 11 Replies
View Related
Apr 20, 2012
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 Related
Aug 22, 2012
Using 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] ..........
View 9 Replies
View Related
Aug 17, 2006
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 Related
Jun 4, 2009
I know I have asked this question before and in that occasion for the safety copy creation I got a good solution, but now I have another issue which requires to distinguish between xl2007 and previous excels. Based on advice I have tried this:
View 3 Replies
View Related
Oct 27, 2007
way to highlight selected cells if one of the selected cells equals a certain value eg
A B C D E
1 X X X Y X
2 X X X X X
3 X X X Y X
I select cells A1-E3 and if column D = "Y" then colour fill the row from A to E
View 9 Replies
View Related
May 30, 2008
I have a column of 3 letter codes (Righthand side) all differant ie BHP, CBA, NAB, WBC etc. (2000 of them) On the left I have the formula ie =MisData!VOLUME!BHP.ASX
Each formula should have the relevant code within it such as the BHP in the formula on the 2nd row should be CBA, the third formula should have NAB in it. right down the whole 2000 codes without doing it one by one. Now using the find & replace tool to do it one ny one
=MisData!VOLUME!BHP.ASX BHP
=MisData!VOLUME!BHP.ASX CBA
=MisData!VOLUME!BHP.ASX NAB
=MisData!VOLUME!BHP.ASX WBC
View 9 Replies
View Related
Sep 4, 2008
I'm trying to do:
Column A is full of daily consecutive dates, Columns B to H are full of numbers.
I'd like to create function that accepts 2 dates and a column reference as input like this:
function(date1, date2, columnnumber)
And as output it gives the product of all the numbers in that referenced column between the two dates.
View 9 Replies
View Related
Jul 6, 2006
Despite setting the "Edit/Links/Startup Prompt/Don't display the alert and update links" option numerous times, my workbook still prompts me to update links every time I open it. The option seems to be set okay (it is preset whenever I go into the "Edit/Links/Startup Prompt" dialog), but it doesn't seem to affect the workbook's startup behaviour. The workbook contains a ComboBox control that is initialized with customer names from another workbook, which is included in the References for this main workbook.
I am using Excel 2003 (from Office Pro 2003) under Windows XP (SP1). I believe this used to work without the prompt when I was working on this app last fall (I'm not sure, as my memory of specific behaviours back that far is fuzzy). However, it has been persistently prompting ever since I started working on this app again this spring/summer. Was there perhaps a bad fix to Office 2003 (that I automatically applied) that broke this feature?
View 5 Replies
View Related
Jan 11, 2009
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies
View Related
Mar 3, 2009
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
View 9 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Jul 28, 2008
I 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?
View 9 Replies
View Related
Aug 24, 2009
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
View 8 Replies
View Related
Feb 13, 2010
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies
View Related
Jul 23, 2005
What is the commands or script for deleting a macro automatically using
another workbook macro.
View 9 Replies
View Related
Jul 14, 2008
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies
View Related
Dec 29, 2009
I have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
View 9 Replies
View Related
Jun 17, 2009
I have been given a worksheet which has 5000 rows spread along 13 columns.
There are about 200 cells that I am interested in from this sheet, these cells being the subtotals of the cells above them.
I have extracted the individual cells containing the subtotal values to a new, empty column but they are spread down this column with blank cells in between.
Is there a function / formula to get rid of the 4800 blank cells so that the data I need is compressed into a stack of 200 cells all filled with data?
View 14 Replies
View Related
Sep 27, 2007
i am having alittle trouble with this line of coding.
With ActiveWorkbook
.Sheets("Hidden Data").Cells(rngFound.Row, 9) = Sheets("Data").Range("L18")
.Sheets("Hidden Data").Cells(rngFound.Row, 12) = Sheets("Data").Range("l20")
.Sheets("Hidden Data").Cells(rngFound.Row, 15) = Sheets("Data").Range("l22")
.Sheets("Hidden Data").Cells(rngFound.Row, 18) = Sheets("Data").Range("l24")
.Sheets("Hidden Data").Cells(rngFound.Row, 21) = Sheets("Data").Range("l26")
.Sheets("Hidden Data").Cells(rngFound.Row, 24) = Sheets("Data").Range("l28")
End With
with the line "Sheets("Hidden Data").cells(rngfound.row, 9) = sheets("data").range("l18")" the error messages states Object Required, but i am not sure what this is trying to say.
any idea's or thoughts would be greatly appreciated.
at the top of the coding i have listed "Public rngFound"
View 9 Replies
View Related
Nov 13, 2009
I am creating a training matrix that will be used in a number of locations. Some only have a few staff members whilst others have lots. I want each location to print a list on a formatted sheet but I want to set it up so that the locations with fewer people don't end up with lots of blank lines.
View 9 Replies
View Related