In this article, you will learn how to create a Table of Contents for any active workbook using VBA. We have created VBA code which you can put in your personal macro. Below is the code to create the Table of Contents - Option Explicit Sub Create_TableOfContents() 'Check is workbook is protected If ActiveWorkbook.ProtectStructure = True Or ActiveWorkbook.ProtectWindows = True Then MsgBox "Workbook is protected. Please unprotect it first", vbCritical
In this article, you will learn how to create a User Defined Function in VBA to get the frequency of specific word in a paragraph. Using this UDF you will be able to get the Total Words, Specific word count (Case Sensitive Check) and Specific word count (Non-Case Sensitive Check). For example, we have below given paragraph, and we have got the Total word count and Specific word count also-
In this article, you will learn how to create a PDF to Excel converter using Excel VBA. We have created a Setting worksheet wherein we are taking 2 folders inputs on range E11 and E12 respectively. PDF Files Folder: Wherein all the PDF files are available Excel File Folder: Wherein Excel files will be saved after conversion. [caption id="attachment_15980" align="aligncenter" width="658"] Setting Sheet[/caption] Add the Microsoft Scripting Runtime Reference
Our daily Excel activities can be automated using VBA. Here we have created an example of creating the Name range in Excel using VBA. Lets’ say we have below given data for which we have create the Names- [caption id="attachment_15185" align="aligncenter" width="1131"] Data to create the Names[/caption] We have created a button on the Home tab and assigned the macro on this button. [caption id="attachment_15183" align="aligncenter" width="559"] Additional button added
In this article, you will learn how to create a User-defined function in VBA to get the comment text from an Excel Cell. Let’s say we have attendance data on the Excel sheet and We are putting comments for the absent employees. Now if we need to fetch these comments in Excel cell to do some analysis then we can use this UDF. [caption id="attachment_14475" align="aligncenter" width="699"] Get Comments UDF[/caption]
Many times, we require to segregate our data into different worksheets. Here we have created a macro which can segregate your data into worksheets. One user from will be displayed wherein you can select the field name by which you want to segregate your data. Below is the data which we will segregate into multiple worksheets- [caption id="attachment_14190" align="aligncenter" width="495"] Data[/caption] We have added an icon on home tab to
Shell function in VBA is very useful. If you want to start a program or open a executable file then you can use shell function in VBA . In this article we will explain shell command for below 3 examples- Start a Program Open a Folder Open a Non-Excel file Below is the syntax of Shell function- Shell (pathname, windowstyle) Pathname: The full path of the program to execute (String).
Login Form is very important for any application. We have designed this Login form in VBA User form and connected with MS Access Database. We have used ADODB Connection to connect with MS Access Database. To create the design of this Login form we have taken the support of Microsoft Power Point also. Click here to learn more about ADODB Connection. Below is the snapshot of MS Access database table
In this article we will tell how you can develop your own PDF to Word converter using Excel VBA. You can convert multiple files using this macro. Keep all your PDF files in one folder. You need to provide the link of PDF and word files folder path on Excel Sheet (Rename is as Settings). Word files folder should be empty. Word files will be saved into this folder after
Keyboard Shortcut Keys can improve your productivity. In this article we will tell you 33 useful VBA shortcut keys. If you are a VBA developer or work on VBA frequently then these shortcut keys are very useful to you. You can quickly design your User form using these shortcut keys. Below is the list of 33 useful VBA Shortcut Keys- [table id=9 /] Click here to download this practice
