Youtube.com/VbaA2z (subscribe for new videos every weekend!): April 2020

VBA UI UX-3: Advanced Autocomplete Suggestions, Predictive search for Excel UserForm

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime?

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today.

https://material.io/

https://icons8.com/icons/

VBA UI UX-1: Build UI using Excel UserForm, Fields with watermark, transparent icons & Buttons

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime?

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today.

https://material.io/

https://icons8.com/icons/

How to customize VBA Editor to Dark Theme or Black Background

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime?

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today.

This page is not monitored so for questions please comment on the youtube video page. For suggestions email vbaa2z.team@gmail.com

Download project or source code from below link(s)

https://www.fontsquirrel.com/fonts/source-code-pro

https://youtu.be/K_sj0GBSLew

Font Install and Selection Guide

Office Add-ins Resources. Create Excel Web Add-in for Absolute Beginners

Office Add-ins platform overview

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime?

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today.

This page is not monitored so for questions please comment on the youtube video page. For suggestions email vbaa2z.team@gmail.com

Download project or source code from below link(s)
Hey, guys welcome back to my channel, I'm Lung Pamai, and in this new series, I'll introduce you to Office Add-ins platform. For demonstration purpose, we'll create a panel or task pane for Excel which will run across platforms.

In nutshell, Office Add-ins can do almost anything a webpage can do inside a browser, so, you extend your app functionalities by using familiar web technologies such as HTML, CSS, and JavaScript. The best thing is that it can run in Office across multiple platforms, including Windows, Mac, iPad, and in a browser. This is because it doesn't involve code that runs on the user's device or in the Office client. Having said that, I do believe that VBA will be around for a long time and still be the king in Client-Side for office app automation. VBA's new features documentation was last updated this year on Jan 22 for Office 2019.

One thing to point out is that - VBA developers have huge leverage because you already know the object hierarchy, properties and methods which you'll be accessing using Excel JavaScript API.

If you're new to Visual Studio, I will leave a link where you can download Visual Studio IDE. I'm using Visual Studio Community Version for Windows but it is also available for Mac if you are Mac User. Community Version is free, you can install it by simply following the instructions on your screen but if you have any specific question please comment below. If you do not find the tools/templates you need while creating the project, you can install more tools and features at any time.

https://docs.microsoft.com/en-us/office/dev/add-ins/overview/office-add-ins

https://docs.microsoft.com/en-us/office/dev/add-ins/quickstarts/excel-quickstart-jquery?tabs=visualstudio

https://docs.microsoft.com/en-us/office/dev/add-ins/excel/

Visual Studio: Best-in-class tools for any developer

https://visualstudio.microsoft.com/

Not finding the templates for Web Add-in? Install 'Office/SharePoint development' from 'Get Tools and Features...' and restart VS.

If you've previously installed Visual Studio 2019, use the Visual Studio Installer to ensure that the Office/SharePoint development workload is installed.

If you don't already have Office, you can join the Office 365 Developer Program to get a free, 90-day renewable Office 365 subscription to use during development.

More resources will be added with more videos/tutorials.

To Support - Subscribe | Like | Share | Donate: https://www.paypal.me/lpamai
Questions/feedback - comment or vbaa2z.team@gmail.com

Exception Unhandled Error. Ensure your internet is on.

Additional Reference Materials / Links.

https://www.w3schools.com/tags/att_input_checked.asp
https://www.daoudisamir.com/references/vs_ebooks/html5_css3.pdf
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Control_flow_and_error_handling
https://htmlcheatsheet.com/js/
https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/number
https://developer.microsoft.com/en-us/fabric-js/components/checkbox/checkbox
https://docs.microsoft.com/en-us/office/dev/add-ins/quickstarts/excel-quickstart-jquery?tabs=visualstudio

Extract any Web table to Excel in seconds using VBA - New 100% free with source code

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime?

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today.

This page is not monitored so for questions please comment on the youtube video page. For suggestions email vbaa2z.team@gmail.com

Please find the link to download the plugin below. If you make enhancements please share back so everyone can benefit from your work/experience. vbaa2z.team@gmail.com

Please subscribe to my channel and also share with your friends.

RibbonX - Build First-Class UI Directly into App Ribbon

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime?

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today.

This page is not monitored so for questions please comment on the youtube video page. For suggestions email vbaa2z.team@gmail.com

Download project or source code from below link(s)

Click here for all videos

Resources

* OfficeCustomUIEditorSetup Download
* 2.1 Parts
* 2.2 Elements
* 3.2 imageMso Table
* imageMSO Browser/Preview

Goto VSTO RibbonX - ALL

