/// <summary> /// Method for passing in validation to command rows /// </summary> /// <param name="commandType">Command type value</param> /// <param name="command">Command named range</param> /// <param name="options">Options named range</param> /// <param name="reference">Reference named range</param> private void AddRowValidation(string commandType, string command, string options = null, string reference = null) { Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet worksheet = workbook.ActiveSheet; Excel.Range range = Globals.ThisAddIn.Application.ActiveCell; //Checks if commands page exists if (!workbook.WorksheetExists("Commands")) { MessageBox.Show("Please run 'Add Commands' and then try again."); } // Get row and next column indices var thisColumn = WorksheetHelper.GetColumnName(range.Column); var thisRow = range.Row; Excel.Range startRange = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; //Clears any previous validation on the row string tempColumn; for (int i = 0; i < 6; i++) { tempColumn = WorksheetHelper.GetColumnName(range.Column + i); worksheet.Range[$"{tempColumn}" + $"{thisRow}"].Validation.Delete(); } // Command type column range.Value = commandType; // Gets next column index thisColumn = WorksheetHelper.GetColumnName(range.Column + 1); // Command column range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; range.AddDropDownList(command); // Gets next column index thisColumn = WorksheetHelper.GetColumnName(range.Column + 1); // Options column if (!string.IsNullOrEmpty(options)) { range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; range.AddDropDownList(options); // Gets next column index thisColumn = WorksheetHelper.GetColumnName(range.Column + 1); if (!string.IsNullOrEmpty(reference)) { // Reference column range = worksheet.Range[$"{thisColumn}" + $"{thisRow}"]; range.AddDropDownList(nameof(ReferenceOptions)); } } }