Excel VBA Tips: Stop Users from Adding Sheets Do you have a spreadsheet that you want to stop users from adding sheets to? Of course, you could lock the workbook using the “protect workbook” feature and ensuring that the protect structure is selected. However, say that you need to protect the workbook via vba. Place…
Read More
Excel VBA Tips: Get Shape Properties Lately, I have been working with shapes in excel – moving, creating, making them invisible and resizing them. This seems easy, but because excel codes the location of an object from the top left of cell A1, it makes it very difficult to work out exactly where the object…
Read More
Excel VBA Tips: Convert Number to Text I have covered how to do this using worksheet formulae, but it can also be done using vba. If your variable (that is a number) is called Number, to convert this to text, use the following code: Number = CStr(Number) It will now be stored in text format.…
Read More
Excel VBA Tips: Closing All Non-Active Workbooks If you ever need to close off all the workbooks you have open in the background but don’t want to have to navigate to each one, this code is extremely useful. Public Sub CloseAllInactive() Dim Wb As Workbook Dim AWb As String AWb = ActiveWorkbook.Name ‘Your code here,…
Read More
Excel VBA Tips: Large concatenation OR concatenating multiple ranges One of the useful worksheet functions in excel is being able to concatenate cells into a single cell. However, if you try to concatenate a large number of cells or a multiple ranges, it can become very problematic. However, after searching today, I came across this…
Read More
Excel VBA Tips: Selecting the Default Printer in VBA I had created a document that is to be printed by multiple people in different locations. However, when pressing the print button that I created on the worksheet, I needed to ensure that the default printer for each location was selected. This can be done with…
Read More
Excel VBA Tips: Running macros using hyperlinks At work today I had to create a workbook that provided links between different sheets within a workbook and turned on a filter. However, I didn’t want to use buttons, as it would be become extremely cluttered. I discovered that you can run macros using hyperlinks, so commandbuttons…
Read More
Excel Worksheet Tips: Creating Navigation Buttons In a previous excel post, I explained how to create hyperlinks. Extending from this, you can link this ability with some simple VBA to create navigation buttons, similar to ones that you would find on websites. 1. Firstly, click on the developer tab. 2. Click on the “Insert” icon.…
Read More
Excel VBA Tips: Introduction Within excel, there are two sides to the program. The “front end” is the worksheet area where data is entered and the “back end” is the visual basic editor side. There are many different things that excel can do to help save time and allow you to become more productive. Why…
Read More
SUBMEG 