• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
smozgur

XL2BB - Excel Range to BBCode 2.1

No permission to download
Excel Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2010
  7. 2007
Excel Version (Mac)
  1. 365
Excel 'mini-sheet' in messages - XL2BB

Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual data with formulas and extra information in an Excel-friendly format.

XL2BB captures the cells in the selected range and converts it to a special BB code hash that you can paste into a post. The resulting mini-sheet can be copied and pasted back to an Excel worksheet with formulas and formatting that provides a simple way to exchange sample range between the post and Excel application.

Known XL2BB issues
Please read the following threads about known XL2BB issues and solutions.

Installation
  • Uninstall the previous version of XL2BB if any.
    • Launch Excel, click the File tab, click Options, and then click the Add-Ins category.
    • Find XL2BB add-in in the Add-ins list and get the installation location in the Location column.
    • In the Manage box, click Excel Add-ins, and then click Go.
      The Add-Ins dialog box appears.
    • Deselect the XL2BB add-in in the Available Add-ins box, click OK and quit Excel.
    • Go to the add-in location and delete the xl2bb.xlam file.
    • Continue installing the new version by following the instructions below.
  • Download the add-in.
  • Extract the xl2bb.xlam file from the download and take note of where you locate it.
  • Launch Excel, click the File tab, click Options, and then click the Add-Ins category.
  • In the Manage box, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears.
  • Click the Browse button to locate the add-in, and then click OK.
Note:
If you have difficulties installing the add-in, please take a look at this amazing article from Jon Peltier - Install an Excel Add-in.

Alternative Installation: Excel Startup folder: XLSTART

You can use the startup folder to load Excel add-ins.
  • Uninstall any previous version of XL2BB.
  • Launch Excel, go to VBA (Alt F11), and open Debug / Immediate window (Ctrl + G). Copy and paste the following in the debug window and press Enter.

    print Application.StartupPath

    This will print the XLSTART folder path. Although it would be different for different Excel versions, It looks like the following for Excel 365:

    Windows:
    %AppData%\Microsoft\Excel\XLSTART

    Mac:
    ~/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel

    Copy this path to be used in the next step and quit Excel.
  • Go to the XLSTART folder and copy and paste the add-in file (xl2bb.xlam) here.
Usage

Click on the custom ribbon tab called Xl2bb where you can find Xl2bb settings and action buttons.

Xl2bb Ribbon Tab

Xl2bb Ribbon Tab


Make sure to select the options such as Formulas, Conditional Formatting, Data Validation, Named Ranges, and Lambda Functions you'd like to include with the mini-sheet. The selected options will be used to generate the output and can be changed anytime.

Select additional information

Select additional information

Note:
Since XL2BB v2.1, you can also include the Lambda functions. If you have Lambda functions then XL2BB will ask you to select the functions that you would like to add into the mini-sheet. The Lambda function codes will be displayed in a separate section under the mini-sheet on the board, however, they should be manually created in the transferred worksheet.
13-lambda-functions-png.png

Lambda function Selector


Open your workbook and select the range that you want to include in your question, then click the Mini Sheet button in the Capture Range group.

Copy the selected range as a mini-sheet

Copy the selected range as a mini-sheet


Note:
For easy access, you might consider adding the Mini Sheet command button on the QAT by right-clicking on the command button and select "Add to Quick Access Toolbar".

Add to Quick Access Toolbar

Add to Quick Access Toolbar

Table Only button generates table view without row and column headers and formulas. This is useful if you'd like to provide tabular data only. Table content can be copied and pasted back to a worksheet by using the copy button at the bottom of the table.
Note:
Since XL2BB v1.2.8, you can also use the cell context menu button to capture the selected range to bypass the additional information dialog. Simply right-click on the selected range and click XL2BB Capture Range which is the last context menu command button. This shortcut will use the last used additional information options to create XL2BB code.

Cell context menu button

Cell context menu button

Note:
Since XL2BB v2.0, you can select multiple areas to create separated mini-sheets. This is useful if you'd like to post separate ranges by excluding the unrelated rows and/or columns without having to hide them.

As soon as the following confirmation message shows up, the generated XL2BB code will be saved in the clipboard as ready to be pasted to the post.
Saved in the clipboard

Saved in the clipboard

Note:
You can check the Hide Confirmation Dialog option in the Xlbb ribbon tab to suppress the confirmation message that will be also used as the default choice next time.

