create a button on spreadsheet

Question

Hi

I want to create a button on the spreadsheet, which I can press to "refresh all" rather than having to go to Data, "refresh all" all the time?

Regards

Stephen

Answer 1

You can certainly create a button in Excel 365 that triggers the "Refresh All" action without having to go to the Data tab each time. This can be achieved using Excel's Developer tab and a simple macro. Here's how:

1. Show the Developer Tab: If the Developer tab is not visible in your Excel ribbon, you need to enable it:
   • Click on "File" in the ribbon.
   • Go to "Options."
   • In the Excel Options dialog, select "Customize Ribbon" on the left.
   • In the right column, check the box next to "Developer," then click "OK."
2. Create a Button:
   • Go to the Developer tab in the ribbon.
   • Click on the "Insert" dropdown in the "Controls" group.
   • Choose a button from the ActiveX Controls section. Draw the button on your worksheet.
3. Assign a Macro:
   • Right-click the button you created and select "Properties."
   • In the "Properties" window, find the "Caption" property and give your button a name like "Refresh All."
   • Find the "Name" property and give your button a unique name (e.g., "btnRefreshAll").
   • Close the "Properties" window.
   • Right-click the button again and select "View Code."
   • In the VBA editor that opens, you'll see a code window. Paste the following code into that window: Sub RefreshAllData() ActiveWorkbook.RefreshAll End Sub
   This code defines a macro called RefreshAllData that will refresh all data connections in the active workbook.
4. Close the VBA Editor:
   • Close the VBA editor to go back to your worksheet.
5. Link Button to Macro:
   • Right-click the button and select "Properties" again.
   • Find the "OnAction" property and enter the macro name you created: RefreshAllData.
   • Close the "Properties" window.
6. Test the Button:
   • Click the button you created to test it. It should trigger the "Refresh All" action for your data connections.

Now you have a button on your Excel worksheet that you can click to refresh all data connections without navigating through menus.

Remember that Excel's interface and features may change over time, so the steps might vary slightly depending on the version and updates.

Answer 2

1. Link Button to Macro:
   • Right-click the button and select "Properties" again.
   • Find the "OnAction" property and enter the macro name you created: RefreshAllData.
   • Close the "Properties" window.

I cannot find the onaction property?

Answer 3

If you're unable to find the "OnAction" property in the Properties window of the button, you can still assign a macro to the button using a different approach.

Here's an alternative method:

1. Create the Button:
   • Go to the Developer tab in the ribbon.
   • Click on the "Insert" dropdown in the "Controls" group.
   • Choose a button from the ActiveX Controls section. Draw the button on your worksheet.
2. Assign a Macro:
   • Right-click the button you created and select "Assign Macro."
   • In the "Assign Macro" dialog, click the "New" button to create a new macro.
3. Write the Macro:
   • In the VBA editor that opens, you'll see a code window. Paste the following code into that window: Sub RefreshAllData() ActiveWorkbook.RefreshAll End Sub
4. Save and Close the VBA Editor:
   • Close the VBA editor and return to your worksheet.
5. Link Button to Macro:
   • In the "Assign Macro" dialog, you should now see the macro you created: RefreshAllData. Select it and click "OK."
6. Test the Button:
   • Click the button you created to test it. It should trigger the "Refresh All" action for your data connections.

By creating the macro first and then assigning it to the button, you should be able to link the button to the macro without using the "OnAction" property explicitly.

If you're still having trouble, it's possible that the interface has changed in your specific version of Excel. In that case, you might consider referring to the official Microsoft documentation or seeking help from Microsoft's support resources.

Answer 4

Why not just add the Refresh All icon to the Quick-Access-Toolbar? Or learn the keyboard shortcut (also shown in the picture below).