Downloads

Excel versions

These files should all work in all recent versions of Excel, unless stated otherwise.

Blocked files problem

If you open a downloaded file but the VBA code doesn't work, here's how to fix that issue: Excel: Add-ins do not load

Excel add-ins and workbooks

RefTreeAnalyser

Better formula precedents and dependents finding. Ever had to work out the logic of other people's Excel files? Ever had to untie the spaghetti-knots of a large Excel workbook's formulas? Then you know what a nightmare this can be!

Now there is RefTreeAnalyser!

Name Manager

Name Manager add-in for Excel

Name Manager An excellent utility to manage defined names in your workbooks.

FlexFind

Flexfind eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more.

Autosafe

Autosafe.zip (Build 151, 16 Jan 2023, downloaded: 60.844 times)

New in Autosafe: bugfix related to the Windows Explorer preview pane.

Autosafe tries to overcome some limitations of the built-in Autorecover feature. This utility creates copies of open workbooks at regular intervals in a separate (user-selectable) directory. It does not overwrite the master file(s), that is up to the user to do, using normal methods. As soon as a workbook is closed, the backup copy is deleted from the backup directory. If an abnormal termination of Excel occurs, the backup copies remain on disk, and Autosafe finds them the next time Excel is started and presents recovery options to the user.
Autosafe comes with an autoupdate function which checks for updates every week.
Includes the following languages: English, Dansk, Deutsch, Español, Français, Indonesia, Italiano, Nederlands, Norsk, Hrvatski, Slovenščina, Korean, Русский (Russian), Lithuanian and Hungarian.

Autosafe does not work on a Mac.

GoBack

GoBack.zip (June 6, 2022, downloaded: 18.111 times) Version 1.0 build 013.

Sometimes one has to edit a large workbook, with many worksheets. This tiny utility keeps a record of the ranges you have visited and gives you the opportunity to return to previous selections using two hot keys: control-alt-p to go to a previous selection and control-alt-n to go to the next. It also creates a toolbar with a dropdown to select a previous selection and to disable/enable the utility.

Now updated with ribbon user interface.

Find My Cursor

Find out where the active cell is

FindMyCursor.zip (6 Jan 2022, downloaded: 2.619 times) build 001.This tool is similar to the one below, except that it does not make any changes to your files, it simply helps you to find where the active cell is. Simply install the tool and press control+shift+H to get an animation which draws your attention to the right area

Cell Sizer

CellSizer.zip (June 15, 2023, downloaded: 844 times) Build 002.

A small and simple tool. It works like this:

Use arrow keys to change row heights and column widths
Hit control+z to undo

Follow Cell Pointer

FollowCellPointer add-in for Excel

FollowCellPointer.zip (14 Dec 2022, downloaded: 43.999 times) Version 1.0 build 012.

A small tool which follows your cell pointer by placing two arrows on top of your sheet pointing to the active cell. Does not change your formatting and has an option to preserve the undo stack.

Sheet Tools

SheetTools add-in for Excel

SheetTools.zip (24 Jan 2022, downloaded: 10.623 times) Version 1.0 build 001.

This is the file that accompanies my article on how to build an Excel add-in

Table Tools

TableTools add-in for Excel

TableTools.zip (October 24, 2022, downloaded: 9.369 times) Version 1.0 build 015.

This is a small utility to make your life with tables a little easier. See this article

Slicers

Slicers.zip (29 Aug 2019, downloaded: 5.540 times).

This download contains files accompanying this article: Synchronising Slicers

Compare Two Tables

Compare2Tables.zip (Nov 15, 2021, downloaded: 5.149 times) Version 1.0 build 014.

A tool which compares two tables. The tool produces three new tables. Two tables containing the records which are in table 1 and not in table 2 and vice versa and one which lists all records which have been modified. Adds a button to the Data tab of the Excel ribbon.

Spreadsheet Model Template

EUDA-template-EN.xlsm (8 Feb 2021, downloaded: 5.845 times)

EUDA-template-NL.xlsm (8 Feb 2021, downloaded: 2.998 times)

This template makes starting a new spreadsheet model easier as it already contains some functionality I think every model should have.

As discussed in this article

Validation dependent lists

DependentValidationUsingDA.xlsm (22 June 2020, downloaded: 5.056 times)

This demonstrates how to create dependent validation lists using dymanic array functions in Excel.

Close All Workbooks And Quit

CloseAllWorkbooksAndQuitExcel.zip (01 Oct 2015, downloaded: 7.057 times) Version 1.0 build 001.

With the new MDI interface introduced with Excel 2013, closing all workbooks has become unnecessary difficult. This little addin adds a 'Quit Excel' button to your File menu that will close all open workbooks and quit Excel. It also features a Short-cut key: control+q

Header Footer

HeaderFooter.zip (24 August 2018, downloaded: 19.027 times) Version 1.0 build 007.

Managing headers and footers in Excel can be a drag. This little tool helps you to change headers and footers in your files easily by means of a toolbar that shows dropdown/edit boxes in which you can type the header/footer codes directly or select them from the list of currently present header/footer codes in your file.

Setup Utility

SetupUtility.zip (Updated May 5, 2008, downloaded: 21.089 times)

If you have created a nice add-in (see this article how to do that), a setup utility is an easy way for your users to install your addin. This free utility just needs you to change 4 cells to roll your own setup file.

Update: Now automatically removes invalid addins from addin list.

An Excel Add-in that installs itself

selfinstallingaddin.zip (Updated July 12, 2021, downloaded: 3.102 times)

Many users struggle with getting add-ins installed. In this article I show some VBA code that will ease that process: As soon as the user opens the add-in and enables macros, the add-in offers to install itself. This article contains an explanation.

Update An Addin

UpdateAnAddin.xls (Updated February 19, 2007, downloaded: 41.207 times)

If you have created a nice add-in (see this article how to do that), an updating mechanism is an easy way to ensure your users get any bugfixes you did automatically . This download demonstrates how you can implement this functionality in your addin.

Show Table On Userform

ShowTableOnUserform.zip (14 May 2008, downloaded: 18.463 times)

For a project I needed a quick way to display the content of an array to the user. I didn't want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. So I figured I'd put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown. That proved far from easy... See this article on how it works.

Userform zoom demo

Userforms have a zoom property which allows you to make a form larger (or smaller) with very little code:

UserformZoomDemo.zip (23 Dec 2021, downloaded: 2.208 times)

A modern-looking userform in VBA

This is the file accompanying this article: Creating modern-looking userforms in VBA

ModernUserformWithSwitch.xlsm (15 Feb 2022, downloaded: 4.264 times)

Two Listbox Drag Drop

TwoListboxDragDrop.zip (June 10, 2020, downloaded: 4.514 times)

Demonstrates how to activate drag and drop between two listboxes on a Userform. See this article.

Fix Links to UDF

FixLinks2UDF.zip (02 June 2008, downloaded: 11.905 times)

A demo file that handles #Name! errors for workbooks that use UDFs that reside in an add-in. See this article on how that works.

Quadratic Equation

QuadraticEquation.xlsx (02 June 2021, downloaded: 3.628 times)

A demo file showing how to use the LAMBDA function to create your own Quadratic Equation function in Excel. See this article on how that works.

Switching Office Channel

SwitchingOfficeChannel.zip (04 May 2020, downloaded: 2.555 times)

A set of four BAT files to aid in switching your Office 365 to a different Channel.

Office Script examples

officeScriptExamples.zip (23 Aug 2021, downloaded: 4.051 times)

An Office script that adds a Table of Contents to your Excel on-line file. See this article.

Keep the Queries and Connections task pane in check

CloseQueriesPaneOnQuit.zip (Build 001, 26-05-2023, downloaded: 578 times)

This tool prevents the Queries and Connections task pane from becoming narrow next time you open Excel:

Queries and Connections taskpane in narrow state

Query Manager

QueryManager.zip (Build 017, 23 September 2010, downloaded: 22.797 times)

This utility has been developed together with Dick Kusleika . It eases the editing of queries and Pivottable connections. One can:

1. Edit the SQL string and the connect string of queries and PivotCaches

2. Add parameters

3. Change the path to the data source

All in a single dialog.

Autosafe VBE

AutosafeVBE.zip (build 026, 6 Aug 2007, downloaded: 28.709 times)

This utility makes backup copies of VBA components to a user-defined directory. It keeps a user selectable number of copies of each component. It thus keeps a number of generations of your code as your work progresses, enabling you to return to a previous copy when things go wrong. Because it just exports the VBA components, it is unobtrusive because this process is relatively fast compared to saving your workbook or document. Excel and Word version included!!

Non linear Least Squares

nonlinearls.zip (13 Apr 2022, downloaded: 27.534 times) Fit complex functions like y=exp(a.x).sin(x) + b to data using Least squares

Copy VBA Project

CopyVBAProject.zip (Version 1.0, Build 011, 22 Jun 2016, downloaded: 16.451 times)

This utility enables you to copy the components from the VBAProject of workbook A to Workbook B

USE AT OWN RISK!

Export VBA Project

ExportVBAProject.zip (Version 1.0, Build 010, 28 Sep 2022, downloaded: 11.682 times)

This utility enables you to copy the components from a VBAProject to a single text file. If you like, the tool also adds the properties of all userforms and their controls to the textfile (one comma-separated row per control). Very useful if you need to compare the VBA code of two Excel files. Use this handy tool to compare differences in the exported files: ExamDiff.

Github VBA Exporter

GithubVBAExporter.zip (Version 1.0, Build 011, Dec 14, 2022, downloaded: 2.871 times)

This utility enables you to export all components from a VBAProject to a user-selectable set of folders. Very useful if you need to upload the contents of a VBA project into Github. The tool generates a textfile which contains all properties of all userforms and their controls, and the ribbonX is exported too, which enables you to do diff's on your user interface.

Performance Class

PerformanceClass.zip (Version 1.0, Build 001, 20 Aug 2014, downloaded: 8.953 times)

This example file demonstrates the use of a class module to measure performance of your VBA code. See A VBA performance class for an explanation.

Trusted Document Manager

TrustedDocumentManager.zip (Version 1.0, Build 006, 4 March 2024, downloaded: 8.596 times)

This little tool enables you to manage your list of trusted documents. Currently, Excel only allows you to either leave the list intact, or delete the entire list. This means all of your currently trusted documents become untrusted again so you have to enable macro's on all of them once again. The tool allows you to remove just one file, remove an entire folder or even an entire drive. Also it offers the possibility to remove files which no longer exist from the list.

Only available for Excel 2010 and up!

Object Lister

Objectlister.zip (Version 1.0, Build 003, 1 October 2008, downloaded: 20.283 times) Lists objects, properties and methods of the selected object and enables you to quickly build code that uses many properties of an object. See the ObjectLister page for more information.

Tree View

An All-VBA (MSforms) treeview control that makes the common controls treeview obsolete. Works in all Office versions as of Office 2000, including 64 bits and MAC office.

The Excel download contains most of the documentation (on the tabs of the workbook), so I recommend you to at least download the Excel version.

Download the treeview sample workbook (build 026.5, 10 Jan 2023, downloaded 51.394 times)

Download the treeview sample Word document (build 026.5, 10 Jan 2023, downloaded 17.498 times)

Download the treeview sample Access database (build 026.5, 10 Jan 2023, downloaded 42.666 times)

See the accompanying article: An MSForms (all VBA) treeview

Wheel of Fortune

A wheel of fortune, built entirely in Excel!

Excel wheel of fortune (29 September 2016, downloaded 12.492 times)

See the accompanying article: Creating a Wheel Of Fortune with Excel

Bingo (Lotto)

An Excel file to play Bingo 20 Oct 2021, downloaded 2.090 times

Tombola

An Excel file to draw names (tombola) 29 Oct 2021, downloaded 1.934 times

VBA driven circular references

VBADrivenCircularReferences.zip (11 February 2015, downloaded 6.387 times)

This file demonstrates the use of VBA to control circular reference calculations. See: Working with Circular references in Excel, Properly setting up circular references

AutoChrt

AutoChrt.zip (2 March 2015, downloaded: 27.656 times)

Automates the process of creating graphs from database-like datasets, where you need to chart various columns against each other in x-y scatter charts to determine relationships between them. It consists of a sheet where to copy the data into and a sheet that holds the chart and some spinner-buttons to control which data are charted.

Simple Inventory System

SimpleInventorySystem.zip (11 Oct 2016, downloaded: 16.552 times)

A simple inventory system built in Excel using simple formulas.

Moving Checkbox

MovingCheckbox.xlsm (29 Feb 2016, downloaded: 14.955 times)

A small demo file which contains a column in which a checkbox is automatically displayed tied to the underlying cell.

Chart An Equation

ChartAnEquation.zip (May 1, 2005, downloaded: 18.404 times)

Demonstrates a method to chart a mathematical equation using just defined names.
See this article for an explanation.

Control Handler

ControlHandler.zip (17 June 2005, downloaded: 15.555 times)

Demonstrates a method to handle the events for multiple controls on a worksheet using a single class module. See this article for an explanation.

Control Lister

ControlLister.zip (10-7-2014, downloaded: 11.162 times)

Tool that lists all controls on your userforms on a worksheet. Includes code to rebuild the userforms from the table. Note: does not handle userforms with multipage controls very well!

Catch Paste Demo

CatchPasteDemo.zip (17 Dec 2007, downloaded: 15.590 times)

Demonstrates how to intercept paste operations in a workbook to prevent users from wrecking your validation. The download contains two workbooks; one for Excel 2003 and earlier, the other for newer versions of Excel. See this article for an explanation.

Undo Handler

UndoHandler.zip (8 March 2006, downloaded: 19.887 times)

Demonstrates a method to enable the user to undo changes made by your VBA code. See this article for an explanation.

Watch Other Cell

WatchOtherCell.zip (18 September 2012, downloaded: 15.910 times)

This workbook simplifies looking at data in a sheet with a lot of columns. It shows the value of a cell on the same row in a column one can enter in a textbox. This way you can scroll all over the sheet and always (e.g.) have the value of a cell in column BE in view. Start the watcher by opening the file and using its entry in the Tools menu.

Get A Range

GetARange.zip (4 May 2006, downloaded: 19.872 times)

This workbook demonstrates a bug in the VBA Application.InputBox function as described here . The workbook also contains a userform and sample code to work around that bug.

Edit Open XML

EditOpenXML.zip (5 September 2011, downloaded: 19.273 times)

Wouldn't it be useful to be able to edit the contents of an Office 2007 OpenXML file from within VBA? Well, now you can using this demo file. Find a full description here .

Modeless Form On Top

ModelessformOnTop.zip (26 November 2012, downloaded: 11.069 times)

With Excel 2013, SDI was introduced as opposed to the MDI previous Excel versions have. This file demonstrates how to keep a modeless userform on top of the Excel window. Find a full description here .

Arg2Name

Arg2Name.zip (8 February 2001, downloaded: 23.620 times)

This workbook demonstrates a trick to pass (range) arguments to defined name formula's. See the Excel names page.

Command Bar IDs

xlMenuFunDict.zip (May 19, 2022, downloaded: 30.287 times)

International versions of Office have the menu system in their local language. Also the Excel worksheet functions are (mostly) listed in local language. This complicates communication with the users with such a version. To aid in this process a utility has been devised that creates and shows a translation list of the Excel built-in command bars and controls and the Excel worksheet functions. This workbook can also list command bars of other Office software packages. It thus enables the international user who is using a different language version of Excel to quickly find translations for sequences of menu commands and function names.

Comments

All comments about this page:

Comment by: Hugh Curran (29-4-2006 03:27:38) deeplink to this comment

Hi guys
Thanks for a great page. I was tairing my hair out trying to translate a GET.DOCUMENT command to Dutch and your FunDict solved the problem. Many thanks

Comment by: ainivip (7-5-2006 04:42:52) deeplink to this comment

Thanks alot.

Comment by: Shahbaz Iqbal (29-5-2006 21:53:04) deeplink to this comment

Hi, I must admit that I really like your Flexfind utility. It has saved me a lot of time, and my skin too (Smile). I am not VBA techie but here is a little proposal regarding this utility. It would be really exciting if Flexfind could find text from closed files in a directory. I and many others are looking forward to it. All my best wishes are with you. Thanks you very much.

Regards.

Comment by: Jan Karel Pieterse (30-5-2006 01:01:59) deeplink to this comment

Hi Shahbaz,

Thanks for your comments. About finding text in closed files: Excel already has that capability (the way to get there differs from version to version). In Excel XP, choose File, Open and click the "Tools" button and select "Search".

