Excel 2013 VBA

[darrenbutt]

I am looking for some assistance with VBA as I am extremely new to this...

I have a drop down menu in cells D8:D28 that have 5 options (Elec, Mech, Op, C/Over and Project). I want to stop people selecting either Elec or Mech unless there is a numeric value in cell N8:N28.

Please if anyone can help I would be greatly appreciative.

Regards,

Darren

 

[Rick Rothstein]

Hi Rick,

Can this code be expanded - If someone is clever enough you can currently enter a numeric value into N8:N28 to allow you to select Elec or Mech from the drop down but you can then go and delete the numeric value. Can the code stop someone from deleting the numeric value if either Elec or Mech is selected from the drop down menu is cells D8:D28.

Give the following a try... what it will do is clear the corresponding cell in Column D if the value in Column N is cleared (I highlighted in blue the change I made that makes this happen).

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Value As Variant
  If Target.Count <> 1 Then Exit Sub
  If Not Intersect(Target, Range("D8:D28")) Is Nothing Then
    Value = Intersect(Target.EntireRow, Columns("N")).Value
    If Value Like "*[!0-9.]*" Or Value Like "*.*.*" Or Len(Value) = 0 Or Value = "." Then
      If Target.Value = "Elec" Or Target.Value = "Mech" Then
        MsgBox "Sorry but you cannot select ""Elec"" or ""Mech"" unless there is a number in cell N" & Target.Row & "."
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
      End If
    End If
  [B][COLOR="#0000FF"]ElseIf Not Intersect(Target, Range("N8:N28")) Is Nothing Then
    If Target.Value = "" Then Target.Offset(, -10).Value = ""[/COLOR][/B]
  End If
End Sub[/td]
[/tr]
[/table]

 

[darrenbutt]

Rick,

Worked a treat! much appreciated. Is there an easy way to se the numeric value so it must be between 900000000 and 999999999 ? I did attempt to myself but it didn't work out too well.

Cheers!

 

[Rick Rothstein]

Worked a treat! much appreciated. Is there an easy way to se the numeric value so it must be between 900000000 and 999999999 ? I did attempt to myself but it didn't work out too well.

Try changing this line of code...

[table="width: 500"]
[tr]
	[td]If Value Like "*[!0-9.]*" Or Value Like "*.*.*" Or Len(Value) = 0 Or Value = "." Then[/td]
[/tr]
[/table]

to this...

[table="width: 500"]
[tr]
	[td]If Value Like "*[!0-9.]*" Or Value Like "*.*.*" Or Len(Value) = 0 Or Value = "." [B][COLOR="#0000FF"]Or Value Like "[0-8]*"[/COLOR][/B] Then[/td]
[/tr]
[/table]

 

[darrenbutt]

Amazing, Thanks ever so much!.