RibbonX - 01

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabHome">
<group id="Group5" label="Loader">
<button id="Button5" label="Upload to Database" size="normal" onAction="code1" imageMso="FilePublishAsWebPage"/>
<button id="Button6" label="Run Reports" size="normal" onAction="code2" imageMso="Chart3DBarChart"/>
<button id="Button7" label="Misc" size="normal" onAction="code3" imageMso="MacroConditions"/>
<button id="Button8" label="Info" size="normal" onAction="code4" imageMso="Info"/>
<button id="Button9" label="Help" size="normal" onAction="code5" imageMso="Help"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

RibbonX - 02

------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<backstage>
<tab id="customTab" label="Custom">
<firstColumn>
   <taskGroup id="customTaskGroup" label="Custom Task Group">
      <category id="tgCategory1" label="Category One">
         <task id="task1" label="Task 1" imageMso="FileOpen"/>
         <task id="task2" label="Task 2" imageMso="FileSave"/>
         <task id="task3" label="Task 3" imageMso="FileSaveAs"/>
      </category>
   </taskGroup>
</firstColumn>
</tab>
</backstage>
</customUI>

------------------------------------------

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="customTab" label="Custom Tab">
   <group id="customGroup" label="Custom Group">
      <button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
   </group>
</tab>
</tabs>
</ribbon>
</customUI>

------------------------------------------
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Contoso" insertAfterMso="TabHome">
   <group idMso="GroupClipboard" />
   <group idMso="GroupFont" />
   <group id="customGroup" label="Contoso Tools">
      <button id="customButton1" label="ConBold" size="large" onAction="conBoldSub" imageMso="Bold" />
      <button id="customButton2" label="ConItalic" size="large" onAction="conItalicSub" imageMso="Italic" />
      <button id="customButton3" label="ConUnderline" size="large" onAction="conUnderlineSub" imageMso="Underline" />
   </group>
   <group idMso="GroupEnterDataAlignment" />
   <group idMso="GroupEnterDataNumber" />
   <group idMso="GroupQuickFormatting" />
</tab>
</tabs>
</ribbon>
</customUI>
------------------------------------------


<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<commands>
<command idMso="Bold" enabled="false"/>
<command idMso="Save" onAction="MySave"/>
</commands>
</customUI>
------------------------------------------

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab idMso="TabInsert">
   <group id="customGroup" label="Contoso" insertAfterMso="GroupIllustrations">
      <button id="customButton" label="Document ID" size="large" imageMso="ListNumVal" onAction="insertDocID" />
   </group>
</tab>
</tabs>
</ribbon>
</customUI>
------------------------------------------

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Contoso" insertAfterMso="TabHome">
<group idMso="GroupClipboard" />
<group idMso="GroupFont" />
<group id="customGroup" label="Contoso Tools">
   <button id="customButton1" label="ConBold" size="large" onAction="conBoldSub" imageMso="Bold" />
   <button id="customButton2" label="ConItalic" size="large" onAction="conItalicSub" imageMso="Italic" />
   <button id="customButton3" label="ConUnderline" size="large" onAction="conUnderlineSub" imageMso="Underline" />

<gallery id="gallery" label="Gallery" itemWidth="88" itemHeight="68"
size="large" imageMso="HappyFace" >
   <item id="item1" imageMso="FreezePanes" />
   <item id="item2" imageMso="FreezePanes" />
   <item id="item3" imageMso="FreezePanes" />
   <item id="item4" imageMso="FreezePanes" />
</gallery>


</group>
<group idMso="GroupEnterDataAlignment" />
<group idMso="GroupEnterDataNumber" />
<group idMso="GroupQuickFormatting" />
</tab>
</tabs>
</ribbon>
</customUI>

RibbonX - 03

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="true">

<qat>
<documentControls>
<control idMso="CalculateNow" />
<control idMso="HyperlinkInsert" />
<button id="Button5" label="Upload to Database" onAction="code1" imageMso="Grouping"/>

</documentControls>

</qat>

</ribbon>

<backstage>

   <button idMso="FileSave" visible="false"/>
   <button idMso="FileSaveAs" visible="false"/>
   <button idMso="FileOpen" visible="false"/>
   <button idMso="FileClose" visible="false"/>
   <button idMso="ApplicationOptionsDialog" visible="false"/>
   <button idMso="FileExit" visible="false"/>
   <tab idMso="TabInfo" visible="false"/>
   <tab idMso="TabRecent" visible="false"/>
   <tab idMso="TabNew" visible="false"/>
   <tab idMso="TabPrint" visible="false"/>
   <tab idMso="TabShare" visible="false"/>
   <tab idMso="TabHelp" visible="false"/>
   <tab idMso="TabPublish" visible="false"/>
   <tab idMso="TabSave" visible="false"/>
   <tab idMso="TabOfficeStart" visible="false"/>

      <tab id="customTab" label="Custom">
      <firstColumn>
      <taskGroup id="customTaskGroup" label="Custom Task Group">
      <category id="tgCategory1" label="Category One">
      <task id="task1" label="Task 1" imageMso="FileOpen"/>
      <task id="task2" label="Task 2" imageMso="FileSave"/>
      <task id="task3" label="Task 3" imageMso="FileSaveAs"/>
      </category>
      </taskGroup>
      </firstColumn>
      </tab>
