Some handy tips and tricks for using Excel, which can make life a lot easier and allow you a lot more functionality in your spreadsheet apps.
Comboboxes: Yes you can use a combobox control to display data dropdowns and code it in VBA, but sometmes it just isn't necessary. You can use Excel's built-in combo equivalent, by setting a validation on a cell.
Select a cell and go to Data, Validation menu. On the Settings tab, choose Allow: List and then specify your source range. Voila, an in-cell dropdown. You could combine this with...
Detecting cell changes: Each worksheet has Change and SelectionChange events which pass to the code a range called Target. This allows you to determine which cell has been changed (in the case of the former) or selected (in the case of the latter).
Tells you the row and column of the cell that was changed.
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox (Target.Row & ", " & Target.Column)
The SpecialCells method: Matches cells according to specific conditions, eg empty cells, cells with numbers or formulae, last used cell etc.
Activates the last used cell.
The WorksheetFunction object: Allows access to all those handy Excel formula functions that you type in a cell function. All the maths functions, statistics, etc can now be accessed through your code so that, for example, you don't have to use an extra cell or a big loop to calculate the average of a range, just use the WorksheetFunction to put it in a variable.
Averages the numbers in the range A1 to A10.
Dim MyRangeAverage as Double
MyRangeAverage = WorksheetFunction.Average(Range("A1:A10"))
The Application object: Very useful to find out information about the current application, such as the current user ID, the start up path, the templates path etc.
Displays the Office username and startup path of the current user.
Msgbox (Application.UserName & ", " & Application.StartUpPath)
Dialog boxes: VBA supports all sorts of dialog boxes, accessed using the Dialog object. These run built-in dialog boxes as though you'd clicked the button yourself. So the Open dialog box will actually open a file, the Chartwizard box will set up a graph etc.
Runs the Open dialog box, and opens the selected file. Returns True.
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Sometimes, however, you'd like to present your user with a file dialog that acts as a picker rather than as an actual Open / Save box. For this you can use the FileDialog, and then code the action to take on the basis of the selection.
Opens a file picker dialog and returns the filenames of all the selected files.
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen)
.AllowMultiSelect = True
For Each i in dlgopen.selecteditems
The Find method: You can use the Find method of a range to pick out the data that you want. The Find method returns a cell if the data is found, or Nothing if not.
Finds the number 2 in the range A1 to B500 and returns the row and column where it first occurs.
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
MsgBox (c.Row & ", " & c.Column)
Excel Graphing: Rather than spend hours generating multiple graphs by hand, or for situations in which you might want to select from a list of parameters and update a graph accordingly, you can simply generate the graph through code and then change the SeriesCollections to display whatever data you might want.
See the link in my sig
Setting sheet visibility: Not quite the same as actually setting permissions, because this doesn't involve locking the sheet but it can be a handy way to determine what users can and cannot see.
Add a worksheet and call it Settings. In column A, list all the Office usernames of your admin users. Then place some code in the Workbook Open event:
Matches the current user to a list on the Settings worksheet. If the user is not on the list, then neither the Settings sheet nor Sheet2 are visible.
Set c = Worksheets("Settings").Range("A:A").Find(Application.UserName)
If Not (c Is Nothing) Then
Worksheets("Sheet2").Visible = True
Worksheets("Settings").Visible = True
Worksheets("Sheet2").Visible = False
Worksheets("Settings").Visible = False