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

XL2003 List Vs. XL2007 Tables

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

How Does This MACROS Work

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

Macros To Work In Certain Cells

May 22, 2014

Is it possible to only allow a macro to work in certain cell?

View 1 Replies View Related

Macros That Only Work On One Sheet?

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

Macros Won't Work With Decimals

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

Locking Macros In Spreadsheets At Work

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

Macros Dont Work Even On Low Security

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

A Few Macros Dont Work Properly With Personal.xls

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

Macros Wont Work When I Protect My Worksheet

Sep 14, 2009

I have the follwing macro that is used to hide certain rows: ......

View 11 Replies View Related

Excel 2007 :: Creating Workbooks With Macros That Work On Other PCs

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

Excel 2010 :: Macros No Longer Work When Workbook Shared

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

If Macro Is Disabled: Place A Message On The Sheet Saying This Will Only Work With Macros Enabled

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

Requires To Distinguish Between Xl2007 And Previous Version

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

Colour Fill Cell Range In (Conditional Formatting XL2007)

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

XL2003 Replacing Multiple Codes Within Formula's

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

Trying To Create A Conditional Product Function In XL2003

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

Automatic Link Update WITHOUT Prompt When File Opened Not Working (XL2003)

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

Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

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

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

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

Encourage Enabling Of Macros: Disable Macros When Opening Then The Worksheet Menu Bar And Other Command Bars Are Still Available

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

Enable/Disable Macros When No Macros Are Present

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

How To Get A Macro To Work With Any Work Book Name

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

Make To Display (0), Instead Of (-0,02) When I Just Do Other Work Than Cad Work

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

How To Delete Another WorkBook Macros Using Macros

Jul 23, 2005

What is the commands or script for deleting a macro automatically using
another workbook macro.

View 9 Replies View Related

Hiding Macros From The Run Macros List

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

Copying A Certain Range In A Work Sheet To Another Existing Work Sheet Using VBA

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

Listing Required

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

Object Required

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

Only Print What Is Required

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







Copyrights 2005-15 www.BigResource.com, All rights reserved