</backstage>

</customUI>

RibbonX - 04

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<contextualTabs>
<tabSet idMso="TabSetChartTools">
   <tab id="Tab1" label="Chart Utilities">
   <group id="Group1" label="Chart Utilites">
      <button id="Button5" label="Upload to Database" size="normal" onAction="code1" imageMso="FilePublishAsWebPage"/>
      <button id="Button6" label="Run Reports" size="normal" onAction="code2" imageMso="Chart3DBarChart"/>
      <button id="Button7" label="Misc" size="normal" onAction="code3" imageMso="MacroConditions"/>
      <button id="Button8" label="Info" size="normal" onAction="code4" imageMso="Info"/>
      <button id="Button9" label="Help" size="normal" onAction="code5" imageMso="Help"/>
   
   </group>
   </tab>
</tabSet>


<tabSet idMso="TabSetPivotTableTools">
      <tab id="Tab1x" label="Pivot Utilities">
      <group id="Group1x" label="Pivot Utilites">
      <button id="Button5x" label="Upload to Database" size="normal" onAction="code1" imageMso="FilePublishAsWebPage"/>
      <button id="Button6x" label="Run Reports" size="normal" onAction="code2" imageMso="Chart3DBarChart"/>
      <button id="Button7x" label="Misc" size="normal" onAction="code3" imageMso="MacroConditions"/>
      <button id="Button8x" label="Info" size="normal" onAction="code4" imageMso="Info"/>
      <button id="Button9x" label="Help" size="normal" onAction="code5" imageMso="Help"/>
      
      </group>
      </tab>
</tabSet>



</contextualTabs>
</ribbon>
</customUI>

RibbonX - 05

https://docs.microsoft.com/en-us/openspecs/office_standards/ms-customui/3caf6b18-34cb-4352-bd31-77a26980c00e

https://docs.microsoft.com/en-us/openspecs/office_standards/ms-customui/700e4451-8706-40c5-8d7b-896e4ae21b69

RibbonX - 06

https://docs.microsoft.com/en-us/office/vba/api/office.iribbonui

https://docs.microsoft.com/en-us/openspecs/office_standards/ms-customui/21312cb8-be0f-412c-8184-acd533a1410b

https://docs.microsoft.com/en-us/openspecs/office_standards/ms-customui/846e8fb6-07d3-460b-816b-bcfae841c95b

Contextual Tabs

Also known as Tool Tabs, these are additional tabs that will appear when you are working with specific objects.

SmartArt Tools
SmartArt Tools
Chart Tools
Chart Tools
Drawing Tools
Picture Tools
Pivot Table Tools
Pivot Table Tools
Header & Footer Tools
Table Tools
Pivot Chart Tools
Pivot Chart Tools
Pivot Chart Tools
Ink Tools
Sparkline Tools
Timeline Tools
Slicer Tools
Search Tools
Query Tools
Equation Tools

VSTO - Customize / Design Excel Ribbon using XML & .NET Tutorial Link: https://www.youtube.com/watch?v=C-F_hLt-goA XML code for reference below:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
<ribbon>

<contextualTabs>
<tabSet idMso="TabSetChartTools">
<tab id="Tab1" label="Chart Utilities">
<group id="Group1" label="Chart Utilites">
<button id="Button5X" label="Change Chart Type" size="normal" onAction="CheckBoxtest" imageMso="FilePublishAsWebPage"/>
<button id="Button6X" label="Change Color Def" size="normal" onAction="CheckBoxtest" imageMso="Chart3DBarChart"/>
</group>

</tab>
</tabSet>

</contextualTabs>

<tabs>

<tab id="Tab2" label="VSTO-XML">
<group id="Group1x" label="Version Info">
<labelControl id="Label1" getLabel="getLabelInfo"/>
<labelControl id="Label2" getLabel="getLabelInfo"/>
<labelControl id="Label3" getLabel="getLabelInfo"/>
</group>

<group id="Group2" label="More Controls">
<button id="Button1" onAction="ShowTP" label="Show Task Pane" showImage="false"/>
<button id="Button2" onAction="ShowUF" label="Show Win Form" showImage="false"/>
</group>