In the subsequent screen enter your search criteria and off you go!

Comment by: Shahbaz Iqbal (9-6-2006 03:12:01) deeplink to this comment

Hi Pieterse,

Sorry for not explianing enough about my wish. So here it goes. Your utlity "Flexfind" does a fantastic job of listing all found values in another workbook. But for that the workbooks need to open to search for text and subsequently listing those found records. Excel's search is limited and also not powerful enough and besides it doesn't allow to put found records in a list. I believe there are VBA macros which could do that, i think.

Thanks for your reply. I request Microsoft to please add this utility in their 2007 release and please appreciate the hard work of author by paying for it this time around.

Have a wonderful time.

Comment by: SIVAREDDY (12-7-2006 03:06:17) deeplink to this comment

Thanks for your autosafe.

Comment by: Mike King (31-10-2006 22:57:51) deeplink to this comment

Name Manager is fantastic!! About 1000 times better than excel's handling of defined names.

Comment by: Jan Karel Pieterse (1-11-2006 04:09:46) deeplink to this comment

Thanks Mike, much appreciated!

Comment by: Claus Coman (3-11-2006 10:34:07) deeplink to this comment

I will spread this disease [Excel] among the people I like to work with.

Comment by: Fareed Ajmal (4-11-2006 05:27:17) deeplink to this comment

Hi
I find it Easie tha any other Software
Thanks

Partner

Comment by: Rae Vino (17-11-2006 00:23:49) deeplink to this comment

Thanks for your excellent flexifind. I was searching for an utility like this for a week. Im using excel 2000 which dont has a good find all interface like this. But excel 2003 has an interface like your free utility.The nice thing is free for all.

Comment by: Dan R. (3-1-2007 10:46:45) deeplink to this comment

Hiya,

For some reason, the Autosafe add-in keeps giving a pop-up on open saying it is the testing version. Is there a non-testing version without the pop-up?

Thanks!

-Dan

Comment by: Jan Karel Pieterse (3-1-2007 10:49:17) deeplink to this comment

Hi Dan,

Yes, the 35 beta version is network enabled and doesn't have the nag screen.

Comment by: kanwaljit (9-1-2007 01:49:17) deeplink to this comment

Hi Jan,
I have downloaded the new build (the link indicator still shows the build number as 577). But don't know how to make it compatible with present version of Fast Excel. Any ideas ?

Regards
Kanwaljit

Comment by: Jan Karel Pieterse (9-1-2007 03:12:28) deeplink to this comment

Hi Kanwaljit,

Contact Charles Williams for information on updating FastExcel:
<a href="http://www.decisionmodels.com">www.decisionmodels.com</a>

Comment by: Vivek (19-4-2007 08:03:13) deeplink to this comment

Hi Jan,

You rock !

Your addins are really wonderful pieces of code.

I especially like & use AutoSafe, FlexFind and Name Manager a lot.

I started using Excel 2007 recently,
so I'll let you know if there are any incompatibilities.

Thanks for your excellent efforts.
All the best.

Regards,
Vivek.

Comment by: Jan Karel Pieterse (19-4-2007 10:38:55) deeplink to this comment

Hi Vivek,

Gee, thanks for your compliments!

Jan Karel

Comment by: Narsi (26-4-2007 15:52:38) deeplink to this comment

Thanks for your excellent work on Name Manager. I saved a lot of time in finding and deleting 1000s (yes thousands) of orphaned range names.

Regards

Narsi

Comment by: Frits (15-6-2007 13:26:04) deeplink to this comment

Al weer een flink tijdje geleden werkte autosafe door veranderde netwerkbevoegdheden niet meer. Ik kreeg snel een goed antwoord waar het probleem zat en de toezegging dat het wordt opgelost, maar even zou kunnen duren. Inmiddels is het bestand geupdated en werkt weer voortreffelijk !! (heeft z´n nut na vastlopen al weer bewezen)
Dank voor updaten, maar vooral ook voor snel antwoorden en nakomen toezeggingen.
Fantastisch !!!

Comment by: Jan Karel Pieterse (17-6-2007 02:56:41) deeplink to this comment

Hallo Frits,

Bedankt en graag gedaan!

Jan Karel

Comment by: Pradeep (19-6-2007 20:53:47) deeplink to this comment

i want to use this utility.

Comment by: Jan Karel Pieterse (19-6-2007 23:59:14) deeplink to this comment

Hi Pradeep,

You did not say what utility?
You can download and use all tools on these pages for free.

Comment by: Jim (26-6-2007 13:30:46) deeplink to this comment

I've been missing Autosafe ever since upgrading to Excel 2007 - I'm eagerly awaiting a new version which works with 2007 :-)

Comment by: Jan Karel Pieterse (27-6-2007 00:42:50) deeplink to this comment

Hi Jim,

Are you saying it does not work as expected in 2007?

Comment by: Roger (27-6-2007 14:56:19) deeplink to this comment

How do you run this macro? CopyVBAProject.zip

I've added it in using the Add-in menu and I can see it in the VBA Project explorer but I can't find a way to run it.

Comment by: Jan Karel Pieterse (28-6-2007 02:39:21) deeplink to this comment

Hi Roger,

Look in the Tools menu...

Comment by: Dominique van Est (20-7-2007 14:09:28) deeplink to this comment

Should have know this prog (autosafe) 3 days ago :(

Thanks :)

Comment by: Pankaj Madgaonkar (24-8-2007 02:58:47) deeplink to this comment

Hi Team,

This is an excellent utility for all the people who are not so conversant with VBA or advanced excel utilities. Amazing pack... Appreciations for the good work...

Pankaj

Comment by: Ian (22-9-2007 08:30:04) deeplink to this comment

Been using autosafe for years.
Would love a word version!

Comment by: Jan Karel Pieterse (23-9-2007 21:04:59) deeplink to this comment

Hi Ian,

Thanks for the suggestion!

Comment by: srinivas (28-9-2007 22:36:04) deeplink to this comment

hi,
iam write code for download excel sheet in asp..
after download that sheet shows empty..
can u tell me reasons

Comment by: Jan Karel Pieterse (30-9-2007 10:31:28) deeplink to this comment

Hi srinivas,

No, I'm sorry I am no ASP expert. Have a go at one of the newsgroups mentioned on my links page.

Comment by: Graham (16-11-2007 04:04:50) deeplink to this comment

It looks like you have some really great tools here - I'm looking forward to trying some of them.

One that I can't see which I would dearly love is a 'target total' function. For example, you have a long column of data, perhaps an extract from bank statements, which totals say £200,000. You know that a number of the items adds up to an exact number, say £12,360.45. I would like a function that would be able to show me all the combinations of rows that would total to £12,360.45. I appreciate that there may be several, so presentation of the results may be tricky.

This would be a really useful tool for accountants and bookkeepers. It would also have applications in law enforcement for tracing a sum coming in to a bank account that is then broken down into smaller parts before being paid out.

Hope you can help!

Graham

Comment by: Jan Karel Pieterse (16-11-2007 05:32:06) deeplink to this comment

Hi Graham,

Thanks for your suggestion, much appreciated.

Comment by: Debra McLaren (18-12-2007 00:31:31) deeplink to this comment

The catch paste download link is broken.

Comment by: Jan Karel Pieterse (18-12-2007 02:56:38) deeplink to this comment

Hi Debra,

Thanks!

Fixed now.

Comment by: Fred (27-1-2008 12:37:06) deeplink to this comment

Hi Jan Karel,

I think you said some time ago that you did not do much with your Header/Footer manager. That's too bad! I see it's not even listed on your web site. I have a copy from 2004.

I discovered a bug: when there are no work books open and you open a new or existing one, I'm getting the following error:

method 'ListIndex' of object 'CommandBarComboBox' failed

Only thing that works is to close Excel entirely and re-open it. You can work in Excel but the toolbars are grayed out.

If you're so inclined to fix the bug, that would be great. Also 2 suggesions for enhancements:

1. a form (for which I can get a button on my toolbar - not sure how to do) that provides a checkbox for each of the header and footer managers so I can decide what I want open when. I know they're under the Toolbars but that's buried. This would be better if it had a section with all the & abbrevs because the alternative is to go into Excel's header/footer area and click on each to see what it is - ie click the calendar to see &D for date.

2. Would love to be able to be able to take the info for the header or footer from a cell. Maybe a sheet with a special name where cols A,B,C correspond to the left, center, right items or something like the formula box capability to "shrink" the box and click on a cell that is then used in the formula.

Anyway, I know that these probably won't happen. And I can train myself to avoid the bug. Just some thoughts.

Thanks.

Fred

Comment by: Jan Karel Pieterse (28-1-2008 01:29:59) deeplink to this comment

Hi Fred,

I've updated the tool and listed it here!

Comment by: Tatiana (31-1-2008 02:04:58) deeplink to this comment

Hello, I will like to have the number of pages and the page im in w/o putting it myself manually, and I dont want it to be in the Head or Foot of the Excel worksheet, i want it to be where i decide to put it. Can you help me? I saw your answer in a Forum with the PageOfPages, but I got no idea how to use it. Sorry kinda nul for pcs.
thanks for ya help!

tatiana

Comment by: Jan Karel Pieterse (3-2-2008 22:20:50) deeplink to this comment

Hi Tatiana,

I've sent you a sample file.

Comment by: Philip (15-5-2008 04:15:26) deeplink to this comment

Hi,

brilliant collection of utilities. Thank you.

I am having a problem with the AutoSafe.xla (in MS Excel 2003). Everytime it tries
to run I get an error message like 'C:\temp\Autosafe VBE.xla!ExportThem' cannot be
found.

The strange thing is that the add-in is installed in the correct place, so how do I
fix this behavior.

thanks
Philip

Comment by: Jan Karel Pieterse (15-5-2008 04:25:58) deeplink to this comment

Hi Philip,

Odd, AutosafeVBE works fine for me.
Could you please download a fresh copy from my site, uninstall the old one and
install the new one?

Comment by: james grimes (22-5-2008 06:21:17) deeplink to this comment

How do I add autosafe to trusted publishers in excel 2007. I know where "trusted
pubs" is, I just don't know how to add to it. Thanks

Comment by: Jan Karel Pieterse (22-5-2008 10:15:03) deeplink to this comment

Hi James,

You should not have to add autosafe to your trusted publishers, if you install it,
it is supposed to be placed in a trusted location.

You can however open the addin file itself directly (Office button, open). Then you
should get an enable macros screen that should have a checkbox to trust code from
this publisher.

Comment by: james grimes (22-5-2008 15:58:29) deeplink to this comment

Hi Jan,
Thanks for your prompt response. I did get autosafe installed properly & it has
saved my bacon! Excel 2007, which I just installed on my main office computer, was
locking up every hour on the hour. I noticed that every time it crashed, it was
attempting an auto-recover. Then I remembered autosafe, which I had put on another
computer about a year ago, just to see how it worked. Since installing autosafe, (&
dis-abling auto-recover), on the office computer, no problems at all. What a
lifesaver.

Comment by: Markus (11-6-2008 14:25:38) deeplink to this comment

Hi Jan,

great thanks for Autosafe that saved me many hours of work! On my new PC however,
which has no C: drive (during installation of WinXP the built in card reader hijacked
the drive letters from C: on, so the system partition is H:) it gives an error
message that some file cannot be found. This is caused by some references to C:\ in
your code. Not longer a problem for me as I could fix it but maybe for others...

Comment by: John (7-7-2008 07:04:45) deeplink to this comment

Will AutoSafe help in this scenario? I have an open sheet for several days,
without any manual saves, I know not very smart. I open another from an email,
ect. ect. and when I closed it I mistakenly answer "no" thinking its the page I've
just opened and it closes all my files, all changed made in past few days are
gone. I'm looking for a scheduled auto save where it would save as last known
good, just one save from original?

Comment by: Sandeep (31-7-2008 13:50:41) deeplink to this comment

Hi Jan.

I use an addin called CWBTXLA to download and upload data from as400 server. Now I
am working on a new machine and installed the addin. It is craching the excel
whenever I tries to connect to As400. I think someone else might have asked you
same question, as this utility has been used by lot many people. Please let me know
if you have found a fix to work it correctly.

Comment by: Jan Karel Pieterse (6-8-2008 10:54:02) deeplink to this comment

Hi Sandeep,

Which addin please?

Comment by: Jan Karel Pieterse (6-8-2008 11:02:48) deeplink to this comment

Hi John,

Yes, Autosafe will help in that scenario, since you will find backup copies of the
edited file in your recycle bin, from which you can restore the last one. This will
be picked up by Autosafe.

Comment by: SANDEEP GAUR (19-8-2008 15:39:33) deeplink to this comment

Hi Jan,

I am sorry, as I missed to check the updates.
The addin which is creating problem for me is CWBTFXLA. It gets installed when
client access is being installed. I referred from location where this addin is
available in IBM/Client access folder. I have recentely got Office 2007 installed
on my machine. It appears this addin is not accepted by 2003 and 2007 as a valid
addin.

Regards,
Sandee[

Comment by: javier gonzalez (5-9-2008 19:36:21) deeplink to this comment

congratulations Jan!
for your interest in publicizing a method to graph in a simple rectangular system
in any capacity, whether this method can be applied to the polar system?
The assessment formula may lead to evaluate algebraic equations? say factoring or
derivative? beste regards
friendly shake hands!!!!
javier gonzalez
from Fresnillo, Mexico

Comment by: Jan Karel Pieterse (8-9-2008 00:49:13) deeplink to this comment

Hi Javier,

You could create a polar chart this way, but I think you have to do a conversion
from polar to semi-x-y values Excel can plot. No doubt you can find information on
this if you google for polar plotting with Excel.

Comment by: Eric DeSouza (8-9-2008 21:07:31) deeplink to this comment

For the people want the old autosave of office 97/2000, there is "autosave.xla"
aka "autosave.xls" that you can get to work in a newer version of office. Simply
get an old office disk 97/2000 version, and take the single autosave.xla file. Go
to Excel -> Tools -> Addins and then browse to the file autosave.xla. Only
downside, you cannot change the default of 10 minutes of autosaving. The addin is
password protected by Microsoft and cannot be altered in VBA.

Comment by: Govert Vissers (4-11-2008 08:43:52) deeplink to this comment

Hi Jan Karel,

I just love The Name Manager add-in!
It's a real timesaver.

I also use JMT utilities a lot.

Thanks for sharing all those great excel-gems.

Govert Vissers

Comment by: Jan Karel Pieterse (4-11-2008 10:17:02) deeplink to this comment

Hi Govert,
Thanks!

Comment by: Egon (5-11-2008 13:38:05) deeplink to this comment

Hi Sandeep,

I find the AS400 (iSeries) utilities quite frustrating myself - they're pretty buggy. If there are nulls in your data table, that might be the issue.

If so, try some ADODB instead: http://support.microsoft.com/default.aspx?scid=kb;en-us;146405

Comment by: Randy (28-1-2009 05:50:02) deeplink to this comment

Hi Jan, Great product- I've successfully removed several hundred strange names/names with bad invalid REFs from a workbook. However now when I close the file it crashes excel every time. Note that I can work/save changes in the file and it only crashes upon exiting Excel. Have you heard of this or seen this before?

Comment by: FARAZ AHMED QURESHI (8-3-2009 14:23:15) deeplink to this comment

An excellent site Jan! However, could you solve or guide me in the following query?

Excel 2007 sure does provide a good feature of auto completion in case of entering a built-in function so as to select one from the recommended list instead of typing the whole. However, the UDFs I have defined in an AddIn are not being presented so.

Any idea to overcome this and have your UDFs be listed as well?

Thanx in advance

Comment by: Jan Karel Pieterse (9-3-2009 00:22:52) deeplink to this comment

Hi Faraz,

It seems to work OK for me (the function is shown in the autocomplete list, but not the arguments).

Comment by: Pankaj Rayal (26-3-2009 23:19:51) deeplink to this comment

I would like thank you for these examples. i learned a lot of them. Excellent job

I hope in future you will inform us about your new research.

Once again very very thanks and regards.
Your
Pankaj Rayal
pankaj_rayal@hotmail.com

Comment by: Karen Chew (10-4-2009 00:33:31) deeplink to this comment

Your add in are great - thanks

Comment by: LJ (13-4-2009 22:32:38) deeplink to this comment

Hi Jan,

I have a query that probably you can help me as I saw many sites that has your name in it. I had encountered similar problems like the others regarding UNREADABLE CONTENT error.

""Excel unable to open the file by repairing or removing the unreadable
content.
-Removed records: styles from /xl/styles.xml part (styles)
Repaired records: format from /xl/styles.xml part (stlyes)"

However, I cannot forward the file as it is very confidential. Is there a software or twicks that i need to have to be able to repair the problem? I validated that the values and data are correct its just that the formatting and styles are gone. I need a fix for this as i need to present this workbook to management and its very rubbish to present it in simple formats and no styles at all. Hope you can help.

Comment by: Jan Karel Pieterse (14-4-2009 01:32:22) deeplink to this comment

Hi LJ,

If you like you can send the file to me, I'll have a quick look.

Comment by: Brian (21-4-2009 16:02:34) deeplink to this comment

Have you found anything out about LJ's problem? I'm having the same issue.

Comment by: Jan Karel Pieterse (21-4-2009 21:22:51) deeplink to this comment

Hi Brian,

No, LJ hasn't responded. You can send the file if you like though.

Comment by: Abdul Qayyum (12-6-2009 02:57:18) deeplink to this comment

Sir

I want to an attendance reqister which sandwitch the rest day if the absent occur before and after the rest day means if a occure before and after r then the r count as a
please help me in this mater i will be thank ful to u

Thanks

Abdul Qayyum

Comment by: Jan Karel Pieterse (12-6-2009 04:55:35) deeplink to this comment

Hi Abdul,

Of course I can develop something for you commercially. Please send me an email using the email address at the bottom of this page.

Comment by: Jim (17-6-2009 19:48:12) deeplink to this comment

I love Autosafe - it makes file recovery so much easier :-). But it has one nasty side-effect: whenever it runs, Excel's undo list clears. I count on being able to undo actions, but now never know when I'll be unable to do this. Is there any workaround to avoid this behavior?

