Macro: Enter a number and lock related cell based on data-validation list choice?

[Mikoustics]

Hi folks, I would be very grateful for any help anyone can give me with this VBA problem! (Using Excel 2010 on mac)

In my cell B18 there are three choices in a data validation list:

1. Duct
2. Bend
3. Silencer

I would like cell D18 to become blank and (and be automatically locked if possible) if the choice in B18 is NOT "Duct".

i.e.
B18--------------C18-----------------------D18
Bend------------Absorber-----------------(needs auto-update: blank)


The problems I'm having are that in cell D18 I have a data validation limit set between 0 - 50 for "Duct" choice (so the user can set the number) and set to "" (blank) for any other choice in B18. The problem is this doesn't update automatically, so any leftover number in D18 remains and will be involved in a calculation further across the sheet.

Also I already have VBA code that automatically updates an INDIRECT list in cell C18 when B18 is changed. So I guess there are two methods of doing this?:

1. Have the VBA automatically update D18 with a blank if B18 choice not "Duct" and update the INDIRECT list in C18 - and let the data validation limit do the limiting in D18. Easier? (Does anyone have any code?)

2. Have the VBA do the D18 updating and locking, plus the INDIRECT list in C18 updating?


The VBA Code i already have is (sorry long post I know):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngParentCell As Range
Dim rngDepCell As Range
Dim rngCell As Range

Set rngParentCell = Range("B18")
Set rngDepCell = Intersect(Target, rngParentCell)

If Not rngDepCell Is Nothing Then
Set rngCell = Range("C18")
rngCell.ClearContents
rngCell.Select
MsgBox _
Title:="Notice", _
Prompt:="Please select 'Type', 'Length (Default 1)' and No. (Default 1)'", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

Set rngParentCell = Nothing
Set rngDepCell = Nothing
Set rngCell = Nothing

End Sub

 

[Mikoustics]

Sorry Subject should be (I can't figure out how to edit):

Macro: Enter a value and lock related cell based on data-validation list choice?

 

[Mikoustics]

SOLVED. Used additional code to clear cell D18 when cell B18 changed (data validation limiting then limits cell to be blank, based on B18):
Set rngCell = Range("C18:D18")


Code to clear adjacent cells C18 and D18 when B18 has changed... and give indication message (all credit goes to Ron Coderre... cheers, http://www.excelbanter.com/showthread.php?t=117167).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngParentCell As Range
Dim rngDepCell As Range
Dim rngCell As Range

Set rngParentCell = Range("B18")
Set rngDepCell = Intersect(Target, rngParentCell)

If Not rngDepCell Is Nothing Then
Set rngCell = Range("C18:D18")
rngCell.ClearContents
rngCell.Select
MsgBox _
Title:="Notice", _
Prompt:="Please select 'Type', 'Length (Default 1)' and No. (Default 1)'", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

Set rngParentCell = Nothing
Set rngDepCell = Nothing
Set rngCell = Nothing

End Sub