<group id="Group3" label="Dropdown">
<dynamicMenu id="dynamic" label="Dynamic Menu" getContent="GetMenuContent"/>
<dropDown id="dropDown" onAction="Location" label="City">
<item id="London" label="London"/>
<item id="NewYork" label="New York"/>
<item id="Delhi" label="Delhi"/>
<button id="Budapest" label="Budapest" onAction="Budapest_bt"/>
</dropDown>
<labelControl id="Label4" label="Label4"/>

</group>

<group id="Group4" label="InputBox">
<editBox id="EditBox1" onChange="EditBox_TextChanged" getText="EditBoxGetText" label="Number 1" showImage="false"/>
<editBox id="EditBox2" onChange="EditBox_TextChanged" getText="EditBoxGetText" label="Number 2" showImage="false"/>
<editBox id="EditBox3" getText="EditBoxGetText" label="Total " showImage="false"/>
<button id="Button4" onAction="CaclTotal" label="Cacl Total" showImage="false"/>
</group>

<group id="Group5" label="Checkbox">
<checkBox id="CheckBox1" onAction="CheckBoxClck" getPressed="CheckBoxPressed" label="CheckBox1"/>
<button id="Button5" onAction="CheckBoxtest" label="Check Box Test" showImage="false"/>
<toggleButton id="ToggleButton1" label="ToggleButton1" showImage="false"/>
</group>

</tab>
</tabs>
</ribbon>
</customUI>

VBA to automatically add Controls in Design Mode to UserForm on Run-Time

As I mentioned in tutorial https://youtu.be/csUhqCN0CBI this code is rough but works.

find below VBA code to automatically add Controls like Labels, Textbox etc to UserForm on Run-Time.

Download Project here: https://bit.ly/3aKfrFS

Or copy the code from below:

Option Explicit

Sub addCtrls()
'-----------------------------
'Thanks for downloading the code.
'Please visit our channel for a quick explainer on this code.
'Feel free to update the code as per your need and also share with your friends.
'Download free codes from http://vbaa2z.blogspot.com
'Subscribe channel: youtube.com/vbaa2z
'Author: L Pamai (vbaa2z.team@gmail.com)
'-----------------------------

't1-54;114;108;78
'T54, 60/42, T54
Dim UFvbc As VBComponent
Dim r As Long

Set UFvbc = ThisWorkbook.VBProject.VBComponents("UserForm1")
Dim cb As Control
Dim t As Long, h&, w&, rowCnt&, ColCnt&, objLeft&, vGap&, objLeft_last&, totalColumns&
Dim objRow_last As Long
Dim TotalRows&

t = 15 'top
h = 10 'hieght
w = 84 'width

rowCnt = 1
ColCnt = 1
objLeft = 40
objLeft_last = 10

vGap = 14
objRow_last = 20

TotalRows = 12
totalColumns = 4

For r = 1 To (TotalRows * totalColumns) '(6 * 7)

Set cb = UFvbc.Designer.Controls.Add("Forms.Label.1", "Label" & r, True)
'Set cb = UFvbc.Designer.Controls.Add("Forms.Textbox.1", "Textbox" & r, True)
    
    't1_r1_c1
    cb.Name = "t1_r" & rowCnt & "_c" & ColCnt
    cb.BackColor = &HC0C0C0   'vbGreen
    cb.Height = h
    cb.Width = w
    cb.Top = objRow_last

    '----------------------
    If ColCnt = 1 Then
      cb.Left = objLeft
      objLeft_last = objLeft
      'vGap = 1
    Else
      cb.Left = objLeft_last + w + vGap
      objLeft_last = objLeft_last + w + vGap
    End If
    
    ColCnt = ColCnt + 1
    '----------------------
    
    If ColCnt = totalColumns + 1 Then
      ColCnt = 1
      rowCnt = rowCnt + 1
      objLeft_last = 10
      objRow_last = objRow_last + h + vGap
        
    End If
    
  Set cb = Nothing
  
Next r

End Sub

Search

Font Install and Selection Guide

Office Add-ins platform overview

Visual Studio: Best-in-class tools for any developer

More resources will be added with more videos/tutorials.

Additional Reference Materials / Links.

Resources

Goto VSTO RibbonX - ALL

RibbonX - 01

RibbonX - 02

RibbonX - 03

RibbonX - 04

RibbonX - 05

RibbonX - 06

Contextual Tabs

VSTO - Customize / Design Excel Ribbon using XML & .NET Tutorial Link: https://www.youtube.com/watch?v=C-F_hLt-goA XML code for reference below: