Custom lists

[Daft Ada]

Hello, I'm working on a sales form for our field reps which needs a list of radiography machines in one field, and then a list of optional extras for that machine in a second field. Each machine has a different set of extras, so I've created customisable lists for the extras field and it works ok if you just select a machine and then the extras.

What I need the form to do (and I don't know if this is even possible) is to clear the extras field if a different machine is chosen. Basically, I need to avoid any potential for the hospital to choose the wrong extras for their machine.

I've only just started experimenting with macros so I have limited experience using them, but can anyone recommend a solution?

Thanks in advance.

PS. I don't know if this image helps at all?

 

[scott_n_phnx]

An easy macro that should get you what you need is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:A7")) Is Nothing Then Exit Sub
    If Target.Value = "" Then
        Range("A11:B19").Value = ""
    End If
End Sub

This goes into the worksheet module
-ALT+F11
-Expand VBAProject(Your Workbook.xlsm)
-Double-Click on worksheet module
-ex. Sheet1(Sheet1)
-If the code is a worksheet event (i.e. worksheet_change, worksheet_beforedoubleclick) the code MUST go into the worksheet module for the worksheet that is being watched
-Paste in code

Modify the ranges as needed. This will clear out the items listed under Item, but not delete.

Use in a test workbook first, or a saved copy, so that you do not lose any data.

 

[Daft Ada]

Thanks for your help!