I was recently asked this question – “How can I make my workbook read-only?”. I gave the person an answer. However, the real question they were asking turned out to be “How can I make my workbook read-only for everybody but me”. In this post, we will explore some of the options available. Save the … Read more
My first experience of Bullet Charts came from reading Information Dashboard Design by Stephen Few in 2007. This was the year I started to get serious with dashboards. Initially, to create Bullet Charts I used the 3 stacked bar/column charts approach. Later, I moved to using the two-axis stacked column where possible, but in general, … Read more
In a Macro or Add-in there may be times when you require the user to select a file. It is possible to use the standard FileDialog box for this purpose. The following code is intended to provide the most common settings which you can amend to meet your needs. Open the file DialogBox for selecting … Read more
There are a lot of circumstances where you need a Macro to run when a workbook is opened. It could be one of the following: Show a user form which requires input Activate a specific range or object To run a process which updates the data Check the username of the person accessing the file … Read more
Named Ranges are a very useful feature of Excel, but a lot of users do not know why or how to use them. This post is not about how to use Named Ranges, but specifically how to create, delete and manage Named Ranges automatically with VBA. Create Named Ranges Named Ranges can be created at … Read more
A couple of weeks ago we were preparing for a presentation. Much of the presentation is constructed from linked Excel worksheets into PowerPoint. This technique is great because the presentation automatically updates each time the Excel worksheets change. In the presentation, there are also some PowerPoint tables in the default blue striped layout. Then came … Read more
Manipulating ranges and cells is one of the most common actions in VBA. You can use the following to either learn from or just to copy and paste into your own code. Referencing ranges & cells from the worksheet ‘Reference range by address Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).Range(“A1:D4”).[Other properties and actions] ‘Reference cells by row and column – Cells(Row,Column) … Read more
Working with workbooks is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Contents: Referencing workbooks by name Referencing workbooks by their status Referencing workbooks by when they were opened Assign a workbook to a variable Create new workbooks Opening workbooks Counting … Read more
Working with worksheets is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Referencing worksheets from the workbook ‘Reference a worksheet in another workbook Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet in the same workbook as the VBA code ThisWorkbook.Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet … Read more
PDF files are pretty much the norm for distributing reports these days. They provide a nice easy way to collate documents from different sources for distribution. However, once a document is in a PDF format getting that information back into a usable form is a nightmare. If we try to copy and paste a data table … Read more