Microsoft excel is very powerful tool to work on the related data. This time i needed a unique functionality in excel sheet and wanted to share with you all. So to create the macro enabled excel sheet, while saving file in file types, select the micro enabled workbook. In Microsoft Excel 2010 the extension of such type of file is “xlsm”.
on changing of auto complete column, a message box should appear and after clicking on button, the adjacent cell value should get changed as shown in below image:
Right click on “Sheet 1” tab at bottom and select the “View code”.
Source editor will open, write down the below source code in sheet 1:
Sub Worksheet_Change(ByVal Target As Range) Dim WatchRange As Range Dim IntersectRange As Range Set WatchRange = Range("B2:B20") Set IntersectRange = Intersect(Target, WatchRange) If IntersectRange Is Nothing Then 'Do Nothing Spectacular Else If ActiveCell.Value = "Fruit" Then response = MsgBox("Press on YES for Mango and NO for Banana", vbYesNo, "Select Fruit") If response = vbYes Then ActiveCell.Offset(0, 1).Value = "Mango" Else ActiveCell.Offset(0, 1).Value = "Banana" End If End If End If End Sub