In this post we learn how to protect all selected workbooks in Excel using VBA macros. The macro protects the workbook structure and the contents of each worksheet with a password. Alternatively, or additionally, another line of code can be added to encrypt the file with that password. The macro initially asks the user to set a password, and then displays a dialog to select the files. The same code can easily be adapted to unprotect the workbooks with a password.
In this post we see how to import data from Google Sheets in Excel using VBA macros. The macro uses Excel’s web query functionality to import the Google spreadsheet as a query table. The principle is similar to what we have seen in a previous post (Add Web Query To Worksheet). The Google spreadsheet to be imported needs to be public and accessed through a modification of the original link along with the key of the spreadsheet.
In this post we learn how to add a chart to show the data in the selected row using Excel VBA macros. We add a hyperlink to each row first, which triggers the procedure to show the chart. Thus, the macro consists of two procedures, one to add the hyperlinks, and another to add a chart every time a hyperlink is clicked. Additionally, the macro requires two event procedures to allow following the hyperlink, and to remove the chart when any other cell is selected. That makes the chart show up for each selected row each time.
In this post we see how to sort and merge a group of cells with the same value in Excel using VBA macros. Selected values are sorted in ascending order, as per the value in column A first, and then column B. Then, the groups of contiguous cells with the same value are merged.
We have already seen how to add buttons to a specific range or cell in
Excel using VBA macros (Add Buttons To Fit Selection). In this post, we are going to add
buttons to each row with data in a worksheet, and assign another macro to each
of them to delete the respective row when clicked. Thus, there is a first macro
to add the buttons, and a second macro to delete rows.
In this post we see how to compare two Excel workbooks with VBA macros. The macro checks if the number of worksheets matches, then compares the worksheet names and the contents in the sheets with the same name (this last piece of code has been covered in this other post: Compare Two Worksheets). Whenever there is a mismatch, it highlights the sheet or cells with different values in yellow, and displays a message with cell and worksheet (this could also be copied to a separate sheet instead). Note that the macro compares the workbook with the macro (ThisWorkbook) and a second open workbook. Optionally, a dialog to open the second file, or even the two workbooks could be implemented at the start if need be.
In this post we learn how to copy/paste charts from Excel to PowerPoint using VBA macros. The macro copies all embedded charts in a worksheet, and pastes each of them in a separate slide in PowerPoint as a picture.
In a previous post we saw how to import data from a webpage into a web query table in Excel using VBA macros. In this post, we see how to update or refresh an existing web query table when the file opens. The macro updates the COVID numbers for different countries as per the table in the worldometers website every time the file is open. That could be changed to update the number when a button is clicked, or using any other trigger.
In a previous post we saw how to insert pictures from the web in Excel using VBA macros. In this post we see how to insert pictures specifically from Google Drive, which requires the Google Drive view path and the picture sharing ID. The picture will be inserted in the selected range and resized to fit in accordingly.
In a previous post we have seen how to autofill table gaps in Excel using VBA macros. In this post we see another way to do it with less code. This macro example uses the CurrentRegion property to select the table, the SpecialCells property to target the gaps only, and the Offset property to get the value to be filled.
In this post we learn how to resize all charts in the active Excel worksheet at once using VBA macros. The macro also places each chart in a particular position relative to the previous chart, starting from the top left corner of the worksheet. The size of the charts can easily be adjusted changing the values for the Width and Height properties.
In this post we see how to split text into columns in Excel using VBA macros. The macro splits the text in one column into as many columns as dictated by the text separator (a comma, space, etc). In this example, we use the TextToColumns method of the Range object. But this can also be achieved using the Split VBA function to create an array and then write into columns.
In this post we learn to insert pictures from the web in Excel using VBA macros. The macro inserts the picture in a given range and resizes the picture accordingly, so mind to select an appropriate range. Otherwise, specific position coordinates for the picture can be set instead. We can also decide whether to insert the source information or just make an independent copy of the picture.
Sometimes we need to manually create a table with many repeating terms and is time-consuming to use Excel’s AutoFill/FillDown or Go To Special features to fill the gaps. We can easily do that with Excel VBA. This macro example fills table gaps with values above within each column in the selected range.
We often need to gather data spread across
several Excel workbooks. The following VBA macro combines all open workbooks
into just one. The final number of worksheets will be the sum of sheets across
all open workbooks. We may want to add code to close the workbooks when done; we
can do that simply adding wb.Close within the IF statement.
In this macro example, we compare two worksheets with data from two different versions of the same data set. Thus, we can see if there are any changes in version 2 compared to version 1. The changes are highlighted in yellow.
We can integrate Excel with other Microsoft Office applications using VBA. In this post we are going to see how to send an email from Excel (via Outlook) using VBA macros. You need to have Microsoft Outlook installed and configured with a mail account for the macro to work.
In a previous post, we have seen how to get a list of unique entries in Excel using VBA macros. Now we are going to add a validation list with available unique entries to the selected range using Excel VBA.
This macro example separates sheets into new workbooks, keeping the original workbook. Each new workbook takes the name of the sheet and contains one single sheet with its original contents.
Excel allows to easily import data from a webpage with the web query functionality. This example shows a simple routine to add a web query and import all tables from a particular website into the active worksheet.