Now go to the board and paste the clipboard content right after the question text.

XL2BB code in the message

XL2BB code in the message


Click preview to see the actual mini-sheet.

Message preview

Message preview

Note:
It is important to not modify the auto-generated code to allow us to maintain XL2BB code portability between forum software in the future. Please write your ideas to us instead of altering the code, we will consider adding it as long as the implementation is possible.

Cells containing formulas are indicated by a yellow triangle in the top left corner. Hover the mouse pointer on these cells to see a quick tooltip showing the formula. Click on the cell to show the actual formula in the formula bar below the workbook name. The formula will be also copied to the clipboard with this action.

Formula cells

Formula cells

Note:
Spilling ranges containing dynamic array formulas are also highlighted on mouse move on the spilling ranges as well as indicated as green in the Cell Formulas table.

The Copy to Clipboard button in the top left corner is the reverse XL2BB (BB2XL) action that copies the entire range from the post to the clipboard. The data can then be pasted to the same top-left cell to Excel, allowing to get the same range with elements such as formulas, formatting (not as conditional formatting as in Excel but current formatting as you can see in the post). Only values and formatting can be copied by clicking on the button with the Alt key pressed.
Note:
Array formulas won't be pasted as array formulas, so those formulas must be re-entered by using CTRL + Shift + Enter in Excel. Similarly, Conditional Formatting rules, Named Ranges, Data Validation, and Tables must be re-defined in Excel as well.


BB2XL - Reverse XL2BB

BB2XL - Reverse XL2BB

Note:
Hold Alt key pressed when you click on the Copy to clipboard button to copy only values and formatting. This is useful when you just need to grab data and formatting without formulas.

Sample XL2BB Mini-Sheet

There are two mini-sheets in the following example representing base data and associated reporting ranges in an Excel workbook.
XL2BB v2.1.0 Demo.xlsx
ABCDEFGHIJK
1DateCategorySubCategoryAccountPayeeExpense AmountCategoriesSub CategoriesAccountsPayees
29/4/2023Home/RentLawn MowingDebit CardBest Lawn Mover125.00Car/AutoElectricityCashBest Food
39/5/2023Food/GroceriesRestaurant/Fast foodCredit CardNice Food9.10DepartmentalFurnitureCredit CardBest Lawn Mover
49/5/2023Food/GroceriesGroceriesCredit CardSupermarket23.79Food/GroceriesGas/HeatingDebit CardElectric Company
59/5/2023DepartmentalFurnitureCredit CardFurniture Company401.55Home/RentGasolineFurniture Company
69/6/2023Food/GroceriesGroceriesCredit CardSupermarket126.30UtilitiesGroceriesGas Company
79/7/2023UtilitiesInternetDebit CardPhone Company75.00InternetGas Station
89/7/2023Food/GroceriesGroceriesCredit CardSupermarket48.10Lawn MowingNice Food
99/12/2023UtilitiesGas/HeatingCashGas Company51.86MobilePest Controller
109/17/2023Home/RentPest ControlDebit CardPest Controller127.49Pest ControlPhone Company
119/18/2023Food/GroceriesRestaurant/Fast foodCashBest Food20.91Restaurant/Fast foodSupermarket
129/19/2023Car/AutoGasolineCredit CardGas Station26.41
139/21/2023Car/AutoGasolineCredit CardGas Station17.16
149/22/2023UtilitiesMobileDebit CardPhone Company99.13
159/25/2023UtilitiesElectricityDebit CardElectric Company82.75
16Total1234.56
Sept 2023 Expenses
Cell Formulas
RangeFormula
F16F16=SUBTOTAL(109,'Sept 2023 Expenses'!$F$2:$F$15)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F14Cell Value>100textNO
F2:F14Cell Value<20textNO
Cells with Data Validation
CellAllowCriteria
B2:B15List=list_category
C2:C15List=list_subcategory
D2:D15List=list_account
E2:E15List=list_payee

