Originally Posted by
Tessawoolfson
Have had a look at the Worksheet selection_change event suggestion and here are my figures etc for the first option
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address
Case Is = "$F$5"
Range("f6") = ' $E$5*$F$5/$E$7
Range("f7") = ' ($E$5/$E$7*($E$7-1)/2)/$E$6*$F$5
End Select
End Sub
I think I understand that it means that if there is a value put into F5 then it will use the first calculation and put the result in F6 and then use the second calculation and put the result in F7. I am understanding correctly so far?
I don't understand the first two lines though! What are they instructing to happen?
Sorry for being so dumb about this.
Hi,
When you say the first two lines, presumably you mean the:
The first line is always the name of the Sub routine/Procedure.macro, (the terms mean the same). So this is always something like
Sub MySuperbProcedure()
in this case of course it's actually a Sub routine which happens to be attached to the Worksheet and is triggered by a change on the worksheet. Unlike most other Sub routines which you can define exactly as you wish, this is a 'reserved' description and you can't change the 'word' "Worksheet_Change".
All Sub routines have a pair of brackets () at the end. A lot of the time these are empty, but on occasions you might wish to pass parameters to the Sub which you want to make use of in the routine. This being an 'event' Sub, the parameter is prescribed for you. In this case the parameter passed to the routine is the 'Target', which is just another way of saying a cell. Think of the target as the cell selected when you click the cursor on it and make a change. It is further defined as being the cell range, i.e. 'as Excel Range'
Which is all a long way of saying the routine starts with knowledge of which cell you're changing.
The second line is a standard Select Case statement. Select Case statement's are usually a tidier and simpler way of handling IF..Then tests. Usually there are a series of Case statements within the Select Case section. i.e.
In this example there is only one Case statement, i.e.
Select Case Target.Address
The target as we've already established, is the cell that you're changing, and the address is of course is in this example the string "$F$5".
The rest you know....
HTH
Bookmarks