/// <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));
}
}
}