Table Only Output
DateCategorySubCategoryAccountPayeeExpense AmountCategoriesSub CategoriesAccountsPayees
9/4/2023Home/RentLawn MowingDebit CardBest Lawn Mover125.00Car/AutoElectricityCashBest Food
9/5/2023Food/GroceriesRestaurant/Fast foodCredit CardNice Food9.10DepartmentalFurnitureCredit CardBest Lawn Mover
9/5/2023Food/GroceriesGroceriesCredit CardSupermarket23.79Food/GroceriesGas/HeatingDebit CardElectric Company
9/5/2023DepartmentalFurnitureCredit CardFurniture Company401.55Home/RentGasolineFurniture Company
9/6/2023Food/GroceriesGroceriesCredit CardSupermarket126.30UtilitiesGroceriesGas Company
9/7/2023UtilitiesInternetDebit CardPhone Company75.00InternetGas Station
9/7/2023Food/GroceriesGroceriesCredit CardSupermarket48.10Lawn MowingNice Food
9/12/2023UtilitiesGas/HeatingCashGas Company51.86MobilePest Controller
9/17/2023Home/RentPest ControlDebit CardPest Controller127.49Pest ControlPhone Company
9/18/2023Food/GroceriesRestaurant/Fast foodCashBest Food20.91Restaurant/Fast foodSupermarket
9/19/2023Car/AutoGasolineCredit CardGas Station26.41
9/21/2023Car/AutoGasolineCredit CardGas Station17.16
9/22/2023UtilitiesMobileDebit CardPhone Company99.13
9/25/2023UtilitiesElectricityDebit CardElectric Company82.75
Total1234.56

Watch Video
Watch the video for XL2BB instructions.
(Thanks to Bill Jelen for creating this video!)
Add-in Short Name
XL2BB
Author
smozgur
Downloads
12,258
Views
67,125
First release
Last update

Ratings

4.14 star(s) 14 ratings

More Excel articles from smozgur

Latest updates

  1. v2.1 Bug fixes, changes, and new features

    Bug fixes: Named ranges in hidden or protected worksheets cause error - Fixed Data Table -...
  2. v2.0 - User interface update and multiple range selection feature

    New features: Multiple areas can be selected in the same worksheet to create separated...
  3. v1.2.9 - Bug fixes

    Bug fixes: Fixed Excel 2007 incompatibility - Restored the XL2BB buttons disappeared in v1.2.8...

Latest reviews

Brilliant, it's part of what sets this forum apart from others.
smozgur
smozgur
Thanks for the feedback, @Georgiboy!
thanks for this new release. have a nice WE
smozgur
smozgur
Thanks for the feedback!
I cannot install the macro as it says it is blocked for security reason. I read the Peltier article and it showed going to the properties of the xlam file and unblocking. However, when i rt. click the file and go to properties, my properties dialog does not look at all like the one shown. It has only one tab, "General" (not Security, Details, and Previous versions). And the "General tab" does not have the verbiage to unblock as shown in the article. Any suggestions as to how to unblock and install?
I have it installed but it does not show when I open up Excel and when I try to add it as an Add On nothing happens...have I missed a trick?
smozgur
smozgur
After unzipping the xl2bb.xlam file, right click on it and Properties->General->Security section at the bottom, and uncheck the Unblock checkbox if it is checked already. Then follow the instructions in the Overview page at the top of this page. If it still doesn't work in your computer, then try the alternative installation with XLSTART folder (which actually works 100%).

Also, this is the Review area that is supposed to contain reviews, so I suggest asking for help in the Discussion section instead. In fact, there are many other questions and solutions like yours over there.
Very helpful to grab data.
smozgur
smozgur
Thanks for the feedback.
I dont think I have ever had so much difficulty trying to paste an image or download an add-in. With the exhaustive instructions here to add the xl2bb I guess I should have been an computer programer.
smozgur
smozgur
I understand that you only use excel on what you consider a basic level as you stated in a question you asked but sorry to hear that the standard add-in installation instructions didn't work for you easily. You could still ask for help installing the add-in in the discussions on this topic, and I am sure one of the other members who downloaded and installed it would help you quickly.

Pasting an image on the other hand could be done with Ctrl + V keys or right-click in the editor and selecting the command "Paste" in the context menu once you take the screenshot of the image from the original source.
It just works every time. Effortless. Thank you.
Can we also capture actual Result(value) and Cell Number Formats?

For example, cell A1 has formula '=1500*0.15784' where the result value will be '236.76' and cell number format is '#,##0', therefore the result displayed on the table is '237'.
Resolved the installation issue and the Add-In is now working great...really a nice and effective Add-In product...keep up the great work!
smozgur
smozgur
Glad it is up and running now. Thanks for the feedback!
Unfortunately it doesn't seem to be working with Excel for MAC. I get the following error:

Run-time error '1004': Method 'Type' of object 'Validation' failed.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top