Macro For Different Versions
May 4, 2009
I have been working on this spreadsheet off and on for a while now. I have a macro that works in Excel 2007 and a different one that works in 2003 (thanks to the help of Mr.Excel.) My problem is, the people who will be using this spreadsheet all have various versions of Excel. Is there a way to script this macro to work for all versions, or do I have to have a different script for each version of Excel? The one I am currently in need of is a script for 2002. See below for the script for the 2007 version. If there isn't a way to make a "one for all" solution, some assistance on a script that would work on 2002 would be great! The section in red is the section that returns the error when people try to run it with earlier versions of Excel.
ActiveWindow.SmallScroll Down:=-9
Columns("B:G").Select
Selection.Copy
Columns("B:G").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("L:N").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("N11").Select
View 9 Replies
ADVERTISEMENT
Jun 3, 2009
I have some codes that require different values when running under xl2003 vs. when running under xl2007.
View 11 Replies
View Related
Nov 23, 2008
ive got my highly expensive office enterprise 2007 disk
what is the best way of installing this so i can still use 97 and 2007. any tips?
i wont bother with outlook but i'd like to try the rest ,mind you onenote,infopath and groove are a complete mystery!
View 11 Replies
View Related
Feb 13, 2009
I'm running Excel 2007 with Windows Vista. I also have Excel 97, which I was told would not run with Vista. I've since discovered that many programs that I was told wouldn't run with Vista seem to work fine.
Is it possible to install Office 97 on the same machine with Office 2007?
View 9 Replies
View Related
Aug 26, 2008
I may be creating a small Excel-based VBA application for a client on a semi-formal basis. I personally use Windows XP with Office XP installed, but I'm concerned about the possibility of serious differences between versions of Windows and Excel that may cause my code to fail on other machines.
I don't think I need to cater to any version of Excel BEFORE that in Office XP, but I probably do need to cater for versions after it. That's Office 2003, Office 2007 and Office for Vista,
View 7 Replies
View Related
Mar 19, 2007
I'm looking for is ComplyXL and that costs $300 to buy. So, as a poor grad student, I'm looking for an alternative solution - either a freeware program or a hassle-free VBA code.
So here's what I need the code or program to do: When I ask excel to open a pre-existing file, I'd like it to automatically rename (& compress?) the last used version of the file & save that to a filename-specific archive somewhere (hidden?), before opening the file as requested. Alternatively, I suppose that a mirror copy of each 'just used' file could be created & saved to an archive folder whenever the used file is saved & closed.
--Note: to avoid making copies of EVERY workbook I ever use, I suppose there should also be a button in the toolbar or a file-associated setting that lets me identify which files I want this task to apply to...
Basically I'm looking to have excel automatically save versions of certain key files (like Word's 'Versioning' feature) without having to remember to 'Save As', without the versions taking up much space, and without having to store the older file versions in the same place as the most recent one.
And yes, I know that a similar problem has already been posted & solved here, but they didn't include a way to choose which files used versioning nor (if it's possible) a way to compress the archived versions to save space (maybe by adding it into a WinRAR archive somewhere?).
View 11 Replies
View Related
May 22, 2009
I have made a file that works perfectly in excel 2007, but when I send it to a client it doesn't work as they have 2003.
View 12 Replies
View Related
Jan 4, 2010
The document shows a table on the left that puts the data from the table on the right into order by total (and if same, alphebetical by name). However the formula used to achive this is not compatable with previous versions of excel. Need alternative function that will bring the same outcome but work with pre 2007 versions (ie .xls file extention).
View 3 Replies
View Related
Apr 8, 2008
I am changing the file type association for .XLS to
C:Program FilesMicrosoft OfficeOffice11excel.exe
I'm using "Change" and "browse" to set it to that exact file. However it still starts version 12 (XL2007) which is dual installed with version 11 (XL2003).
I was successful in creating a shortcut that opens version 11, despite Microsoft's apparent attempt to thwart that; now I need to fix the file association. (Try to directly edit the Office links in your XP Start Menu - how the target is grayed out?
View 9 Replies
View Related
May 30, 2009
I have a dir with over 150.000 files in it, especially pdf, doc and xls. Now I'm looking for a script or program that will automatically search the entire directory and look for old xls files, open them and save them in a new(er) version. I'm working with a software that needs to use these XLS files in my directory but it can't use the old xls-versions. It's an impossible job to search by hand for the old versions and put them in a new(er) version.
View 11 Replies
View Related
Jul 2, 2013
When I attempt to send a spreadsheet using a German version of Excel, the following formula does NOT work properly.
=ZELLE("adresse",C14)
The function gets translated into =CELL("adresse", C14), and what I need is, obviously, = CELL("address",C14). The argument does not get translated.
View 1 Replies
View Related
Jul 17, 2009
Compatibility mode, in 2007, is frustrating me like crazy. I thought it was supposed to 'limit' functionality to what was in 2003. But what it seems to be doing is letting me use features that only work in 2007and then tell me when I save that 'hey, these won't work after you save'. Ugh! I can't even pick a highlight color without getting a warning and wondering what it will look like to 2003 users!
Is there a 'theme' or anything that will truly limit the features I use to those available to 2003 users?
View 5 Replies
View Related
Feb 22, 2007
I have to run a daily report from winxp or win2k and need to change the host files a few times for access.
I tried to use %systemroot% in the file path, but for some reason vba doesn't like it.
Here's the snippet I'm using for copying over the host file based on user choice: (the thing works if I change to the actual system root path (C: etc...)
Private Sub CommandButton1_Click()
Select Case ComboBox1
Case "- Choose -"
MsgBox ("This is not a choice")
Case "BTR"
View 9 Replies
View Related
Mar 2, 2008
I've found a link on OzGrid "How to remove the (X) CloseButton from Userforms" but I can't seem to get it to work with Excel 2003. The code looks like it was written for Excel '95, '97 and 2000.
View 6 Replies
View Related
Nov 27, 2009
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
xlApp.Application.DisplayAlerts = False
xlApp.Workbooks.Open ("somepathWebquery.xlsm")
xlApp.Application.Run "'some macro"
The only problem is, the web query will only work properly if it is in Excel 2007 (2003 will parse the table in a way that is not acceptable). However, when I tried to run the script, the stubborn Excel 2003 will show up. I even tried to uninstall the Office Compatibility Pack and the stubborn Windows will only run Excel 2003 and ask me to download the Office Compatibility Pack instead of giving me Excel 2007.
I tried to open it up using Open With...and it did not work, I even tried to regserver and unregserver and that did not work as well. Somehow Windows thought the only Excel I have is Excel 2003.
Is there anyway I could change the VB Script so that it would force Excel 2007 to open?
View 6 Replies
View Related
Apr 30, 2008
Add Multiple Buttons That Select Row Of Housing Cell. One of my Macro's places a button on a sheet using Top/Left;
Set sbut = Sheets("Listings").Shapes.AddFormControl(xlButtonControl, _
Range("J" & count).Left, Range("J" & count).Top, 50, 12)
When I try to find the row of the clicked button using the following code;
Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate
The code behaves differently in Excel 2002 and 2007. In 2002 it returns the row the button sits on, but 2007 returns the cell above the button. This could be because the workbook is an XLS running in Excel 2007 in compatibility mode? Is there a way to "nudge" the button down a little after it has been placed using .top/.left ? Or is the only way around this something like;
Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate
count = ActiveCell.Row
If Application.Version >= 12 Then
count = count + 1
End If
Is this a bug, or something that I am doing wrong ? The workbook needs to be a .xls to ensure that it works in older versions of Excel.
View 4 Replies
View Related
Feb 28, 2014
I would like to automate the conversion of multiple versions of company names contained in cells to a single company name. i.e. XYZ Inc., XYZ Co., XYZ to all cells XYZ. My data file contains numerous versions of company names. I want them to all be the same company name so sorting data is easier. I now do this manually and it is getting tedious and time consuming. Can I build a table with the different versions that then chamges the cells to a single version that I choose?
View 1 Replies
View Related
Jul 25, 2006
i downloaded the beta version of the office 2007 for testing. can i install this version alongside my existing office 2003 version. i want to have the 2 versions installed on my computer so that i can choose between the versions when opening a file.
View 3 Replies
View Related
Oct 16, 2009
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
View 10 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
Jan 26, 2009
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
View 14 Replies
View Related
Feb 1, 2009
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
View 5 Replies
View Related
Sep 17, 2009
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
View 13 Replies
View Related
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
Mar 4, 2014
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
View 1 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Mar 16, 2007
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View 2 Replies
View Related
