ExcelDashboardWidgets

Every time I try to copy/paste my widget from the original sheet to the new sheet it locks up both sheets and forces a restart of Excel. Any advice?

Hi schultesnj

Apologies for the delay in replying to your question - we've actually just got back from vacation.

What you are describing sounds like strange behavior for Excel - especially as you are only copying and pasting shape objects from one spreadsheet into another and Excel is designed to handle far more complicated operations than this!

There's actually a small tutorial on copying widgets that has been posted on the knowledge base page..

https://www.exceldashboardwidgets.com/k ... -base.html

We also took a few moments to make a short YouTube video to help you out here..



Hope this helps and please feel free to get back to us if anything is not clear or you have more questions.

Warmest regards

DashboardWidgets

This is exactly what I'm having troubles with. My current Excel version doesn't support this. Are there any workaround by any chance?

Hello, I have been having a similar issue with copying and pasting the widgets, and the tutorials listed do not help. I can copy a widget from the original spreadsheet into a new spreadsheet and work on it just fine, but after saving the spreadsheet into OneDrive and reopening it, Excel promptly crashes and is unable to open the spreadsheet. The spreadsheet becomes corrupted and can never be opened.

I have tried only having the widget in the new spreadsheet, keeping source format, using destination format; and I have also tried deleting all the formulas that lead to outside sources in hopes that that would fix the issue, but it still nonetheless becomes corrupted after saving.

Please help me resolve this issue.

Thanks!

twest wrote:Hello, I have been having a similar issue with copying and pasting the widgets, and the tutorials listed do not help. I can copy a widget from the original spreadsheet into a new spreadsheet and work on it just fine, but after saving the spreadsheet into OneDrive and reopening it, Excel promptly crashes and is unable to open the spreadsheet. The spreadsheet becomes corrupted and can never be opened.

I have tried only having the widget in the new spreadsheet, keeping source format, using destination format; and I have also tried deleting all the formulas that lead to outside sources in hopes that that would fix the issue, but it still nonetheless becomes corrupted after saving.

Please help me resolve this issue.

Thanks!

The only way I've found around this is to take a copy of the original spreadsheet with the widgets and delete all the widgets except the ones I will use on the Widget Showcase Page.

Edit: Upon more trial and error, it appears that the act of copying and pasting a widget causes the destination spreadsheet to become broken.

Hi twest

Sorry to hear that you are having problems.

We have been selling our dashboard spreadsheets for five years and have not heard of this particular problem.

The excel widgets are made up of common native Excel objects (graphs, text boxes, shapes) laid on top of each other - so you should not have problems copying from one spreadsheet to another.

The only advice we can give us to pick a widget and attempt to copy the elements to a new spreadsheet one by one until you identify which element is giving the problem. The easiest way to do this is to use the SELECTION PANE (RIBBON > HOME > FIND & SELECT > SELECTION PANE). If it helps we can make a small video showing the process.

Hope this helps.

Regards

DashboardWidgets

Thank you for the response! I decided to try coping one element at a time in order to help locate what the issue may be. I have written out a log of the actions I took to resolve this issue. Should you want to just see the results, feel free to skip past the "Trial and Error" section to the "Results" section at the bottom of the post. Please note that copying and pasting from inside the original document does not result in the document crashing upon reopening, only when it is copied from the original to a different workbook.

Trial And Error
Here is a log of what happened to each thing I copied. The Widget in question is Widget B) Circular Dial Widget Type #1 (Light Version). Each trial was performed by having the original widget workbook open, opening a blank, unchanged workbook, copying and pasting from the original workbook to the new workbook using Ctrl+C and Ctrl+V, saving on the desktop and closing the new workbook, then reopening the new workbook.

