Monday, April 16, 2007
To copy from a range in another sheet (eg Sheet3) to the active cell you need to change the code to;
Friday, April 13, 2007
When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro will do the trick.
mycount = Selection.Rows.Count 'Change Rows to Columns to count columns
The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.
mycount = Application.Sheets.Count
To add a combobox refer to User Form. To populate a combobox or a listbox is the same. You could add from the code or even from a range of cells in your spreadsheet. To add from the code, just add this line to your code.
ComboBox1.AddItem "Product A"
ComboBox1.AddItem "Product B"
Making your macros run automatically when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.
This code would be located in the module. However if you use the second method, the code must be in the workbook (double click "This Workbook" in the explorer window). Click on the drop down list (that says General) and select Workbook. Click on the drop down list (that says declarations) and select Open.
Private Sub Workbook_Open()