Thread: Change event macro question

Originally Posted by Tessawoolfson

I have a very basic understanding of using excel to make calculations but I am trying to perform a specific task and am unable to work out how to do it.....

Six cells (illustrated below) as part of the excel spreadsheet

A1 Use
A2 Use
A3 Use

I would like to have three 'use' buttons. Working as follows:

1. If you know the value for A1 you type this into cell A1 (leaving A2 and A3 blank), press the use button in the cell next to A1 and this would be assigned to two specific calculations which would input the correct values into A2 and A3

2. If you know the value for A2 you type this into cell A2 (leaving A1 and A3 blank), press the 'use' button in the cell next to A2 and this would be assigned to two specific calculations which would input the correct values into A1 and A3

3. Same as the above for the value in A3....

I know how to use the functions to make a formula for the calculations. I don't know how to make the 'use buttons' or how to link them to the calculations so that depending which 'use button' you press it will input different values into the relevant boxes.

I hope I have explained it reasonablly enough and that this is an appropriate place to post this.

As you can see I am a newbie to excel programming but am a fast learner so any explanations or even hints might point me in the right direction....

Thank you.

Hi,

You could simplify this even further and do away with buttons altogether. With the Worksheet Selection_Change event use code like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address
    Case Is = "$A$1"
        Range("a2") = ' your calculation
        Range("a3") = ' your calculation

    Case Is = "$A$2"
        Range("a1") = ' your calculation
        Range("a3") = ' your calculation

    Case Is = "$A$3"
        Range("a1") = ' your calculation
        Range("a2") = ' your calculation

    
End Select
End Sub

HTH

This looks like exactly what I was looking for so I will play around with it for a bit and see how I go.

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:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address

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.

Select Case Month ' where month is say a variable containing the month name

Case is = "January"
    'do this
Case is = "February
    'do this

End Select

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