• b) Light Circular Dial Widget Type #1 (Crash)
• Circular Dial1 Title (Crash)
• Circular Dial1 % Value (Crash)
• Circular Dial1 Total Value (Crash)
• Circular Dial1 Main Value (Crash)
• Circular Dial1 LightBlue Circle (Works)
• Circular Dial1 Donut Chart (Crash)
• Circular Dial1 Units (Crash)
• Light Circular Dial1 Background Rectangle (Works)

Upon closer inspection, each of these failed elements are linked to another place inside the workbook, so this time I repeated what I did before, but after pasting the elements with formulas into the new workbook, I deleted the formula before saving and closing. Here are the results:

• Circular Dial1 Title (Crash)
• Circular Dial1 % Value (Crash)
• Circular Dial1 Total Value (Crash)
• Circular Dial1 Main Value (Crash)
• Circular Dial1 Donut Chart (Crash)
• Circular Dial1 Units (Crash)

Again, they all crash. I try this once more, except I delete the formulas in the original workbook first before copying the element over to the new workbook. Here are the results:

• Circular Dial1 Title (Works)
• Circular Dial1 % Value (Works)
• Circular Dial1 Total Value (Works)
• Circular Dial1 Main Value (Works)
• Circular Dial1 Donut Chart (Works)
• Circular Dial1 Units (Works)

This is good news! By deleting the formula before copying it, I was able to move each individual element. After all the formulas had been deleted, I copied and pasted the entire widget as a whole, and I was able to open the workbook. The results of these trials have shown that the issue lies within the formula itself and not the actual widget or elements.

I questioned that perhaps the real problem inside the formula is where the information is being linked from. Since I use OneDrive at my company, I had the original workbook saved on there. With this in mind, I performed another set of trials where I had the original workbook either on the local desktop or on the shared OneDrive folder. The new workbook will follow the same rules. I copied the entire Widget B) Circular Dial Widget Type #1 (Light Version) from the Original to the New without modifying any of its elements. Here are the results (From=Original. To=New):

• From OneDrive - To OneDrive (Crash)
• From OneDrive - To Desktop (Crash)
• From Desktop - To Desktop (Works)
• From Desktop - To OneDrive (Works)

It appears that the formula does work when it is copied from the Desktop instead of OneDrive, which leads me to think that perhaps it is either the link to OneDrive or that the original information was not stored locally that was causing the spreadsheet to crash. I tested this theory by opening the "From Desktop - To OneDrive" workbook from another computer that did not have the link's source saved locally, and the results were successful!

I downloaded the original workbook onto a different computer and copied and pasted a widget from that computer onto OneDrive, and I was still able to view the workbook, which is excellent!

Results

• The widget is not at fault.
• The formula causes the issue when copying and pasting.
• This issue is only caused when the widget is copied from OneDrive.
• You can still copy from OneDrive, but in order to do so, you will have to delete all the formulas in the original workbook, copy to the new workbook, then rebuild the formulas.

I hope this information helps you and your company in the future should this problem ever arise again!

Hi twest

Thanks for this very comprehensive information!

The biggest issue with OneDrive is that the current online version of Excel does not support text boxes which we unfortunately rely on rather heavily for positioning text within the widgets. Our tests have shown that it is the textboxes that cause the spreadsheets to fail - all the other Excel elements (graphs, shapes, images) work fine. We should add that the desktop version work perfectly though - the problems only appear if the widgets are used online.

For your information, we did recreate a number of the widgets so that they could be used online with OneDrive (i.e using cells to display numerical titles and numerical information instead of textboxes) however this heavily restricted how users could lay out the widgets to form a dashboard (especially when mixing types of widgets within the same dashboard) - so we didn't ever release this as a product to the general public.

Thanks once again for your contributions and insights - they are highly appreciated!

DashboardWidgets

Excel recommends charts on the “Insert” tab of the ribbon at the top of Excel. Take your chart and copy and paste it into a separate worksheet. You can do this for each of the charts you're building for your KPI dashboard in Excel. The new sheet where you place these charts will become your dashboard.