Thanks!

-jim.

Comment by: Jan Karel Pieterse (17-6-2009 21:24:45) deeplink to this comment

Hi Jim,

I know, that is the only real drawback of Autosafe (in fact, of anything that does a save in Excel): it zaps your undo list.
Luckily, as of Excel 2007 the undo list is no longer affected by a save.

Comment by: Jim (17-6-2009 23:19:32) deeplink to this comment

Hmm, I think something else is going on. I'm running Excel 2007 SP2, and indeed, if I manually save a file, the Undo list stays intact. But the moment that Autosafe backs up my files, my Undo lists clear. Any other ideas?

Thanks!

Comment by: Jan Karel Pieterse (18-6-2009 00:11:32) deeplink to this comment

Hi Jim,

Odd indeed, seems the save through the UI does not behave the same as save through VBA.
On my system (2007 sp2) the last undo action is is retained however, so it is slightly better than 2003 and before used to be.

Comment by: Gerald Shaffer (30-7-2009 18:08:24) deeplink to this comment

I do comercial estimating and the program I use (etakeoff) comes with a spreadsheet which I can use to break down walls into the various components. I then copy and drag the contents to excel. The problem I'm having is that when I get the info into excel, I have to manually copy the amount of say type X sheetrock from column a2 to the corresponding column for type x sheetrock. Is there a way create a column which reads a code in the same row as the amount is in and enters or copies that amount in a specified column for type x sheetrock? Would be kinda like " if columm b2=01(code for type x sheetrock) copy amount in columm c2 to column d2(column d would be type x sheetrock) then do the same for the next row, if column b3=02 copy amount in column c3 to column e3 and so on and so on? Any help or suggestions where I might find any info on how to program excel to complete this operation would be greatly appreciated.

Comment by: Jan Karel Pieterse (31-7-2009 06:13:14) deeplink to this comment

Hi Gerald,

You could use a formula in your destination table using VLOOKUP to extract the information from the other table.
Look in Help what the VLOOKUP formula comprises.

Comment by: Richard Kennedy (20-8-2009 05:54:23) deeplink to this comment

Excel version:     2003 (Office 11)
Language:         Portuguese (Brazil)

Add-in:             Autosafe

Comment:            Items copied are lost (wiped from clipboard?) when save of autosafe runs.

Fix:                no suggestions at this time.

Add-in:             xlMenuFunDict

Comment:            Very useful to me (I'm english, working in Brazil)

Comment:            ssheetname generated for PowerPoint (Office 11) is too long (> 31 characters)

Possible fix:     test length of ssheetname, if too long, replace "Microsoft" in ssheetname by "MS"

Comment:            Error during creation of menu lists for PowerPoint, initiated by command button with text "Add menulist of appl in dropdown". Powerpoint is opened, new Excel sheet seems to list items in portuguese successfuly, but no entries in English. Powerpoint crashes. VBA stops at code line

ListIt iLevel + 1, "(" & cControl.Caption & ")"

Sub ListCB

. Running

Sub ListMenuTextXL()

with

ListAppMenuText ("powerpoint.Application")

generates same errors.

Fix: no suggestions at this time.

Sincerely,
Richard.

Comment by: Pleased (2-9-2009 02:37:12) deeplink to this comment

After losing days of work, Autosafe is a great utility, thanks for helping computers to make our lives easier...

Comment by: Jan Karel Pieterse (7-9-2009 09:46:25) deeplink to this comment

Hi Rich,

Thanks for your suggestions!

Comment by: ElmerPabel (18-10-2009 22:27:29) deeplink to this comment

We added some code in the arg2name.xls file . the name Myref "=MID(GET.CELL(6,INDIRECT(GetRC,FALSE)),FIND(GetRow,GET.CELL(6,INDIRECT(GetRC,FALSE)))+4,FIND(")",GET.CELL(6,INDIRECT(GetRC,FALSE)))-FIND(GetRow,GET.CELL(6,INDIRECT(GetRC,FALSE)))-4)"

in spanish

'=EXTRAE(INDICAR.CELDA(6,INDIRECTO(GetRC,FALSO)),ENCONTRAR(GetRow,INDICAR.CELDA(6,INDIRECTO(GetRC,FALSO)))+4,ENCONTRAR(")",INDICAR.CELDA(6,INDIRECTO(GetRC,FALSO)))-ENCONTRAR(GetRow,INDICAR.CELDA(6,INDIRECTO(GetRC,FALSO)))-4)

the part "+4" and "-4" need replace to "+5" and "-5" .

thanks for a great utility.

Elmer

Comment by: ed.ayers315 (15-11-2009 12:57:16) deeplink to this comment

I have tried a number of forums with this request with no luck so far.

I have a user form where I need users to input dates and times for events that happen, there are 38 dates and 38 times.

I know if I put the form out there to use with all that info manually entered, I will not get my information consistanly. Any suggestions?

Comment by: Mike (15-11-2009 21:01:25) deeplink to this comment

I've installed autosafe on Excel 2007. How can I access the settings dialog? It does not appear under the File menu. Thanks

Comment by: Jan Karel Pieterse (15-11-2009 22:22:39) deeplink to this comment

Hi ed.ayers315,

Without knowing your further requirements, this is hard to answer. All I can say is make it as easy for the user as you possibly can.

Comment by: Jan Karel Pieterse (15-11-2009 22:23:10) deeplink to this comment

Hi Mike,

In Excel 2007 look on the addins tab of the ribbon.

Comment by: bancha (10-12-2009 09:28:49) deeplink to this comment

thank for example

Comment by: Nayan (7-1-2010 00:12:38) deeplink to this comment

ITS REALLY GOOD & SO HELPFUL

Comment by: Raman (19-1-2010 05:36:17) deeplink to this comment

I have a table containing 'Name' in columnA, 'Date' in ColumnB and 'Data' in columnC. For a particular 'Name' and 'Date' i have several rows of 'data'. I want to convert this format into a format where all rows of data becomes consecutive columns. Kindly help with the code.

Comment by: Jan Karel Pieterse (19-1-2010 08:28:10) deeplink to this comment

Hi Raman,

Does this help:

- Select Table
- Control+c
- Select area to the right of table
- Home, Paste, Paste Special, Check "Transpose".

Comment by: Vino (2-2-2010 00:22:01) deeplink to this comment

Hi,

How to record the Pivot table fuction in MACRO recording

Comment by: Jan Karel Pieterse (2-2-2010 05:32:53) deeplink to this comment

Hi Vino,

Doesn't it record your action?
Have a look at this site for lots of info on pivot tables:
http://www.contextures.com

Comment by: mae (21-2-2010 23:06:45) deeplink to this comment

how can you edit a formula which you have already validated?

Comment by: Jan Karel Pieterse (22-2-2010 07:29:14) deeplink to this comment

Hi Mae,

By hitting the F2 key or clicking in the formula bar?

Comment by: Andrew J (10-3-2010 15:13:19) deeplink to this comment

AutoSafe VBE is great! But it won't work on an XLA. Is there anyway I can get it to do that?

Comment by: Jan Karel Pieterse (10-3-2010 22:24:06) deeplink to this comment

Hi Andrew,

I could, but don't have the time right now.
I usually do not develop in add-ins. Instead, I edit the source workbook, which when done I save-as an add-in.

In such a case, AutosafeVBE just works.

Comment by: Charmaine (11-3-2010 17:28:05) deeplink to this comment

I have an Excel file that has become corrupted. Upon opening yesterday there was a lot of gibberish in there...code perharphs, I don't know. The worksheet was completly changed merging cells and converting cells to the gibberish...is there any way to fix this? There is a months worth of data in the document and it would take months to try to recreate.

Thanks,
Charmaine

Comment by: Jan Karel Pieterse (13-3-2010 10:49:39) deeplink to this comment

Hi Charmaine,

Have you checked out the options on my page on corrupt files? (look under Articles)?
www.jkp-ads.com/articles/corruptfiles.asp

Comment by: Chris (19-3-2010 16:46:52) deeplink to this comment

Thanks for the great software

Comment by: Anil Gamare (13-4-2010 03:35:43) deeplink to this comment

in excel 2007, I filled up entire first row with 1s (ones) and tried to copy it to entire excel sheet. I am encountering the following error which I will not get in excel 2007 :

“Excel Cannot complete this task with available resources. Choose less data or close other applications.”

About hardware, I am using the following hardware :
Model : HP Z600 Workstation
Processor : Intel® Xeon® CPU X5560@ 2.80 Ghz
RAM : 12 GB
Cache Size (L1/L2/L3) : 256KB / 1024KB / 8192KB
DIMMs (MB) : D1:2048 D2:2048 D3:2048
Operating System : Windows XP Professional x64 Edition version 2003 SP2

Pl help me to overcome on it.
Regards,
Anil

Comment by: Jan Karel Pieterse (13-4-2010 05:22:18) deeplink to this comment

Hi Anil,

I doubt if I can help with this.You just tried to fill a 16,000 by 1,000,000 cells table. Your system just cannot handle this amount.

This is hardly a case that is likely to happen. Mostly a table in Excel contains either lots of rows and some columns, or the other way around. A lot of both is very, very rare.

Comment by: Anil Gamare (14-4-2010 21:19:17) deeplink to this comment

Hi Jan Karel Pieterse,
I do agree with you. But in real-time, I have almost 10000 rows and columns upto dkz. When I try to insert a single column in it, immediately it gives me error “Excel Cannot complete this task with available resources. Choose less data or close other applications.” So what do you think, I should do to overcome this problem.

Regards,
Anil Gamare

Comment by: Jan Karel Pieterse (14-4-2010 21:24:27) deeplink to this comment

I think you've just hit a limit of what Excel 2007 can handle. You need to reconsider how to work with this data I'm afraid.

Comment by: Mr. Lau (6-5-2010 23:44:49) deeplink to this comment

How do I recover the master files (Word/Excel 2007) by any of your softwares after saving the wrong versions? Thanks a lot.

Comment by: Jan Karel Pieterse (7-5-2010 05:19:32) deeplink to this comment

Hi Lau,

I don't think you can. Files that have been overwritten can only be recovered using dedicated file recovery software.
If you want to use thatoption, make sure you do as little as possible with the computer on which the files you want recovered are, so as to prevent Windows from overwriting the relevant part of your hard disk.

Comment by: Robert Armstrong (7-5-2010 08:17:22) deeplink to this comment

I am trying to use the "PROPER" function to capitalize the first letter for 235 entries all at once. The best I have done so far is to make this work by choosing a cell a couple of colums over and that is word by word. If I use the colum right next to my data it wont work, it just shows the formula whether I click "show formula on or off", but several colums away I get results, but only one word at a time. Is there not a way I can just select the entire colum, click the text format dropdown, choose "PROPER" and just convert the entire colum? If I choose the cell I want to convert then pick "PROPER" and select the same cell that I am applying the function to it shows in the dialog box that it is going to capitalize the first letter, but when I click ok I get a warning about circular ref.

The wierd part is that when I insert a new colum with the expectations of populating all the new cells with the modified strings all I get is the formula listed in the cell, but if I go a few colums over and pick any cell the operation works. But only one word at a time, not a mass coversion of all 200 or so entries. I know that I have probably confused the heck out of this description so I will just say what I am attempting to do. I want to take 200 plus words in one collum and capitalize the first letter of each word all in one shot rather than word for word.

Thanks, I am using (12.0.6524.5003) SP2 MSO (12.0.6529.5000) MS Home and Student 2007

Comment by: Jan Karel Pieterse (7-5-2010 10:37:14) deeplink to this comment

Hi Robert,

The columns for which the function doesn't work have been formatted as text. Format them as general and enter the formula.

This little macro sets all selected cells to proper case:

Sub MakeProper()
    Dim oCell As Range
    For Each oCell In Selection
        oCell.Value = Application.Proper(oCell.Value)
    Next
End Sub

Comment by: Robert Armstrong (7-5-2010 15:27:37) deeplink to this comment

Thanks Jan !!

Worked like a charm. Selected the text / ran the macro / 2 seconds later everything was converted.

Is oCell a variable that knows only to deal with the selected text? I noticed that no particular range was listed.

Comment by: Jan Karel Pieterse (8-5-2010 02:59:23) deeplink to this comment

oCell is used as the loop variable. The for Each oCell in Selection tells VBA to put each cell in the selction into oCell in turn. Then the next statement does something with that particular cell.

Comment by: xtream (9-5-2010 13:58:00) deeplink to this comment

I really need your help to make custom titlebar in VBA with one additional button near to close button.

Thanks in advance.

xtream

Comment by: Jan Karel Pieterse (9-5-2010 23:00:52) deeplink to this comment

Hi xtream,

What do you mean by a custom title bar?

Comment by: Yard (17-6-2010 07:33:51) deeplink to this comment

Hi,

Just installed Autosafe.xla in Excel 2010. It looked OK and has made one background save, but when I choose Autosafe settings from the Add-ins ribbon, I just get an InputBox saying "Please choose a new shortcut key", where the default value is "n".

Have I missed something?

Thanks

Comment by: Jan Karel Pieterse (18-6-2010 01:34:42) deeplink to this comment

Hi Yard,

That is very odd, not something I programmed into Autosafe, so I suspect this is Excel (or possibly another add-in) doing something to avoid conflicting shortcut keys?

Comment by: Yard (21-6-2010 01:11:17) deeplink to this comment

Thanks - any suggestions as to what I can do about it? No matter what I enter into that InputBox, the Autosafe settings screen does not appear! It only appears when I uninstall the add-in and then re-install.

Comment by: Pieter (21-6-2010 03:11:01) deeplink to this comment

Have downloaded Autosafe. Thanks. I installed into Microsoft 2007 (Home & Student version) I seems to work O.K - when I click on Add-ins I can change the settings etc.
However , it does not do a back-up - even after half an hour no backup files show in either my chosen directory or in Recycle Bin.
Did I miss something?

Comment by: Jan Karel Pieterse (21-6-2010 04:11:22) deeplink to this comment

Hi Yard,

Could you please try unchecking other add-ins, maybe it is some other addin causing havoc?

Comment by: Jan Karel Pieterse (21-6-2010 04:12:09) deeplink to this comment

Hi Pieter,

It should create files. But it only saves if a file has been changed, so please first make a trivial change to a file open in Excel.

Comment by: Pieter (21-6-2010 04:57:43) deeplink to this comment

Thank JK - it now works OK!

Comment by: Alex Turetsky (29-6-2010 09:39:33) deeplink to this comment

I installed Autosafe, but do not see the "settings" screen. I am using Excel 2007 and there is no file menu there of the type you show on the screenshots.

Please advise,

Thanks,
Alex

