VBA Code - Update Cell if adjacent cell is changed

[backfromthebush]

Hi all!

I have a spreadsheet with 4 columns.

Column B contains names (John, Barry, Henry, Chris, and **Available**). These are referenced & selected via a data validation list. The default value is **Available**

Column D contains a category - Red, Blue, Green or Yellow.
Column E also contains the same categories as above, except that it is optional to actually have a value in these cells. These values are already typed into these cells.

What I wish to achieve is that when I select a name from Column B1 or B2 or B3 etc, that isn't **Available** then the adjacent column C cell will auto update as follows:-
- if there isn't a value in Column E, then return the value on Column D
- if there is a value in Column E, then provide a message box that asks the user which category they won't input (e.g. column D has "Green" and column E has "Yellow", a message box will pop up and say "Which Category do you wish to input?" and two buttons - Green and Yellow will be available).

I have data in ranges B8:E801, so i need the above to work whenever I change the value in any cell in column B.

Can anyone help??

 

[sijpie]

You want to create a macro in the module for the specific worksheet, that only runs when changes are made to the worksheet.
Right click on the worksheet tab and select 'View Code'.

The macro editor will open in the module for the worksheet.
Now above the macro window there is a dropdown filed that reads '(General)'. Click on it and select 'Worksheet'.
The editor will write the sub headings for 'SelectionChange'
ignore this (you can delete it)
In the right dropdown field you also see SelectionChange. Click on it and sleect 'Change'.
The macro editor now writes the sub for Worksheet_Change.

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

This macro will be run anytime a change is made to this sheet.

We now only want to do anything in the macro if the change occurs in column B. In the parameters passed to the Worksheet_Change sub you can see a 'Target'. This is the cell that has been changed. So we need to check if that is in column B, then have the macro process the checking of what is in D & E of that row.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        'the change has been made in column B
 
        If Target.Value <> "**Available**" Then
            If Target.Offset(0, 3).Value = vbNullString Then 'E is empty
                Target.Offset(0, 1).Value = Target.Offset(0, 2).Value   'set C to value in D
            Else
                Set Trgt = Target
                Userform1.Show        ' call message box
                Set Trgt = Nothing
            End If
        End If
    End If
End Sub

You see that if E has a value then a userform UserForm1 is called. This is because the standard messagebox does not allow changing of the button text. So you will now have to create your own message box.

in the macor editor, left panel you will see your workbook. Rightclick on the name and select 'Insert' / 'Userform'

A blank userform will be shown. the userform already has the name Userform1.
Now using the little toolbox that pops up when you click on the userform you can add a label. Paint it on your form
Add the text: "Please select which category you want to enter in column C"

then add two buttons. For now change the text on the buttons to yellow and Green (you can give the buttons a colour as well with the properties in the lower left panel when you click on a button)

Now you need to enter the code for the buttons. Double click on one of the buttons, and the code window for the form will open. Delete wahtever is in there and paste the following code (this assumes that the first button was labeled yellow and the 2nd button Green)

Private Sub CommandButton1_Click()
    Trgt.Offset(0, 1).Value = "Yellow"
    Unload Me
End Sub
 
Private Sub CommandButton2_Click()
    Trgt.Offset(0, 1).Value = "Green"
    Unload Me
End Sub

Now as a last thing you need to define a public parameter called Trgt. This is because the userform does not know which range has been changed, and it might not be the current cell if the user used an arrow key.
So for this you need to add a macro module. Right click on the file name and instead of adding a userform add a Module.

in here enter this simple declaration:

 Public Trgt as range

now go back to your spreadsheet and it should work