There are two types of controls that you can add to your worksheets and there are a lot of similarities between the two. You can add Form controls and/or ActiveX controls. The most common types of worksheet controls are command buttons, option buttons and check boxes. If you want to be able to run a macro from any location in any worksheet or workbook then it would be more appropriate to attach this to a button on a toolbar or ribbon tab. If a macro is worksheet specific and will only be used on that particular worksheet then it is appropriate to use a button that has been embedded on that worksheet. These are not examples of worksheet controls. items selected in a list box).ģ) They are not as robust as using userform controls and can frequently cause Excel to crash.Ĥ) There are some known problems especially around the resizing of buttons.ĭo not get confused with the drop-down list boxes that appear as part of the Data Validation > Drop-Down Lists or Tables > Filtering Tables features. The worksheet will automatically recalculate when a value changes.ģ) All worksheet controls can be moved and sized independently of the row and column headings.Ĥ) They can be used to help prevent errors by restricting choices to valid options.ĭisadvantages of using Worksheet Controlsġ) You must select the controls using the mouse, there is no keyboard equivalent.Ģ) Some control properties will be reset when the workbook is next opened (e.g. When controls are placed directly onto a worksheet they are known as Worksheet Controls or Form Controls.ġ) Controls can be placed anywhere on the worksheet giving you more flexibility.Ģ) The consequence of changing a worksheet control is immediately visible within the worksheet. It is even possible to embed a worksheet control inside a chart object.ġ) Providing a button to perform a specific task, such as printing particular cell ranges.Ģ) Providing a checkbox to allow options to be easily switched on or off.ģ) Providing a spinner button to easily change the parameters for a calculation. Worksheet controls can be used effectively with databases and pivot tables. When controls are placed directly onto a worksheet they are known as Worksheet Controls, Custom Forms or ActiveX Controls.Ī worksheet control is basically a graphical object that enables the user to enter information, perform an action or select a value. Therefore, the values are added to theĮmployee BindingList as an Employee object.Most applications use dialog boxes as a means of interacting with the user, whether it is displaying a message or requesting a password.Īn alternative to using custom dialog boxes is to actually place the controls directly onto the worksheet. The start cell (e.g., A1 ) and end cell (e.g., G4) as parameters and returns all the cell values in this range as a two dimensional array. The get_Range(string, string) method takes This feature is not achievable using a traditional Modification to BindingList will cause the component model object to refresh (if the data source of the UI element is set asĪ BindingList object). The reason behind this usage is that, any To my surprise, here we have used BindingList for Employee objects rather thanĪ normal List. Number = MyValues.GetValue( 1, 4).ToString() Name = MyValues.GetValue( 1, 1).ToString(),Įmployee_ID = MyValues.GetValue( 1, 2).ToString(),Įmail_ID = MyValues.GetValue( 1, 3).ToString(), Index.ToString(), " D" + index.ToString()).Cells.Value System.Array MyValues = (System.Array)MySheet.get_Range( " A" +
This namespace is widely referred in the project, hence I have assigned an alias name ' Excel'.Ĭopy Code BindingList EmpList = new BindingList() įor ( int index = 2 index <= lastRow index++) In order to use Excel operations in VS, the first and foremost thing to do is to include the Microsoft.Office Object Library reference to the project.ĭoing so will enable us to use the namespace. This app can only manipulate Excel with four columns, i.e., Name, Emp_ID, Email_ID, and Mobile_No. Let's find out how the above operations are implemented in the below sections.