Comment by: Jan Karel Pieterse (29-6-2010 11:27:33) deeplink to this comment

Hi Alex,

Look on the add-ins tab of the ribbon.

Comment by: Mike Lemaster (13-8-2010 18:09:59) deeplink to this comment

I'm just a user of Excel and I just need a simple add in to select rows or columns from multiple spreadsheets and insert them into a new spreasheet. I found one called RDBMerge that almost does it, but gives me errors when I try to copy more than one row or colmn at a time. I can't seem to find anything that can do this simple task...Any help would be appreciated.

Comment by: Jan Karel Pieterse (16-8-2010 04:39:46) deeplink to this comment

Hi Mike,

I haven't got a ready-made solution I'm afraid. Maybe someone at www.eileenslounge.com can help?

Comment by: Mike Lemaster (17-8-2010 12:03:38) deeplink to this comment

Thanks anyway. I'll check it out.

Comment by: Mickey (27-8-2010 09:07:06) deeplink to this comment

I just installed Autosafe and I'm using 2007. I can't find the settings. I read this page looking for answers, but the problem is, I have no "Add-Ins" tab on my ribbon and I can't figure out how to get it up there. As you can tell, I'm just a very basic user of Excel.

Comment by: Jan Karel Pieterse (27-8-2010 10:22:47) deeplink to this comment

Hi Mickey,

Probably the installation process didn't go well. Open the add-ins dialog (alt+t, i). Look for an entry called "Autosave & Recovery Utility". If it's there, uncheck it, close dialog, close Excel. Repeat the above, this time checking that tool. Now you should have the addins tab appear.

Comment by: Bruce (29-8-2010 13:17:22) deeplink to this comment

I have Excel 2010. Are your add ins compatible with Excel 2010?

Comment by: Jan Karel Pieterse (29-8-2010 23:37:06) deeplink to this comment

Hi Bruce,

They should all work in 2010. Where available, download the 2007 version.

Comment by: Ram (30-8-2010 13:10:56) deeplink to this comment

I add macros which work as add ins in all excel files when using Office 2003. While using Office 2007, the macros get displayed under 'Add-Ins' ribbon. When I click the macro icon, it does not work. It says file cannot be found. Check your spelling, or try a different path. How do I change the path for add-ins in excel 2007. Please help. Thanks.
Ram

Comment by: Jan Karel Pieterse (30-8-2010 23:18:47) deeplink to this comment

Hi Ram,

Many things might be wrong.
When you click the macro icon, is the correct filename displayed?
If you go into the add-ins dialog (alt+t, i) and select the add-in in question, do you get an error?
If not, is the right file displayed at the bottom of the dialog?
Are you sure the macro in question is in the add-in file?

Comment by: josefklus (30-9-2010 11:19:13) deeplink to this comment

HI. I take it Autosafe does NOT work with Mac? After following manual instructions, I get an compile error.

Comment by: Jan Karel Pieterse (2-10-2010 05:22:13) deeplink to this comment

Hi Josef,

You're right, it doesn't. It contains quite some Windows-specific API code. Drop me an email if you want the code so you can try to adjust it to MAC Excel.

Comment by: Judy Schwarz (2-11-2010 06:36:38) deeplink to this comment

Visual basic runs every time I close Excel. How can I stop this?

Comment by: Jan Karel Pieterse (2-11-2010 23:27:35) deeplink to this comment

Hi Judy,

Could you give a bit more detail please? What do you mean by "Visual basic runs"?

Comment by: Judy schwarz (3-11-2010 04:54:22) deeplink to this comment

When I click Close, I get a Microsoft Visual Basic pop up that says: Run-time error '424':
Object required

Continue End Debug Help

I just want to close the file.
Thank you.

Comment by: Jan Karel Pieterse (3-11-2010 06:33:54) deeplink to this comment

Hi Judy,

Is this happening when you close a specific file or every time you close Excel? If the latter, look at the options listed here how to troubleshoot the issue:
https://jkp-ads.com/articles/startupproblems.asp

Comment by: Marcos Pereda (13-12-2010 18:40:54) deeplink to this comment

Hi, I found the RegisterUDF7.xls fantastic !
My problem is that I have more than 100 user functions in the same xla. I wonder where I can get more than 100 dll unique functions or if I can make my own dll with dummy functions in vb6 ?
Thanks in advance for your help.

Comment by: Jan Karel Pieterse (14-12-2010 00:13:07) deeplink to this comment

Hi Marcos,

You can, but to be honest, I don't know how to create a VB6 dll that allows that.

Comment by: ANIL KEDIA (1-1-2011 00:59:51) deeplink to this comment

Hi
I required to insert picture in cell whose file name given in crosponding cell can i do it by programing.

regards
thanks
anil kedia

Comment by: Jan Karel Pieterse (2-1-2011 22:20:01) deeplink to this comment

Hi Anil,

Have a look at this page:

http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Excel/486.html

Comment by: Billy (2-2-2011 02:13:25) deeplink to this comment

I downloaded BackupTool 004.mdb from web - JPK Appl Dev Services. Exactly what I needed. I'm on Access 2010. Problem is when I open the application, the timer settings are disabled. Only once I go into the Settings option and out again, its active. What should I do to have the timer setting active when opening the utility. Appreciate your assistance. Billy

Comment by: Jan Karel Pieterse (2-2-2011 03:24:51) deeplink to this comment

Hi Billy,

It sure works for me on Access 2010. Have you enabled the active content for the database? (Access may have opened the database in sandbox mode because you downloaded it from the web).

Comment by: Randy (1-3-2011 17:59:10) deeplink to this comment

Hello Jan,

I downloaded Autosafe to see if works better than Autorecover. I like what it does but it seems it also has an issue saving when there are two workbooks open (same problem I am seeing with Autorecover). As soon as the second workbook is closed both save fine. I've read a few things stating this is a "single instance" issue with Excel. Is this true or is there something else I can try.

Thanks!

Comment by: Jan Karel Pieterse (1-3-2011 23:23:00) deeplink to this comment

Hi Randy,

I can confirm what you see. In my opinion, this is a bug in Excel or VBA where application events sometimes somehow cancel the close (and save) event where they should not.

I have not yet found a way to prevent this from happening.

Comment by: Bob Schaevitz (2-3-2011 13:24:59) deeplink to this comment

Hello. I have recently moved from Excel 2003 to 2010. I had been using your AutoSafe product with great results for years. I just downloaded and installed the latest version from your website. However, the "Autosafe" menu item does not appear on the 2010 "File" menu. Can you please tell me how to access the file location and frequency dialog?

Also, the automated installation places the add-in file in a folder that is no longer used by Excel 2010. (Maybe it worked for 2007.) The correct location seems to be: C:\Program Files\Microsoft Office\Office14\Library.

Finally, I'm using an old version of FlexFind with 2010 and it seems to work. Is there any reason to use the new one?

Best regards,
Bob Schaevitz

Comment by: Jan Karel Pieterse (2-3-2011 23:18:37) deeplink to this comment

Hi Bob,

Look for it on the Add-ins tab of the ribbon.

Comment by: Huong Huynh (20-3-2011 19:48:26) deeplink to this comment

hello,

What is the best solution for us to prevent reader print from Excel pages? I have read some online instruction but seem it doesnt work for Win7 today.
Can you please advise?

Thanks in advance.

Regards,
Huong Huynh

Comment by: Jan Karel Pieterse (20-3-2011 22:16:15) deeplink to this comment

Hi Huong,

The only way I know of is by using Information Rights Management. In Excel 2010 it is found under File, Info, Permissions, Restrict permission by people, Manage credentials.

Comment by: FARAZ AHMED QURESHI (23-3-2011 10:49:21) deeplink to this comment

Hi Jan,

Sure was a superb & tremendously XClent job on the new NameManager+ specially with respect to it's perfect position and placement on the ribbon.

Comment by: Gary Camp (4-4-2011 08:06:22) deeplink to this comment

Thank you for the great (and free) utilities. I am retired and still continue to use Office 97. So it was with pleasure to see you still support that with Flexfind, a great add-on. I cant believe Excel has such a terrible Find that it cant do a decent search on sheets in just one workbook.
Thanks again, Gary

Comment by: Jan Karel Pieterse (4-4-2011 09:34:26) deeplink to this comment

Hi Gary,

You're welcome!
NB: Excel has improved search nowadays, you can search an entire workbook. But Flexfind still does a better job if you ask me :-)

Comment by: Paul (17-4-2011 03:43:57) deeplink to this comment

Hi Jan

I am not sure if I am being particularly dim but I am trying to set up Query Manager - but get the error suggesting I copy the xla to the directory - where can I find the xla to copy it?!

Thanks for all the great utilities!

Paul

Comment by: Jan Karel Pieterse (17-4-2011 10:08:30) deeplink to this comment

Hi Paul,

Just open the zip file and drag the xla to any location you like and go from there.

Comment by: Bob Kennelly (30-4-2011 12:06:15) deeplink to this comment

Can i use the GoBack plugin for Word?
Thank You!

Comment by: Jan Karel Pieterse (1-5-2011 21:47:27) deeplink to this comment

Hi Bob,

I'm sorry, no it can't.

Comment by: Sameer Joshi (15-7-2011 05:49:41) deeplink to this comment

