Excel Macros : How to add GUI controls and VBScript code ?

Add GUI control 1. Enable Developer menu Right click anywhere in toolbar, and click customize this Ribbon... In main tabs, select developer and click OK 2. In developer menu, Click icon Design Mode Click Insert - GUI control (like, button) 3. Draw the GUI control on the sheet. 4. Right click on button, choose Assign Macro... and click on New button. 5. Write VBScript code to perform any action for the new function Sub calculateSum()    . . . . . End Sub WRITING MACRO CODE COPY DATA FROM ONE CELL TO OTHER Cells(RowIdx, ColIdx).Value = Cells(RowIdx, ColIdx).Value Cells(2, 3).Value = Cells(2, 6).Value GET CELL VALUE TO STRING VAR Dim Name As String Name = Cells(2, 3).Value REPLACE STRINGS Name = Replace(Name, "SE", "Software Engineer") ' Replace New line with space Name = Replace(Name, vbLf, " ")  COUNT NUMBER OF SHEETS IN THE WORKBOOK Dim sheetCount As Integer sheetCount = ActiveWorkbook.Worksheets.Count GET THE SHEET NAME sheetName = ActiveWorkbook.Worksheets(sheetIdx).Name ' sheetIdx starts with 1 and goes till n COUNT TOTAL ROWS USED (WITH DATA) IN A SHEET Dim EndRow As Long With ActiveWorkbook.Worksheets(sheetIdx).UsedRange    EndRow = .Cells(.Cells.Count).Row End With GET DATA FROM ANY CELL OF ANY SHEET Data_Name = ActiveWorkbook.Worksheets(sheetIdx).Cells(RowIdx, ColIdx) TRIM THE STRING Data_Name = Trim(Data_Name) DISPLAY MESSAGE BOX MsgBox "Sheet : " & sheetName GET SHEET NAME AND DATA (CANDIDATE NAMES) FROM ALL SHEETS Assuming, Candidate Name column is 4th on each sheet. Dim sheetCount As Integer Dim EndRow As Long ColIdx_Name = 4 sheetCount = ActiveWorkbook.Worksheets.Count For sheetIdx = 1 To sheetCount     sheetName = ActiveWorkbook.Worksheets(sheetIdx).Name     With ActiveWorkbook.Worksheets(sheetIdx).UsedRange              EndRow = .Cells(.Cells.Count).Row      End With      For RowIdx = 2 To EndRow    Data_Name = Trim(ActiveWorkbook.Worksheets(sheetIdx).Cells(RowIdx, ColIdx_Name))      Next RowIdx Next sheetIdx CREATING / USING FUNCTIONS ' Assign macro to Button1 Sub button_Export_Name_click()   exportData columnIdx:=2, FileExt:=".txt" End Sub ' Assign macro to Button2 Sub button_Export_Dept_click()    exportData columnIdx:=4, FileExt:=".txt" End Sub ' Common function called by both the buttons Public Sub exportData(columnIdx As Integer, FileExt As String)    . . . End Sub FILE AND FOLDER OPERATIONS Set objFSO = CreateObject("Scripting.FileSystemObject") ' Check if a folder (location = parellel to the workbook) exists ; if not, create it strFolder = ThisWorkbook.Path & "\exportedKeyFiles" If objFSO.FolderExists(strFolder) = False Then     objFSO.CreateFolder strFolder End If ' Remove file if already exist FName = strFolder & "\projectConfig.txt" If objFSO.fileExists(FName) Then     objFSO.DeleteFile (FName) End If ' Open a text file for Write with append Open FName For Append Access Write As #FNum ' Write to text file line = "Hello this is first line" & vbCrLf Print #FNum, line