I want to develop a form where there are 5 to 7 fields having text box to enter data. Now there is also a calculation field in the same form which will accept formula based output. I want to know How I can put two command button, one for saving data (in another sheet) and one for 'View Report' (from the same sheet used for saving data.

Waiting for your prompt reply.

Thanks,
Sameer Joshi.
Mumbai,[India].

Comment by: Jan Karel Pieterse (15-7-2011 11:35:38) deeplink to this comment

Hi Sameer,

Please ask your question at www.eileenslounge.com

Comment by: Linh (21-9-2011 08:16:25) deeplink to this comment

Hi Jan,
Can Autosafe work with my file with interval more than 6hours, 480 mins???

Comment by: Jan Karel Pieterse (22-9-2011 00:16:41) deeplink to this comment

Hi Linh,

Yes it should work just fine.

Comment by: James (8-10-2011 07:52:15) deeplink to this comment

Hi Jan Karel,

It seems that you forgot to add, in your download list, one of your extremely handy file : ControlLister

In addition, I would like to know if you have upgraded your original solution to handle multipage-userforms ...

Thanks a lot for your very astute solutions.

Comment by: Jan Karel Pieterse (9-10-2011 23:52:52) deeplink to this comment

Hi James,

Thanks for reminding me! The tool does not handle multipages yet (nor frames if I recall correctly), so it needs work.

Comment by: Henry Lee (13-10-2011 08:05:06) deeplink to this comment

Hi Jan,

I just downloaded your utility ExportVBAProject. When I tried to execute it, I get Error 438: Object doesn't support this property or method in modMenu.MenuHandler. Debugging the code, it turns out the problem is in the subroutine InitForm() for ufSelectWB. The reference "oAddin.IsOpen" is valid only for Excel 2010 and I was using Excel 2007. So you should add this requirement to the description for this utility.

Regards,
Henry Lee

Comment by: Jan Karel Pieterse (14-10-2011 01:59:12) deeplink to this comment

HI Henry,

Well spotted. I have updated the code so it works for all versions: Excel 2003 up to Excel 2010.

Comment by: Donald Nelson (28-11-2011 10:08:06) deeplink to this comment

I am new to VB and programming. I can write add-ins and create menus and toolboxes, wrote macros to edit macros, add-ins to customize workbook functionality and more, so you might say I'm at an intermediate level with VB. Is there any way I could see all or some of the NameManager code. I do not want to sell or redistribute your code I just want to see how you sorted the names by external/errors/filter/etc... also when I try to write a macro to delete some names it comes up with errors. How do you handle the errors.

Recommend changing (or allowing user to select where) the menu to be available in the actual names menu of excel in 2003. Insert/Name/Name Manager. Also, I needed to delete names that do not refer to anything. In a post you mentioned to be careful not to delete hidden names that excel uses. Could you provide a list of names that users should not delete? Is it possible to create a menu that snaps to the side of the application similar to how the insert image or thesaurus looks? At work I have multiple monitors but at home I do not. It is semi-inconvenient to view this form window and the workbook at the same time with one monitor.

I am really interested in learning more. Are there any books aside from Excel 2007 VBA Programming For Dummies or any other ways to understand. I visit forums but they don't say how the code really works just try this or that. I would like to understand not just use their suggestions. Should I learn more than VB for manipulating MS Office?

Comment by: Jan Karel Pieterse (29-11-2011 07:02:50) deeplink to this comment

Hi Donald,

Thanks for the suggestions.

Sometimes names cannot be deleted from VBA, these have somehow been corrupted. In newer versions of Excel you can remove them by using the Name Manager dialog that's built-in.

I cannot share the Name Manager code, it is code I developed together with Charles Williams. Hundreds, if not thousands of man-hours have gone into it. Why? To make it absolutely robust in all circumstances. There are many quirks we had to work around to get this all working properly.

You can turn off the system names option so the system names of Excel are not shown and hence not removed.

This function tells you if a name is a system name:

Function IsSysName(sName As String) As Boolean
    If sName Like "*_FilterDatabase" Then
        IsSysName = True
        Exit Function
    End If
    If sName Like "*Print_Area" Then
        IsSysName = True
        Exit Function
    End If
    If sName Like "*Print_Titles" Then
        IsSysName = True
    End If
    If sName Like "*.wvu.*" Then
        IsSysName = True
    End If
    If sName Like "*wrn.*" Then
        IsSysName = True
    End If
    If sName Like "*!Criteria" Then
        IsSysName = True
    End If
End Function

Mind you, it does not know about names inserted by queries.

Comment by: Barry McFarlane (5-1-2012 17:31:00) deeplink to this comment

Having lost several hours of Excel work due to not saving the file correctly, I did a search for autosave tools and found AutoSafe. I have installed this tool and initial testing is good but it would be more useful to me if it had the following options:
1. always make a backup copy at the nominated interval irrespective of activity or skipped saves.
2. option to keep the backup file in the backup directory even if the file is saved correctly (ie don't move it to the recycle bin). The rationale for this is that I often edit files that are on networked drives and these files occasionally get trashed by other people. It would be easier to recover my work by only having to look in the backup directory instead of searching through the recycle bin.
Barry

Comment by: Jan Karel Pieterse (9-1-2012 01:07:32) deeplink to this comment

Hi Barry,

Version 3.2 has option 1 by default.
For option 2, I'd have to create a special version for you. Please send me an email if you want me to quote how much that would cost.

Comment by: andre (19-1-2012 16:09:17) deeplink to this comment

Hi Jan Karel

Does the ChartAnEquation work in excel 2007. I get an error when Trying to use it that says a furmla has invalid references.
Rgds

Andre

Comment by: Jan Karel Pieterse (20-1-2012 08:39:32) deeplink to this comment

Hi Andre,

It did for me some time ago!

What happens if you hit control+shift+alt+F9?

Comment by: Bill Benson (24-2-2012 09:38:14) deeplink to this comment

Jan Karel, hi. I think that Barry McFarlane's request is reasonable and belongs in your official released version. I am not against your stand on revenue, however that one option alone is probably why I am going to write my own add-in. It won't be polished like yours and it will not be for sale or intend to be in competition with what you have produced, which others have raved about. However I do not want something which automatically deletes any of my backups, no matter how many times I save, if that is the correct inference from Barry's comment.

I also want to be sure I can turn it off for certain files on a file by file basis via a convenient toggle. (Some of the programming code I have has to save the file along the way to avoid Excel crashing, and in that situation the backups are not useful to me

Comment by: Jan Karel Pieterse (24-2-2012 11:01:54) deeplink to this comment

Hi Bill,

I see where you're going, but Autosafe is different in that it offers left-over backup copies for recovery when you restart Excel. It would require too much of a rewrite.

Comment by: Tany (13-3-2012 04:36:35) deeplink to this comment

Hi JKP,
If I enter "http://investing.money.msn.com/investments/stock-ratings?symbol=ADM" in IE, then I reach the site.
I I pass this string to below Sub, then I get "1004 This Web query returned no data. To change the query, click OK, click the arrow on the name box in the formula bar, click the name of the external data range for the Web query, right-click the selection, and then click Edit Query." as error trap.
If I go in manual "Edit Web Query" then I get a script error pointing to platform.twitter.com/widgets.js... continue... get this again... continue... then it is possible to "Import".

Strange but true... replacing ADM by KOL... then it works.

Any suggestion ?

Thanks.

T

Sub ExecWebQuery(prmURL As String)
On Error Resume Next

    Sheets("WebQry").Select
    Range("edr").Select
    With Selection.QueryTable
        .Connection = "URL;" & prmURL
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With

Comment by: Jan Karel Pieterse (13-3-2012 06:09:49) deeplink to this comment

Hi Tany,

If I enter that url in IE, the site automatically adds an ampersand after the URL, like so:

http://investing.money.msn.com/investments/stock-ratings?symbol=ADM&

then the script error does not happen.

So the error is site-based and cannot be prevented I'm afraid.

Comment by: gerdami (3-4-2012 01:16:51) deeplink to this comment

Autosafe

Is there a way to disable the automatic update?
Problem is that being behind a firewall proxy, internet credentials are requested and update dialog remains on hold if I use Excel in scheduled tasks.

Comment by: Jan Karel Pieterse (3-4-2012 06:01:43) deeplink to this comment

Hi Gerdami,

As of build 134 you can turn updating off.

Comment by: gerdami (3-4-2012 09:31:27) deeplink to this comment

Hi JKP,
You are really fantastic: you are quickier than Lucky Luke!
Thank you.

Comment by: bhanu kiran (17-4-2012 02:52:27) deeplink to this comment

Hi Jan Kal,

I was looking at your comment on EE and came down to visit your site. You have a excellent tool set.

Thanks for your agreement on the Rackspace cloud drive vs rackspace sharepoint.

regards
bhanu

Comment by: john pyskaty (23-5-2012 10:41:14) deeplink to this comment

MY QUESTION IS , CAN A WEB QUERY FROM YAHOO FINANCE ( HISTORICAL PRICE ) OF A STOCK FOR 10 YEARS OR MORE , WITH A SET DATE OF DAILY , GO TO A PIVOT TABLE IN EXCEL 2010 , WITH OUT GOING FIRST TO A EXCEL SHEET , BECAUSE WHEN I GO TO THE WEB AND QUERY IT TO A EXCEL SPREAD SHEET , ALL OF THE WEB QUERY DOES NOT IMPORT .

THANK YOU
JOHN PYSKATY
E-MAIL JRP381@OPTIMUM.NET
201-666-4418
I AM 70 YEAR OLD AND ALL I KNOW ABOUT EXCEL , I LEARN FROM DVD .

Comment by: Jan Karel Pieterse (23-5-2012 23:13:12) deeplink to this comment

Hi John,

No, unfortunately web queries cannot be tied directly to a pivottable, you have to put them on a worksheet. How many rows of data does the web query return approximately?

Comment by: Ana Maria (23-6-2012 21:34:21) deeplink to this comment

How do I disable (or just temporarily turn off) FollowCellPointer? I find it quite useful when I am working with my file, but during a presentation, I may need to turn it off. I considered "parking" it in the first col/row, but that may be problematic as I go to different cells. I have fussy audiences at times! Thank you!

Comment by: Jan Karel Pieterse (25-6-2012 07:06:08) deeplink to this comment

Hi Ana Maria,

There are some controls to control it available on the add-ins tab of the ribbon. Including an enable and disable button.

Comment by: ganesh (23-7-2012 12:18:38) deeplink to this comment

Sir,

Autosafe VBe is not working on word 2007 kindly guide me.

Comment by: Jan Karel Pieterse (7-8-2012 11:31:37) deeplink to this comment

Hi Ganesh,

I have not tried the tool on Word 2007 yet. One thing though: you have to change a Word security setting to allow access to the VBA project object model. (Word options, Security center).

Comment by: Bruno F (13-9-2012 15:24:01) deeplink to this comment

Dear Jan,

First of all, thank you very much for the applications that you've provided to us.
I'm a user of your great Autosafe addin for excel, and, if you don't mind, I would just like to give you two suggestions to make this add-in even better (if possible):

- the ability to select which of the opened files in excel should be considered whenever a "autosafe cycle" is made (this is specially important when, for example, we're working with big auxiliary excel files sizes that are opened just for reading purposes);

- the inability of autosafe to work while you've excel without any workbook opened (this is noticeable when after some while, when you create a new workbooks,.. it starts with name "Book24.xlsx [this number is just an example..I suppose it should be the number autosafe has already tried to save without any workbook opened])".. this situation randomly caused me some excel crashes.

Please I kindly ask you to not consider this suggestions as some critics to your already great excel addin.

Thank you for support.

Best regards,
Bruno F

Comment by: Jan Karel Pieterse (13-9-2012 17:10:31) deeplink to this comment

Hi Bruno,

Thanks for the suggestions!

Comment by: Mike (18-9-2012 12:29:54) deeplink to this comment

Jan,

Last night I downloaded "WatchOtherCell" and started adapting it to a spreadsheet I have. I appreciate the free application and think it is a great tool, but I couldn't figure out how to expand the range to use in columns beyond Column IV.

I found that changing the column designation in the 'Sub tbxColumn_MouseDown' isn't the answer. Could you please provide some direction?

Thanks, Mike

Comment by: Jan Karel Pieterse (18-9-2012 17:39:59) deeplink to this comment

Hi Mike,

I have adapted the tool so it works in all versions (2003 up to 2010).

Comment by: Mike (20-9-2012 12:45:08) deeplink to this comment

Your tool "WatchOtherCell" was great before the revision to expand its range. Now it is even better! It will now handle extremely large spreadsheets with columns extending past IV.

Thanks, Jan, for the improvements. I appreciate your help adapting the tool for my application.

Comment by: Santhosh (23-9-2012 15:19:31) deeplink to this comment

i have an excel table of 25 columns and more than 2000 rows, from this table I have to create a user form report to another sheet by selected columns of eleven and rows selection in "from" and "to" date wise and selection by month wise(i have a date column in my table) Can u provide me vba code for this

Comment by: Jan Karel Pieterse (24-9-2012 11:41:29) deeplink to this comment

Hi Santhosh,

Best to ask this question at www.eileenslounge.com.

Comment by: Harun Reşit Zafer (29-11-2012 11:04:49) deeplink to this comment

FollowCellPointer is just great. It does what it should do so simple and elegantly. Nothing less, nothing more. Small but very efficient add-in. Thank you.

Comment by: Jan Karel Pieterse (29-11-2012 13:43:35) deeplink to this comment

Hi Harun,

Well thank you!

Comment by: Louis Kirsten (11-12-2012 10:28:05) deeplink to this comment

Jan,
Thanks so much for the equation charting procedure - great tool.

I would like to make a donation but the Paypal option is not fully functional. Do you have an alternative?

Regards,

Comment by: Jan Karel Pieterse (11-12-2012 14:16:33) deeplink to this comment

Hi Louis,

Thanks for offering to donate, I appreciate that.

Can I ask you to make the donation to:

https://www.doctorswithoutborders.org/donate/?

Comment by: Surendran R (4-1-2013 11:53:30) deeplink to this comment

How solve unable to read file excel 2003. Kindly advice.
Regards
Surendran R
Infomation Systems

Comment by: muru (21-1-2013 08:44:52) deeplink to this comment

I have a set of data measured against time. How to calculate the time constant using least curve fitting? Please help

Comment by: Jan Karel Pieterse (22-1-2013 08:51:30) deeplink to this comment

Hi Muru,

Please go to http://www.eileenslounge.com to ask your question.

Comment by: Karen Saliers (18-2-2013 21:37:58) deeplink to this comment

how to adjust settings in autosafe

Comment by: Jan Karel Pieterse (19-2-2013 07:35:40) deeplink to this comment

Hi Karen,

Click the Add-ins tab and look for an entry for Autosafe.

Comment by: Jan Sohn (4-3-2013 14:20:40) deeplink to this comment

Hi Jan
Nice Little tool, but it lacks the opportunity to find parts of an xpath string in the XML-map pane.
Best regards
Jan

Comment by: Jan Karel Pieterse (4-3-2013 14:25:21) deeplink to this comment

Hi Jan,

I take it you are referring to the Flexfind tool?

Comment by: Alain Heremans (13-3-2013 12:30:03) deeplink to this comment

Your treeview control appears really great. Unfortunately the example is VBA Excel based. Do you have a version only based on VBA Access, e.g. using an Access form ?

Comment by: Jan Karel Pieterse (13-3-2013 16:28:43) deeplink to this comment

Hi Alain,

Access forms behave quite different from VBA userforms, so I guess the answer is no :-(

Comment by: Mike (22-3-2013 21:11:03) deeplink to this comment

Hi,

I really like your SetupUtility. My question is that it fails for some users and the error message states that the Caption can't be set...any ideas what that might be attributed to?

Thanks for any direction on this.

Comment by: Jan Karel Pieterse (23-3-2013 19:44:04) deeplink to this comment

Hi Mike,

Perhaps there is something amiss with the two buttons (as that is the only thing the code changes a caption of)?

Comment by: israr (25-5-2013 10:48:58) deeplink to this comment

Dear I am working in an Excel Sheet of 2003 version. i last saved the workbook and than excel starting errors
"The file format does not match ..........." and i lost all my data. Please help me to recover the last saved sheet.

Please help me.

Comment by: Jan Karel Pieterse (25-5-2013 19:49:22) deeplink to this comment

Hi Israr,

please have a look at:

https://jkp-ads.com/articles/corruptfiles.asp

Comment by: Tomi S. (10-8-2013 20:39:51) deeplink to this comment

Hi,
I have a question about AutoSafe.
I'm preparing my massive database spreadsheet with Calculation set to Manual.
After installing AutoSafe I notice that Calculation keeps moving to setting Automatic after few edits or few minutes.
Does the AutoSafe set that option?
How can I avoid that?

BR, Tom

Comment by: Jan Karel Pieterse (11-8-2013 16:55:42) deeplink to this comment

Hi Tom,

It may very well be that Autosafe does that. I'll add it to the list of things to fix. Thanks.

Comment by: Michael (29-8-2013 17:21:55) deeplink to this comment

Hi JPK!

Just a quick question about AutoSafe. Love the product as you are probably aware from previous discussions!

I am noticing some weird activity in which AutoSafe is not running on a schedule appropriately and, as of today, I am not seeing a way to get into the settings via the Add-In Tab on the Ribbon as AutoSafe is no longer there (Screenshot: http://img845.imageshack.us/img845/3228/kyik.jpg )

I am wondering if perhaps another Add-In has messed with it somehow? MicroStrategy and PowerPivot are the most robust and newest things I'm dealing with.

Is there a way to call up the options for AutoSafe without using the Ribbon?

Thanks very much for your excellent products!

Comment by: Jan Karel Pieterse (30-8-2013 12:07:45) deeplink to this comment

Hi Michael,

Perhaps Excel disabled Autosafe (look under Options, Add-ins, click the dropdown and select disabled items).

Comment by: Michael Shallal (30-8-2013 23:37:36) deeplink to this comment

Thanks JKP, I will have a look when I have a chance (it's on a computer issued by a client that I do not have with me at the moment). I noticed yesterday after I posed that AutoSafe was actually working but there were a few peculiar things about it:

(1) The settings (set from way before) were at 13 minutes. For the first 60-90 minutes of opening files in Excel, it backed up my open documents very irregularly (just twice). After that, though, it was like clockwork every 13 minutes. Weird.

(2) I had (and still have) no way of getting to the AutoSafe Settings. I unchecked, and in some cases uninstalled, almost every add-in to try to get AutoSafe to show up in the Add-Ins ribbon, but no matter what configuration I tried, it never showed up. I wished it was like the old version in which it showed up in the File Menu!

I suppose if there was some way I could get to those settings, that'd be ok.

Thanks again!

Comment by: Jan Karel Pieterse (31-8-2013 15:17:45) deeplink to this comment

Hi Michael,

If it is doing backups it must be loaded and working.
Perhaps your toolbar customisation file (yes, Excel 2007-2013 still have that one) is experiencing problems?
Search for a file with the xlb extension. Move it some place else and start Excel.

Comment by: André (5-12-2013 14:15:05) deeplink to this comment

Dear,

There is a way to avoid (or monitoring) an excel file be copied.

For instance, I do prepare some task's excel files to student's fill with some formulas, chart etc..
Then I concerned that some students are just copying the excel file from another one that complete the task, changing the name e sent back as a task done.

So, there is a way to monitor if this is happening? or even prevent that's happens using VBA ?

Thanks a lot, I'm new in VBA script.
Cheers

Comment by: Jan Karel Pieterse (5-12-2013 15:16:29) deeplink to this comment

Hi André,

There is unfortunately no way to prevent that, as your student can easily go to Windows Explorer and make a copy of the file. In such a case, Excel is not even running :-)

Comment by: André (5-12-2013 17:51:44) deeplink to this comment

Hi Jan,

Thank you very much for your attention.

Ok, I understand. But normally they have to open Excel to fill out their names inside a specific cell. When this happens, it is possible to get for instance their MAC number and print out in a hidden cell, that I can verify after ? (I don't know if its possible using VBA).

Thanks again for any tips about it!

Comment by: Jan Karel Pieterse (6-12-2013 12:01:58) deeplink to this comment

Hi André,

Sure, that can be arranged. But you then also have to make sure they enable macro's, otherwise the trick would not work.
I cannot help you with this this month, but you might ask at www.eileenslounge.com.

Comment by: tomo.vujovic@nbs.rs (26-2-2014 12:15:12) deeplink to this comment

Dear,

I have question about bonds yield curve modeling/fitting in Microsoft excel. Do you have some practical example how to do this? I tried with Nelson Siegel Svensson model and results are not bad but is there any other kind how to do that in Microsoft excel.

Comment by: Jan Karel Pieterse (26-2-2014 16:23:37) deeplink to this comment

Hi Tomo,

Unfortunately I know nothing about bonds yield fitting!

Comment by: Elaine (14-3-2014 09:56:37) deeplink to this comment

Hi

I am ubable to use Autosafe. I keep getting a message to enable macros despite enabling all macros.
Can you help please.
Thanks

Comment by: Jan Karel Pieterse (14-3-2014 12:01:27) deeplink to this comment

Hi Elaine,

I think the prblem is that you need to allow access to the VBA object model, which is a specific setting in:
File, Options, Trust Center, Trust Center Settings, Macro options, Allow access to the VBA object model.

Comment by: Elaine (14-3-2014 14:41:32) deeplink to this comment

Hi

Many thanks for your help. Now working! (Just wish I had it earlier this week!!) All safe now.
Elaine

Comment by: Jack (19-3-2014 14:22:35) deeplink to this comment

can i configure autosafe to always create the file in the same name with extension .xls ? example: test.xls

Thank you.

Comment by: Jan Karel Pieterse (21-3-2014 15:40:10) deeplink to this comment

Hi Jack,

No, you can't do that I'm afraid.

Comment by: Antoniu (26-3-2014 13:26:57) deeplink to this comment

Hi Jan

I am trying to use your Treeview classes in a small project (without success till now) maybe you can help me a bit.

So I have an userform1 with frameControl that will keep the treeview and a command button. That botton will start up an userform2 with a text box. On text box I write the name of the root tree node, click the button from userform2, userform2 close and the frame is populated with the root node.

now.

If I repeat procedure I like to keep the first root node created and add the new one etc .... On bellow code the root node is just "renamed".

Please can you help me a bit?

in userform1

Option Explicit
Private WithEvents mcTree As clsTreeView
Public nTxtBox As String
Public k As Integer

Private Sub CommandButton1_Click()
    UserForm2.Show
    MsgBox nTxtBox
    RootNodes
End Sub
Sub RootNodes()
    Dim cRoot As clsNode

    Set mcTree = New clsTreeView
    With mcTree
        k = k + 1
        MsgBox k
        Set .TreeControl = Me.frTreeControl
        .RootButton = True
                Set cRoot = .AddRoot(nTxtBox & k, nTxtBox)

        .Refresh
    End With

End Sub

in userform2

Option Explicit
Private Sub CommandButton1_Click()
    UserForm1.nTxtBox = UserForm2.TextBox1.Text
    Unload Me
End Sub

Comment by: Antoniu (27-3-2014 13:08:26) deeplink to this comment

With Jan's help, I putted like this (and it's working like I am expected):

Jan, thank you!

Sub RootNodes()
    Dim cRoot As clsNode

    k = k + 1
    If mcTree Is Nothing Then
    Set mcTree = New clsTreeView
        With mcTree
            Set .TreeControl = Me.frTreeControl
            .RootButton = True
            .CheckBoxes = True
                    Set cRoot = .AddRoot(nTxtBox & k, nTxtBox)

            .Refresh
        End With
    Else
        With mcTree
            Set .TreeControl = Me.frTreeControl
            .RootButton = True
                    Set cRoot = .AddRoot(nTxtBox & k, nTxtBox)

            .Refresh
        End With
    End If

End Sub

Comment by: Bomino (8-6-2014 02:18:03) deeplink to this comment

Hi,
I would like to use your "ShowTableOnUserform" code in a project; but i was wondering if it is possible not to show the selected range when the macro (demo) is fired. I would like to have just the Userform poping up.
Thank you so much for all the awesome ressources you are so kindly sharing.

Comment by: Jan Karel Pieterse (10-6-2014 08:49:09) deeplink to this comment

Hi Bomino,

You could remove the .Initialise statement from the code, that should stop it from loading the table.

Or you could move the part within Initialise between With and End With to a different sub in the form and call that when needed.

So when do you want data to show up on the form?

Comment by: Bomino (10-6-2014 19:24:53) deeplink to this comment

I think I didn't make myself clear. Apparently things got lost in translation...Lol
When the macro "Demo" is fired, the userform pops up just beside the cells containing the data. I don't want that....I was wondering if it is possible to make the userform show in the region of say A1:B15....
I have a temporary fix: I just hide the cells containing the data.

I am probably still not clear; Bear with please,I am not a native speaker and my English is still not good.
Thanks for your patience

Comment by: Jan Karel Pieterse (10-6-2014 21:05:29) deeplink to this comment

Hi Bomino,

No problem.
You can dictate the position of a userform by setting its left and top position prior to the .Show command.
The hard part is positioning a userform exactly on a specific grid location in Excel.

Comment by: Bomino (10-6-2014 22:17:35) deeplink to this comment

Thank you so much.

Comment by: Bruce Chernoff (14-7-2014 15:32:18) deeplink to this comment

Your link to
https://jkp-ads.com/downloadscript.asp?filename=TreeviewOnAccessForm.zip
is down.

Comment by: Brian Crane (21-7-2014 21:32:47) deeplink to this comment

Hi
How can I disable or indeed delete FollowCell Pointer from my spreadsheets?
Many thanks

Comment by: Jan Karel Pieterse (22-7-2014 21:00:58) deeplink to this comment

Hi Brian,

Open the addins list (File, Options, Click Addins tab, click Go button) and uncheck it in that list.

Comment by: Jan Karel Pieterse (12-8-2014 11:16:33) deeplink to this comment

Hi Bruce,

Thanks for letting me know. I removed the entry since there is a valid download on the treeview line already.

Comment by: ryan (3-9-2014 21:32:42) deeplink to this comment

Sir, my question is:
sheet1 (front end) and sheet2 (back end i.e Actual RawData)
I want to extract part of data from sheet2 of particular column and want to compare/match with typed value(in sheet1), irrespective of data (containing "DIO-" or not) typed in sheet2 . it must TRIM the A1(i.e.upto "10203")data and compare i/p value. (need formula not VBA Code)

ex: in sheet1                         sheet2
                                         A        | B | C
i/p: 10203 ("DIO-" maybe typed/not) 1 DIO-10203 | |
o/p: "yes at cell address"         2     40503 | |
                                     3 ....
                                     4 .....

i tried the formula "RIGHT(a:a,5)" it works on same sheet but not when referenced in other sheet.

thank you for ur time and help.

Comment by: Jan Karel Pieterse (8-9-2014 07:32:28) deeplink to this comment

Hi Ryan,

I suggest you post your question here: www.eileenslounge.com

Comment by: Glenn (20-9-2014 22:34:20) deeplink to this comment

I'm attempting to disable FollowCell Pointer but it doesn't appear in the excel add-ins list. Thoughts? I use Excel 2010.

The reason I want to disable it is because whenever I open an excel sheet, there are residual red lines on the sheet but that don't move when I click on other cells. They're static lines that just sit there. If I open FollowCell Pointer then they're active again, but I only want to use it occasionally.

Comment by: Jan Karel Pieterse (22-9-2014 13:42:57) deeplink to this comment

Hi Glenn,

Odd, I just tried and it removes the arrow as soon as I close the tool

Comment by: Glenn (23-9-2014 23:54:06) deeplink to this comment

And how do you close the tool? Do you mean through the addins list? It doesn't show in the addins list for me.

Any other thoughts on how to remove it? It's basically "stuck" on all my excel sheets now with no way to remove it. Use the VB editor maybe?

Comment by: Jan Karel Pieterse (24-9-2014 10:18:22) deeplink to this comment

Hi Glenn,

Yes, through the add-ins list. You can also disable it from the Add-ins tab of your ribbon

Comment by: jean-pierre degroote (5-10-2014 13:35:44) deeplink to this comment

Hi Jan Karel,

It is just a detail but if you once the time to correct the spelling error in RefTreeAnalyser settings ...

Deafult Key

Comment by: Jan Karel Pieterse (5-10-2014 19:24:33) deeplink to this comment

Hi Jean-Pierre,

Thanks for letting me know!

Comment by: Marius Dreyer (6-10-2014 23:09:59) deeplink to this comment

Hi Jan Karel,

I am experiencing the problem that in Excel 2010 Find and Replace only provides Look in "Formulas" and not "Values".
The VBA code on the web provides a dated form (perhaps from an older version of Excel). Can you please direct me how to reset the options to allow me to select between "Formulas" and "Values"?

Comment by: Jan Karel Pieterse (7-10-2014 09:49:32) deeplink to this comment

Hi Marius,

This is by design and has been the case a very long time: if you are using the Replace option, you can only replace in formulas, not in values.

Comment by: Don (23-10-2014 20:18:08) deeplink to this comment

Hi Jan Karel,

I am using your Autosafe add-in and am wondering if there is a way to get it to save the files (which I have set to auto save every 5 minutes) with a traditional .xls extension instead of the .xls00000 extension. I access the file throughout the day from offsite to see progress and when I access it and try to open it, it doesn't automatically open with Excel because of the unusual extension.

Comment by: Jan Karel Pieterse (27-10-2014 09:46:48) deeplink to this comment

Hi Don,

Not as such, unless you'd be willing to start a paid consulting project for a tailored version?

Comment by: gerdami (27-11-2014 16:37:52) deeplink to this comment

Bug in ribbon version of GoBack

Hi Jan Karel,
I recently upgraded to the ribbon version of GoBack.

When closing a workbook, I got a Run-time error 91: Object variable or With block variable not set.
I clicked on the Debug button...
Error highlighted was

oObject.WindowName = ActiveWindow.Caption

in Sub AddToObjects(oNewObj As Object)

Comment by: Jan Karel Pieterse (27-11-2014 16:46:26) deeplink to this comment

Hi Gerdami,

I seem unable to reproduce that error, what steps do I need to take to make it happen?

Comment by: Ash (17-12-2014 06:53:14) deeplink to this comment

I have been playing around with VBA for about 3 years but I am really still a novice. I found your name and website in a Microsoft website explaining the difficulties in getting a date picker into a Microsoft Excel 2013 file when running Excel 2013 64 bit.

Do you have a post on this topic?

Comment by: Jan Karel Pieterse (17-12-2014 09:04:05) deeplink to this comment

Hi Ash,

Have a look at:
https://sites.google.com/site/e90e50/calendar-control-class

Comment by: Sam Radcliffe (9-1-2015 22:35:28) deeplink to this comment

Hi,

I have a problem with GoBack. If I press alt-ctrl-n when there is no "next' address (I am at the end of the chain), I get run-time error 9 "Subscript out of range". It occurs in the statement ".ListIndex = mlCurrent" When this happens, the GoBack ribbon becomes unusable, the enable/disable button disappears.

Win 7, Excel 2010

Thanks.

Comment by: Jan Karel Pieterse (12-1-2015 11:06:04) deeplink to this comment

Hi Sam,

Thanks for letting me know!

Comment by: Jean -Pierre Degroote (4-2-2015 19:13:10) deeplink to this comment

Hi JK,

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/is-there-a-free-excel-add-in-for-easily-cell/f08589a5-80f1-4293-8b91-b4430502e182

Saw your input in the thread above, had a look to the new version and that undo option is really a good feature. It happen often that I am working with different screens and just start typing in the wrong application.

One thing, I looks as I cannot set the arrow head style. Changing it to triangle, OK, just lines. Back to format, again none.

Regards,

JP (RONSE)

Comment by: Jean -Pierre Degroote (4-2-2015 19:20:04) deeplink to this comment

About followcellpointer, in addition to my previous message.

As you agreed to include it in my own add-in, I showed it to some colleagues and all found it a very useful option.

The only thing I was missing a bit was the option to enable or disable it at startup.

Regards,

JP (RONSE)

Comment by: Jan Karel Pieterse (5-2-2015 06:54:47) deeplink to this comment

Hi JP,

Thanks!

Comment by: Keyvan (7-5-2015 21:35:37) deeplink to this comment

Thanks so much for developing the Autosafe add-in.
Great job

Comment by: Bart Vermolen (29-5-2015 10:32:28) deeplink to this comment

Thanks for nonlinearls.zip!

Comment by: Ali (11-6-2015 14:41:23) deeplink to this comment

i really want to thank you very much for these real professional useful addins - and i wish you could post a tutorial for how can we protect our addins to keep codes in VBA extremely safe.
thank you
Respect

Comment by: Ali (15-6-2015 14:56:23) deeplink to this comment

Hi Jan
i hope you are doing great.
regarding to Autosafe Addin, i actually would like to ask if you are interested into translating it to Arabic , i may do this i wish i can offer anything to your very helpful site.

have a good day

Comment by: Jan Karel Pieterse (15-6-2015 16:17:46) deeplink to this comment

Hi Ali,

Sure, go ahead and edit the translations file and email it to me!

Comment by: Jan Schrijver (7-8-2015 15:40:44) deeplink to this comment

Hi Jan, are there any subjects written in the Dutch laguage or better is there a comparable Dutch site.

KRegards Jan

Comment by: Jan Karel Pieterse (7-8-2015 19:12:50) deeplink to this comment

Hi Jan,

Many of the articles (if not most of them) are also available in Dutch. If a page is also available in Dutch (or in English when you're on a Dutch page), there is a link at the top-right of each page stating "Deze pagina in het Nederlands" or "This page in English" respectively!

Comment by: gerdami (13-8-2015 16:34:39) deeplink to this comment

SheetTools feedback.

I was about to write my own ToC maker when I realized that you did it (better).

However, when I select the "ToC" sheet from the ribbon's drop box, the control does not detect any change and just does nothing. Also, sometimes, the sheet selector leads to a wrong sheet.

Comment by: Jan Karel Pieterse (13-8-2015 17:42:14) deeplink to this comment

Hi Gerdami,

Arguably, perhaps I did not do better? :-)

If you have repro steps for the problems you describe, I'd be glad to look into this.

Comment by: m.susanto (26-9-2015 16:36:59) deeplink to this comment

how do make this code below will be excel add in & showing in ribbon excel as add in...would you help me?

Sub LastCell()
Dim rng As Range
    Set rng = Application.InputBox(Prompt:="Select a cell", Type:=8)
    Set rng = rng.EntireColumn.Find("*", SearchOrder:=xlRows, SearchDirection:=xlPrevious)
    If Not rng Is Nothing Then Application.Goto rng
End Sub

Sub FirstCell()
Dim rng As Range
    Set rng = Application.InputBox(Prompt:="Select a cell", Type:=8)
    Set rng = rng.EntireColumn.Find("*", SearchOrder:=xlRows, SearchDirection:=xlNext)
    If Not rng Is Nothing Then Application.Goto rng
End Sub

would you make it for me, thanks & much appreciated..
i'm using excel 2007 & 2013

please, your add in build send in my email above...

regards...

Comment by: Jan Karel Pieterse (26-9-2015 22:17:28) deeplink to this comment

Hi m.Susanto,

Perhaps this article gives you sufficient ideas?

https://jkp-ads.com/Articles/buildexceladdin.asp

Comment by: sam (1-10-2015 18:37:34) deeplink to this comment

Hi Jan Karel
Re : Quit Excel Add-in

Try the following in Excel 2013
Go to customise the Quick Access Toolbar
Choose all commands
Type E
It will take you to buttons that begin with E
Sroll donw further and there is a button called EXIT
Add it to the QAT
(If you are a keyboarder ALT+F+X works as well)

Comment by: Jan Karel Pieterse (2-10-2015 14:59:46) deeplink to this comment

Hi Sam,

I fell into a trap many many people fall into; 80 percent of all feature requests Microsoft gets for new features for Excel are already in the product. So was this one :-)

Comment by: gerdami (2-11-2015 12:27:18) deeplink to this comment

Feedback on Autosafe.

Just donated!

Suggestion:
When restoring a workbook, relative hyperlinks to other workbooks have a wrong path to C:\Autosafe-excel\, i.e. Autosafe path folder.

Would be great if the routine could simply replace the hyperlink paths to "".

Comment by: Jan Karel Pieterse (2-11-2015 14:26:51) deeplink to this comment

Hi gerdami,

Thank you for both the donation and the suggestion for improvement!

Comment by: nilesh (8-11-2015 08:41:24) deeplink to this comment

i overwrite my file.
how can i backup old data and 3 day before file in excel.
pleas send me email

gilakiya.nilesh@yahoo.com

Comment by: Jan Karel Pieterse (10-11-2015 17:23:20) deeplink to this comment

Hi Nilesh,

Right-click the file and choose Properties. Click the Previous versions tab.

Comment by: gerdami (20-11-2015 12:25:23) deeplink to this comment

Autosafe settings interval reset to 1 min.

I set this to 10' but after a while, it is reset to 1 min.
It happens on 2 different computers:
- Windows7, Excel 2010
- XP, Excel 2003 (do not smile !)
Autosafe 3.5, Build 138.

Comment by: Dennis Allen (23-2-2016 02:19:15) deeplink to this comment

I have developed an Add-in.xlam which loads with Excel. I often make changes to the xlam from the VBE. My question is can I use your Exportvbaproject to export (backup) all the modules in this xlam project?

Comment by: Jan Karel Pieterse (23-2-2016 06:52:55) deeplink to this comment

Hi Dennis,

Yes you can. Note though that this little tool exports all code in a single textfile because I coded the tool to be able to compare two entire VBAProjects with a text compare utility. If you just need to export a VBA project, consider using Code Cleaner by appspro.com.

Comment by: B.Z. (2-3-2016 00:54:36) deeplink to this comment

Hi Jan, I had been trying to understand how to use Solver for months checking one website after another. One example from you was enough to get me out of the woods. Incredible! Thank you so much. I am learning a lot here. Too much to learn.

Comment by: TonyP (7-4-2016 22:54:39) deeplink to this comment

TrustedDocumentManager used to work on my access 2010, but since i reinstalled it i cant see my trusted docs in access.
i can see them in excel, powerpoint, word. is there any way i can get a list of the registry settings/values it looks at so i can make sure they exist?
thanks in advance.

Comment by: Jan Karel Pieterse (13-4-2016 16:05:10) deeplink to this comment

Hi Tony,

Odd, as it works just fine for me.

Comment by: TonyP (15-4-2016 06:03:09) deeplink to this comment

solution found. in office went to trust manager and had them delete the trusted documents. after that it now works correctly and i can see all trusted items.

Comment by: Jeff Carnochan (23-4-2016 17:03:19) deeplink to this comment

I am getting a compile error when I enable the AutosafeVBE addin. It says the code in the xla needs to be updated to 64 bit version. Do you have a 64 bit version or is there a workaround? Many thanks.

Comment by: Jan Karel Pieterse (24-4-2016 15:52:50) deeplink to this comment

Hi Jeff,

Thank you for reminding me, I haven't updated the addin yet!

Comment by: olesya reyenger (7-5-2016 00:08:08) deeplink to this comment

Hello Jan,

Regarding:
"Automatic setup
Open the file called Setup.exe from the zip file and follow instructions."

Is it possible to receive the Setup.exe? (It was not in the zip file I downloaded)

Many thanks!

Comment by: Jan Karel Pieterse (7-5-2016 10:41:37) deeplink to this comment

Hi Olesya,

Which file are you referring to please?

Comment by: olesya reyenger (7-5-2016 15:30:35) deeplink to this comment

Sorry,

It's Autosafe.

Thank you!

Comment by: Jan Karel Pieterse (7-5-2016 15:51:48) deeplink to this comment

Hi Olesya,

Apologies, I removes the setup.exe because many people cannot use that. Simply unzip everything to one folder and then from that folder open "setup autosafe.xls", enable macros and click the buttton.

Comment by: olesya reyenger (7-5-2016 15:55:08) deeplink to this comment

Thank you.

How do you enable macros? I followed the instructions but at the end i receive an error message.

Comment by: Jan Karel Pieterse (11-5-2016 15:57:56) deeplink to this comment

Hi Olesya,

What error do you get?

Comment by: Orlando (18-5-2016 22:42:26) deeplink to this comment

Reference: 'updateanaddin.xls'
I was wondering if you could create a full 'Demo' with 2 files. Let explain:
One updateanaddin.xla or xlam, with a sheet named 'MyData'

One regular workbook xls with a button "Save to Addin" that when you click on it, it will take data from Sheet1 range A1:A100 and save this data in the addin's "Mydata' sheet. The same functionality to backup the existing xla and save the data in the new xla should be the same.
Thank you for sharing the knowledge :)

Comment by: Jan Karel Pieterse (19-5-2016 07:08:27) deeplink to this comment

Hi Orlando,

I'd expect this should be simple, as you can simply use code like this to save the add-in:

Workbooks("MyAddin.xlam").Save

Comment by: Orlando (19-5-2016 22:17:27) deeplink to this comment

Reference: 'updateanaddin.xls'
Hi Jan, thanks for the prompt reply, but a I have a follow up question. The addin that I'm trying to change on the fly, it's shared by many users in the network, therefore it is open as 'Read Only', so I don't think that the command 'Workbooks("MyAddin.xlam").Save' will work, would it??? Any ideas how to overcome this 'Read Only' problem?

Comment by: Jan Karel Pieterse (20-5-2016 20:40:34) deeplink to this comment

Hi Orlando,

I'd change the design and save data to another Excel file (or textfile or whatever is convenient) rather than the add-in. That way you can check for that file being open or not prior to the "save".

Comment by: Olesya (26-5-2016 21:24:11) deeplink to this comment

Greetings,

A silly question regarding Autosafe: I've downloaded it, and I see that the program periodically autosaves when I work in Excel. However, where can I find the files that it saves?

Thank you!

Comment by: Jan Karel Pieterse (26-5-2016 23:37:52) deeplink to this comment

Hi Olesya,

You can find its files in the folder which is set up in settings (Add-ins tab, Autosafe settings).

Comment by: Bill Dowton (28-6-2016 13:20:23) deeplink to this comment

Hi Guys
The Treeview control works great :) and I now have it working (almost) perfectly. Almost as I can't get the icons to the size I require. I've resized them in the frame, changed node height, font size etc. but nothing seems to make any difference. Any ideas?

Comment by: m_susanto (12-8-2016 06:02:13) deeplink to this comment

hi..i am interest use FollowCellPointer your small tool but have problem can't running on Excel 2013,win 64 bit, after installed show statement "The code in this project must be updated for use on 64-bit systems......"

could you help me to make that tool can run & use in Excel 2013 win 64 bit

any help, much appreciated..

m.susanto

Comment by: Jan Karel Pieterse (15-8-2016 10:02:36) deeplink to this comment

Hi Susanto,

I have updated the tool.

Comment by: Paulo (17-8-2016 19:50:16) deeplink to this comment

Hi Orlando,
I'd like to thank you for posting the export-vba-code function. I was looking for any similar code for a log time.
It's great!
Thank you again.
Paulo

Comment by: Jesko (15-11-2016 21:08:38) deeplink to this comment

It was really a lucky day I found your page!
Tried some of the tools and I love them.

Detected an error with app "FollowCellPointer". If I click on "Line Color" in the Add-In, it shows "Run-time error 13: Type Mismatch".

My System: Windows 10Aniversary, 64bit, Excel 2013.

Keep up this great work!

Comment by: Jan Karel Pieterse (17-11-2016 12:02:39) deeplink to this comment

Hi Jesko,

Thanks for bringing this to my attention.

Comment by: Joshua Miers (30-12-2016 20:11:40) deeplink to this comment

Hello,
I have been using the Excel VBD Multiline Search and Replace but I am now getting an error:

Oops, An error has in occurred in Excel VBE Multiline Search and Replace
Error:
Error 50289: Can't perform operation since the project is protected. in FindNext.Form ufMultilineReplace

I have tried uninstalling and reinstalling but still have the same problem. Any suggestions?

Comment by: Jan Karel Pieterse (3-1-2017 11:29:40) deeplink to this comment

Hi Joshua,

Sorry for the belated response. That error normally only occurs if the VBA project you are working on has not been "unlocked" by entering its password. Can you describe exactly what you are doing?

Comment by: Erdem Urasoglu (27-1-2017 09:03:20) deeplink to this comment

Plugin: FollowCellPointer v1.0.011
Problems:
1. It prevents working of Merge&Center function of Excel. Means you cannot merge two cells when the addin enabled.
2. If you're working on a sheet which already has merged cells between rows or columns, when the addin enabled, cell pointer shows more than one column or row because of merged cells. This is the case even the pointer is not directly on those cells but in another cell on the same column or row.
Would be glad if you can correct it, othwerwise it's a great addin though.. Thanks.

Comment by: Erdem Urasoglu (27-1-2017 10:23:23) deeplink to this comment

Regarding the merged cells problem in FollowCellPointer addin, I forgot to add that this problem occurs only when you choose the "Select cells (with undo)" method in format menu. This problem doesn't occur when you choose "Arrows (no undo)" method.

If you can't reproduce the problem on your side, I can easily send you a sample sheet to show you the problem.

Thanks.

Comment by: Jan Karel Pieterse (27-1-2017 17:21:36) deeplink to this comment

Hi Erdem,

I can fully understand that merged cells will cause problems with that method. It is one of the (many) reasons why I recommend NOT to merge cells to begin with.

I'm afraid I am not planning to make any changes to the tool for this.

Comment by: gerdami (7-2-2017 08:31:40) deeplink to this comment

Autosafe - interval reset to 1 minute.

Dear Jan Karel,
This something I reported already some months ago: after a while, the "Interval (min)" setting is reset to 1 minute.

Autosafe, version 3.5.139 (Network version)
Excel 14.0.7173.5000 (32-bit)
Windows 7 Enterprise Service Pack 1

Comment by: Jan Karel Pieterse (7-2-2017 11:47:39) deeplink to this comment

Hi gerdami,

I'm afraid I am unable to reproduce that problem.
The Autosafe tool is designed to:
- Check for user activity (keyboard and mouse)
- WHen the set time interval is passed, and the user has been active in the past 30 seconds, it will postpone the save five times, each time checking again after one minute.
- If it finds that after five times trying you're still busy, it will force an autosave. Then it will change the schedule back to whatever you set it to.

Comment by: gerdami (15-2-2017 11:20:34) deeplink to this comment

Dear Jan Karel,

About this Autosafe problem, it actually happens with the 3 computers I use, basically with the same configuration, i.e. several add-ins and personal.xslb.

I understand that it does not make sense to investigate a lot for a product that you provide for free. Thank you again.

May I suggest that you send me by private email the VBA password so I can try to debug this issue.
Thanks.

Best,
gerdami

Comment by: Jan Karel Pieterse (16-2-2017 10:32:02) deeplink to this comment

Hi Gerdami,

I'm sorry I am not at liberty to share that password.

Comment by: Joe Addams (17-3-2017 13:46:27) deeplink to this comment

Do you maintain the old FlexLink Add-in or know where I can find it?
I use Excel 2010 32-bit on a 64 bit Windows 7 computer and on a Windows 8.1 computer.
Thanks, Joe

Comment by: Jan Karel Pieterse (17-3-2017 16:28:01) deeplink to this comment

Hi Joe,

Doe you mean FlexFind? It is mentioned on this very page.
FindLink is a tool by Bill Manville:
http://www.manville.org.uk/software/index.htm

Comment by: Joe Adams (18-3-2017 02:16:32) deeplink to this comment

Thank you for Bill's site. I was looking for FindLink. It does not show up in Google searches.

Comment by: Michael (6-4-2017 14:10:39) deeplink to this comment

Hello,

For the FollowCellPointer tool, how do I toggle the option to keep the Undo stack? Right now my Undo is not working.

Thanks!

Comment by: Jan Karel Pieterse (6-4-2017 15:58:40) deeplink to this comment

Hi Michael,

It is hidden under the "Format" button :-)

Comment by: Joshua Ramsekp (21-4-2017 22:52:14) deeplink to this comment

AUTOSAFE

Thank you for this invaluable utility.

Recently a "Path/File access error" has been occuring... pressing OK presents several (many) more of the same, ending with a
"System Error &H8000FFF(-2147418113). Catastrophic failure"

It appears that the previously Autosaved file is getting locked & therefore is inaccessible?

Thank you again for the lifesaving addin & for any help on this issue.

Thomas Moore
--
Windows 7, Excel 2000 (9.0.6926 SP-3)
Autosave 3.5, 138

Comment by: Jan Karel Pieterse (9-5-2017 15:47:56) deeplink to this comment

Hi Joshua,

Sorry to hear that you have had issues. If that issue does not go away, try if clearing out the folder Autosafe is using helps. This error sounds as if there is a file in there that does not open properly.

Comment by: Andreas Toth (10-5-2017 00:41:08) deeplink to this comment

FollowCellPointer works OK but there are several side-effects with the two available methods that forces me to disable it and only use it when really necessary even though I wish it could be enabled all the time. The first method falls short due to the undo buffer being killed while the other method falls short because of the inherent issues with highlighting multiple cells (some operations like fill don't work as a consequence, etc). I really wish there was a solution that overcame all of these issues and just worked without side-effects.

Comment by: Jan Karel Pieterse (10-5-2017 09:09:44) deeplink to this comment

Hi Andreas,

I totally agree! I created this small tool more as a proof of concept than as a really useful utility to be honest. If you ever come up with an idea to solve these issues I'd be happy to try to implement it!

Comment by: Roger March (21-8-2017 16:58:46) deeplink to this comment

d/l FollowCellPointer, and feel I need to unload it.
How do I do that?

Roger

Comment by: Jan Karel Pieterse (21-8-2017 17:22:25) deeplink to this comment

Hi Roger,

Just hit alt+t, i to open the add-ins dialog and uncheck it there.

Comment by: Roger March (21-8-2017 18:09:44) deeplink to this comment

hit alt+t, i to open the add-ins dialog and uncheck [ followcellpointer ] does not get rid of it.
Roger

Comment by: Jan Karel Pieterse (22-8-2017 16:09:13) deeplink to this comment

Hi Roger,

It should! What happens precisely?

Comment by: Nenad Stevanović (25-8-2017 08:50:08) deeplink to this comment

I really like idea behind autosafe, but I was unable to run it and give it a try. It gives me error file not found or use by other program.
I'm using Windows 10 Creators Update and Office Pro 2016 64bit

Comment by: Jan Karel Pieterse (25-8-2017 13:12:57) deeplink to this comment

Hi Nenad,

I'm sorry to hear that. Can't you modify its settings either? (llok in the add-ins tab for the settings button)

Comment by: Stan (4-10-2017 21:27:50) deeplink to this comment

Hi I tried installing your Autosafe in the 2010 Excel 64bit version but was not successful. Is it 64bit compatible? If not, would it be easy to make it 64bit compatible?

Comment by: Jan Karel Pieterse (5-10-2017 07:34:00) deeplink to this comment

Hi Stan,

Autosafe should work on 64 bit Office, yes. Which Excel version are you on?

Comment by: Stan (5-10-2017 17:37:23) deeplink to this comment

Hi Jan, I run this version of 2010 Excel: 14.0.4760.1000 (64-bit).
The error message that I get states: Compile Error in hidden module Del2recyc. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application.

Comment by: Jan Karel Pieterse (5-10-2017 18:00:58) deeplink to this comment

Hi Stan,

I just checked on my installation of Office 2010 64 bit (14.0.7188.5002) and did not get that compile error.
Perhaps you need to update your Office?

Comment by: Muhammad Imran Bhatti (27-12-2017 17:45:23) deeplink to this comment

Hi JKP

Hope you are doing well.Today I need your kind guidance regarding your utility CopyVBAProject.I often update my financial book's VBA code and I need to update the copies of accounts assistants across Pakistan. You utility is doing what I need ( to copy the VBProject of my updated financial workbook to other office incharges books) but makes me engaged for about every book to be updated. Its OK for the weekly books as they are only 53 to 60 but when it comes to quarterly (needed at our head office its very tidious to keep sitting and update about 300 + books. Can it be modified to update multiple books with somewhat a batch mode.The structure and format of the financial books is same.Do you have a plan to update this utility in such way in future.That will be very usefull for so many others like me. I need it very much.

Thanks for developing this time saving utility .

Best Regards
Imran Bhatti

Comment by: Jan Karel Pieterse (28-12-2017 12:19:47) deeplink to this comment

Hi Imran,

I wonder why you have to update the code so often in so many files. WOuldn't a change in structure be better? Like moving all of the code out of the financial books and into an add-in.

It will take updating the code to cater for the fact that it now has to work with the active workbook and that you have to make sure the code is allowed to run against the active workbook. But the reward is that you can update one single file per workstation (or if everyone has access to the same network, just one file in one folder).
Or even use a process like this one: https://jkp-ads.com/Articles/UpdateAnAddin.asp

Comment by: Imran Bhatti (10-1-2018 11:02:54) deeplink to this comment

Hi Jan K.P
There are many reasons not creating an addin. One ov'em is it will need to be shared with multiple users across the country and there are many issues with shared Addins.

Comment by: Jan Karel Pieterse (10-1-2018 14:12:35) deeplink to this comment

Hi Imran,

I am not sure what issues there might be with "shared add-ins". My add-ins are used by thousands of people without issues. And some of them update automatically as per the article I referred to above.

Comment by: HW (18-4-2018 07:02:25) deeplink to this comment

Dear Sir or Madam,

Where to set the option to preserve undo function in the FollowCellPointer Macro? Looking forward to your response.

Thank you!

Best,

HW

Comment by: Jan Karel Pieterse (19-4-2018 08:17:24) deeplink to this comment

Hi HW,

The tool has a custom tab in the ribbon called FCP (Follow Cell Pointer)

Comment by: Joshua (19-4-2018 21:18:02) deeplink to this comment

Hi Jan... even after recent, required update to Office 16 (on Win7-64bit) still find AutoSafe one of the best utilities EVER!
Unfortunately, a problem has developed... the add-in occasionally gets confused... on closing Excel, somewhere an ONTIME signal seems to be reopening Excel (no workbook). Closing this workbook simply generates another. Clicking the Add-ins tab in the ribbon shows instances of Autosafe equaling the number of close/reopens.

Comment by: Joshua (19-4-2018 21:19:38) deeplink to this comment

Jan... before sending, wanted to reiterate appreciation for your work. Thank you.

Comment by: Jan Karel Pieterse (23-4-2018 11:24:00) deeplink to this comment

Hi Joshua,

Thank you for letting me know. I can't say I've heard this before, but if I get more people complaining about this I'll look into it for sure!

Comment by: Stanley Kenner (22-6-2018 17:11:17) deeplink to this comment

Dear Sir/Madam

I am using Version 3.5 of Autosafe, build 140 in the latest version of Excel 2016. My operating system is the latest version of Windows 10. Unfortunately, the Autosafe add-in causes my other add-in, ASAP Utilities, to freeze making Excel "crash". Removing Autosafe as an Excel add-in resolves the problem. ASAP is a very popular add-in to Excel. Please let me know if anything can be done to enable both add-ins to be enabled and work correctly.

Many thanks

Comment by: Jan Karel Pieterse (25-6-2018 09:28:51) deeplink to this comment

Hi Stanley,

Thank you for letting me know, much appreciated.
Is it any specific functionality of ASAP utilities that is affected?

Comment by: Stanley Kenner (26-6-2018 16:04:04) deeplink to this comment

Hi

Thanks for getting back to me about my problem of using ASAP Utilities and Autosafe in Excel.

All of Autosafe's functionality is effected. When carrying out any ASAP operation, the first time it works fine. On carrying out the same or other ASAP operation, Excel then hangs. In ASAP's log for example, it states - "waiting for Excel/background processes took 31.80 seconds, continue processing." Its seems as though ASAP is continually waiting for a background process to complete.

Regards Stanley

Comment by: Jan Karel Pieterse (29-6-2018 16:51:52) deeplink to this comment

Hi Stanley,

Strange enough, I did not experience any problem when testing just now. Can you pin-point me to a specific action in ASAP which triggers this for you?

Comment by: Jim Lovejoy (17-12-2018 05:16:21) deeplink to this comment

Hi Jan, I tried the "CatchPasteDemo" with a right-click > Paste, and pasting was allowed with no notice. Is this normal?

Comment by: Jan Karel Pieterse (17-12-2018 07:48:35) deeplink to this comment

Hi Jim,

Well, it shouldn't. But since I wrote this article, parts of the right-click menu customisation have moved to ribbonX so you would have to repurpose the paste buttons on the right-click menu through XML. See: http://rondebruin.nl/win/s2/win006.htm

Comment by: Anita S (18-1-2019 15:51:59) deeplink to this comment

I have FollowCellPointer on my computer and it is listed in the Addins list and I do have it checked. It worked once. It doesn't start automatically, but I would rather turn it on when I need it and not all the time. I assume unchecking the addin, turns it off when I don't need it. Can you tell me how to do this? Also, saving my excel spreadsheet as .xlam messed up the spreadsheet, so it is saved as .xlsx. When saving as a macro file, I lose the data entered in it.
Please send reply by email, if possible. Thanks.

Comment by: Jan Karel Pieterse (2-2-2019 16:23:30) deeplink to this comment

Hi Anita,

It should work every time, not sure what gives. Unfortunately it does not remember whether it has been enabled between sessions of Excel.

Comment by: Bruno Fernandes (6-2-2019 11:09:54) deeplink to this comment

Hello Jan Pieterse,

Just to let you know that I've recently upgraded my OS to Win 10 Pro x64 version 1709 (build 16299.904) and Excel 2016 32bit (16.0.4417.1000) and that from that time I start having a lot of issues with autosafe addin along with ASAP Utilities (v. 7.5.2 32bit), similarly with what happened to the user "Stanley Kenner" (comments above), with excel freezing or even crashing regularly.

With the new autosafe version (build 144), looks like the problems were solved and now everything works much more smoother than before.

Thank you for your support.

Best regards,
Bruno

Comment by: Jan Karel Pieterse (6-2-2019 15:03:33) deeplink to this comment

Hi Bruno,

Thanks for letting me know!

Comment by: Mourad Louha (26-2-2019 17:55:09) deeplink to this comment

Hi Jan,
I tried your tool for comparing tables in my German Excel 2016 Version 1902 Build 11328.20100 with a file having to tables in it - one named Table_1 on sheet 1 and one named Table_2 on sheet 2. Both tables only have one column. Opening your tool shows me both tables in the Combobox. However as soon, as I select a table, your tool throws error 381. The problem does not show up, if I have more than one column in the tables. Clicking on the button (with the 3 points) next to the Comboboxes throws me a compiler error in the module ufSettings. I have a samll suggestion too: the tab order for the right part selects the Index Column 1 first, instead of the list of tables. Maybe it's possible to have the same order as in the left part.
Best,
Mourad

Comment by: Jan Karel Pieterse (26-2-2019 19:10:33) deeplink to this comment

Hi Mourad

Thanks, I'll look into this.

Comment by: Jim Chisholm (16-3-2019 15:30:52) deeplink to this comment

Recently had a need to prevent users from destroying their work by pasting into hidden rows. I quickly realized it wasn't going to be a trivial fix. Your CatchPasteDemo provides valuable information to get me started in the right direction. Thanks.

Comment by: Germán (14-8-2019 17:53:00) deeplink to this comment

VERY GOOD, BUT THE UNDO OPTION DOES NOT WORK.
VERY GOOD, BUT THE UNDO FUNCTIONALITY DOES NOT WORK, IT IS DISABLED.

Comment by: Salim TS (30-9-2019 06:36:00) deeplink to this comment

Hi!

Is there somewhere in your website we could download an outlookbar control??

Thanks.

Comment by: Jan Karel Pieterse (8-10-2019 21:20:00) deeplink to this comment

Hi Salim,

I'm afraid not, I'm not even sure what that is!

Comment by: Pat Haworth (23-10-2019 20:22:00) deeplink to this comment

on your FollowCellPointer add in how do we check the option to maintain the undo stack?

Comment by: Jan Karel Pieterse (24-10-2019 13:27:00) deeplink to this comment

Hi Pat,

I'm sorry, this isn't obvious. On the FCP tab on the ribbon, click the Format button.

Comment by: Greg (6-5-2020 13:05:00) deeplink to this comment

Thanks for Follow The Cell!

I'm using it with the UNDO option - is there a way to style the color? Change the grey from the default?

Thanks, Greg

Comment by: Jan Karel Pieterse (6-5-2020 13:37:00) deeplink to this comment

Hi Greg,

I'm afraid not, all it does is select an entire row and an entire column so you get the default color Excel uses as a background for a selected cell.

Comment by: Michael (18-12-2020 01:43:00) deeplink to this comment

Hi Jan Karel!

Love your Autosafe Add-in! Been using it for like 15 years!

I've come across an error that has destroyed multiple workbooks, rendering them with critical errors that are not repairable or saveable after Autosafe fails. Something in the SaveAsCopy portion.

It just started happening today and I've disabled/re-enabled, updated it (it did need an update), restarted my PC, etc. The second the first AutoSafe timer goes off, Excel is biffed.

Any ideas? I'll just disable it for now.

Thank you so much for your contributions to the community. They are second to none!

Comment by: Jan Karel Pieterse (18-12-2020 15:48:00) deeplink to this comment

Hi Michael,

Odd, I do not see that problem. Is there any chance you could share one of those wrecked workbooks with me? Perhaps even a "before" and an "After" version?

Comment by: Michael (22-12-2020 19:58:00) deeplink to this comment

Apologies for the delay in responding. I didn't receive an alert in my email that you did!

The workbook and Excel (365) in question is on a client machine and the workbook itself is highly sensitive. It also contains the Full Works in terms of Power Query, Data Model, Power Pivot, etc.

Now that I have some breathing room, let me try to re-enable the add-in on that machine and see if it works for other workbooks and if the failure is workbook-specific. I never thought to do that last week when I was buried in it and needing to meet a deadline.

If I do verify an error, shall I send you screenshots or whatever I can to the jkp-ads webmaster address?

Comment by: Jan Karel Pieterse (23-12-2020 11:38:00) deeplink to this comment

Hi Michael,

Unfortunately, my website does not have the possibility to notify you for new replies as all comments are "independent". Your only notification is that your message was posted.

Comment by: Dinesh Gaikwad (6-2-2021 08:02:00) deeplink to this comment

Please explain non linear regression by least suqre method,thanks.

Comment by: Jan Karel Pieterse (6-2-2021 16:36:00) deeplink to this comment

Hi Dinesh,

Please see: https://jkp-ads.com/Articles/leastsquares.asp

Comment by: Craig Stanley (17-6-2021 20:51:00) deeplink to this comment

RefTreeAnalyzer looks like a great tool I can use. Have you given any thought to a visual IF analyzer? I started to write something like that but decided it would take more time than I had. I'd buy a visual IF analyzer if you have one.

Comment by: Jan Karel Pieterse (18-6-2021 11:37:00) deeplink to this comment

Hi Craig,

I'm always interested in ideas for improvement. But what is a "visual IF analyzer" please?

Comment by: Craig Stanley (18-6-2021 20:07:00) deeplink to this comment

A visual IF analyzer tool would let you select a cell with a complex IF statement and would visually break down all the parts in a tree-like structure to make it easier to follow the conditional logic.

Comment by: Jan Karel Pieterse (21-6-2021 11:28:00) deeplink to this comment

Hi Craig,

Check out a tool called "FastExcel V4 Manager Pro" by Charles Williams:

http://sites.fastspring.com/decisionmodels/product/fastexcelspeedtoolsproducts?source=jkpads

It has a formula explorer that does exactly this.

Comment by: Bruce Volkert (28-6-2021 21:13:00) deeplink to this comment

Hi Karl,

I've learned to trust your stuff and appreciate what I've learned from it at various web sites on which you are active. I've gotten interested in working with INI files for a home project I'm working on and got interested in learning something from your kpini class. However, I've been unsuccessful downloading from https://classicvb.net/samples/kpIni/ and can't seem to locate it elsewhere.

I've tried it in Edge and Firefox. Nothing seems to happen. I've turned off my security (Windows Security). Still nothing. There does not appear to be any attempt to download the file.

I've been able to download unrelated files; but, I can't seem to get anything to download from anything I've clicked at classicvb.net.

It seems something may be a miss on the site; or, I'm missing something silly.

Any thoughts?

Comment by: Jan Karel Pieterse (29-6-2021 13:22:00) deeplink to this comment

Hi Bruce,

I'm afraid I do not own that website, so I'm not the right person to ask.

Comment by: Mrs N.N.D. (14-9-2021 01:34:00) deeplink to this comment

Thank you very much for FollowCellPointer! You have made my life easier.:)

Comment by: FrankG (17-9-2021 14:26:00) deeplink to this comment

How would I convert this code from 32 to 64 bit?

Thanks for any help !!

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
'--------------------------------------------------------------
Private Declare Function FindWindow Lib "user32" _
                Alias "FindWindowA" _
             (ByVal lpClassName As String, _
                ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" _
                Alias "SendMessageA" _
             (ByVal hwnd As Long, _
                ByVal wMsg As Long, _
                ByVal wParam As Long, _
                lParam As Any) As Long
Private Declare Function ReleaseCapture Lib "user32" () As Long

Comment by: Jan Karel Pieterse (17-9-2021 15:53:00) deeplink to this comment

Hi Frank,

You should be able to find them here:
https://jkp-ads.com/Articles/apideclarations.asp

Comment by: Pankaj Patel (25-3-2022 16:45:00) deeplink to this comment

for create API Menu in Userform in excel 64 bit
i got compile error in Addressof HookWinProc when
userform initialize

Option Explicit

Public Declare PtrSafe Function CallWindowProc _
    Lib "kernel32" _
        Alias "CallWindowProcA" ( _
            ByVal lpPrevWndFunc As Long, _
            ByVal hwnd As Long, _
            ByVal Msg As Long, _
            ByVal wParam As Long, _
            ByVal lParam As Long) _
         As Long

Private Const WM_COMMAND = &H111
Private Const WM_MENUSELECT As Long = &H11F
Public g_lpMyWndProc As Long
Public Const GWL_WNDPROC = (-4)

Public Function HookWinProc(ByVal hw As Long, ByVal uMsg As Long, _
    ByVal wParam As Long, ByVal lParam As Long) As Long

    If uMsg = WM_COMMAND Then
        DoEvents
        Call RunAPIMNUMacro(g_APIMacro(wParam - IDM_MU))
    End If
    HookWinProc = CallWindowProc(g_lpMyWndProc, hw, uMsg, wParam, lParam)

End Function

Comment by: Jan Karel Pieterse (25-3-2022 16:54:00) deeplink to this comment

Hi Pankaj,

The correct declaration for that API function is:

Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hWnd As LongPtr, ByVal Msg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr

In addition, you will have to change the return type of your function HookWinProc to LongPtr and some of the types of the arguments of that function:

Public Function HookWinProc(ByVal hw As LongPtr, ByVal uMsg As Long, _
ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long

Comment by: TM (6-4-2022 21:03:00) deeplink to this comment

ad TwoListBoxDragDrop 7: It's worth noting that one might encounter an error due to different regional settings of list separators for the ColumnWidths-property with ";" (e.g. for Austria,Germany... like in my case) instead of the usual US-EN "," separator.

Comment by: Jan Karel Pieterse (7-4-2022 10:39:00) deeplink to this comment

Hi TM,

As far as I know, the separator for the columnwidths property does not change for other countries?

Comment by: TM (7-4-2022 19:57:00) deeplink to this comment

Trying to show the UserForm, VBA raised a runtime error -213735271 (80020005). ~~>
Changing for both ListBoxes from `.ColumnWidths = .Width / 2.1 & "," & .Width / 2.1` to a string joined by a semicolon via `.ColumnWidths = .Width / 2.1 & ";" & .Width / 2.1` solved the issue in my case.

I have to correct my assumption of regional differences, as you have to use semicolons in any case and not commata.

MS Help reference (docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/columnwidths-property) specifies:
"To separate column entries, use semicolons (;) as list separators.
In Windows, use the list separator specified in the Regional Settings section of the Windows Control Panel to change this value."

Comment by: Jan Karel Pieterse (7-4-2022 20:17:00) deeplink to this comment

Hi TM,

I think you must make sure that the string contains valid decimal separator too. Not sure if the comma would work. Your code would return a string with the windows decimal separator due to the string conversion. Perhaps you need to convert explicitly using the Str function to get US syntax or using Cstr to get the local string

Comment by: TM (7-4-2022 21:11:00) deeplink to this comment

Hi Jan Karel, thanks for prompt post (as well as any of your interesting articles)

Think you might have misunderstood my post: I do Not plead for an intermediate comma "," as column withs separator. - On the contrary I'm referring to the need to insert the globally valid semicolon ";" between the individual column width entries (which of course have to be entered with a decimal point in VBA code). Hence `width1 & ";" & width2` instead of `width1 & ";" & width2` in the userform's Initialize event procedure.

Comment by: Thomas (14-4-2022 23:41:00) deeplink to this comment

Hi:

A couple of issues that I have been running into with your tools for a while:

Compare 2 Tables, on sheet:"Modified records", the query hangs with "ExternalData_1: Getting Data ...". If I open the PQ Editor , it tells me "Expression.SyntaxError: Invalid identifier."

TableTools: In the App_SheetChange handler, I am getting 1004 , Method Undo' of object '_Application' failed , when you Undo to try and collect the oldname, for the automatic update of all Queries and Datamodel.

Actually, in this instance (but there have been frequent other instances), the 2nd error has been triggered by running the 1st tool.

Since this has haunted me for several years, I am wondering whether there is something awry with my config.

Many thanks for your many Excel tools,
Tom

Comment by: Jan Karel Pieterse (15-4-2022 14:15:00) deeplink to this comment

Hi Thomas,

Can you perhaps share the file that causes the comparetwotables add-in to fail?

I must admit that I have not tested both tools being loaded in the same Excel instance.

Comment by: Ali Mohammadi (30-7-2022 09:05:00) deeplink to this comment

"I am getting a compile error in ExportVBA addin. It says the code needs to be updated to 64 bit version. I Checked the code. You added 32-64 bit declaration for some but not all. I changed codes as below. Although, after modifying API declaration, ExportVBA doesn't extract VBA codes.

Modified codes:

#If VBA7 Then
    Private Declare PtrSafe Function SHGetSpecialFolderLocation _
        Lib ""shell32"" (ByVal hWnd As LongPtr, ByVal nFolder As Long, ppidl As LongPtr) As LongPtr
    Private Declare PtrSafe Function SHGetPathFromIDList _
            Lib ""shell32"" Alias ""SHGetPathFromIDListA"" (ByVal Pidl As LongPtr, ByVal pszPath As String) As LongPtr
    Private Declare PtrSafe Function SetCurrentDirectoryA _
        Lib ""kernel32"" (ByVal lpPathName As String) As Long
    Private Declare PtrSafe Sub CoTaskMemFree Lib ""ole32"" (ByVal pvoid As LongPtr)
#Else
    Private Declare Function SHGetSpecialFolderLocation _
            Lib ""shell32"" (ByVal hWnd As Long,ByVal nFolder As Long, ppidl As Long) As Long
    Private Declare Function SHGetPathFromIDList _
            Lib ""shell32"" Alias ""SHGetPathFromIDListA"" (ByVal Pidl As Long, ByVal pszPath As String) As Long
    Private Declare Function SetCurrentDirectoryA _
            Lib ""kernel32"" (ByVal lpPathName As String) As Long
    Private Declare Sub CoTaskMemFree Lib ""ole32"" (ByVal pvoid As Long)
#End If

Comment by: Jan Karel Pieterse (1-8-2022 11:38:00) deeplink to this comment

Thank you Ali, I have uploaded an updated version today.

Comment by: Alex. M. (17-4-2024 20:18:00) deeplink to this comment

Hello,
I downloaded the autosafe, enabled and configured in Excel 2003, but nothing happen when the time that I set run off.
Any info of what and where to check to make it work?

Comment by: Jan Karel Pieterse (17-4-2024 20:44:00) deeplink to this comment

Hi Alex,

Autosafe waits until you've been in active before doing a save. So to test, dry it to a short time, make a change to the file and then don't touch the system for that time. It should do its work.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.