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

 

[Norie]

Darren

How did you create the drop down menu?

 

[darrenbutt]

Hi Norie,

I created them using data validation and list.

 

[jim may]

ONE WAY... Use a Worksheet Change (Event) code such as:

Private Sub Worksheet_Change(ByVal Target As Range)
'Independently Assign a Range Name to your Input Area D8:D28 before running this Macro
If Application.Intersect(Target, Range("MyInput")) Is Nothing Then Exit Sub
If Target.Value = "Elec" Or Target.Value = "Mech" Then
    If Not IsNumeric(Target.Offset(0, 10)) Then
        MsgBox "You can't enter Either Elec or Mech if Column N is a Non-Numeric."
        Application.Undo
        Target.Value = ""
        Exit Sub
    End If
End If
End Sub

 

[darrenbutt]

Hi Jim,

I tried the code you supplied but it doesn't work. I may be doing something wrong as I am extremely new to this but I assigned my range D8:D28 a range name of "MyInput" but I can still select Elec or mech without entering a number in cell N8:N28.

 

[EdwardsBueno]

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

Hi,
create the dropdown from a duplicate list linked to the original one and give a condition to it so it can be dynamic as showed below.



[TABLE="width: 348"]
<tbody>[TR]
[TD="class: xl62, width: 94"]Original List[/TD]
[TD="class: xl62, width: 99"]Duplicated List[/TD]
[TD="class: xl62, width: 64"][/TD]
[TD="class: xl62, width: 91"]Values[/TD]
[/TR]
[TR]
[TD="class: xl62"]a[/TD]
[TD="class: xl62"]a[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]1[/TD]
[/TR]
[TR]
[TD="class: xl62"]b[/TD]
[TD="class: xl62"]b[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]2[/TD]
[/TR]
[TR]
[TD="class: xl62"]c[/TD]
[TD="class: xl62"]c[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]3[/TD]
[/TR]
[TR]
[TD="class: xl62"]d[/TD]
[TD="class: xl62"]d[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]4[/TD]
[/TR]
[TR]
[TD="class: xl62"]e[/TD]
[TD="class: xl62"]e[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]5[/TD]
[/TR]
[TR]
[TD="class: xl62"]f[/TD]
[TD="class: xl62"]f[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]6[/TD]
[/TR]
</tbody>[/TABLE]


Formulas:

[TABLE="width: 696"]
<tbody>[TR]
[TD="class: xl62, width: 188"]Original List[/TD]
[TD="class: xl62, width: 198"]Duplicated List[/TD]
[TD="class: xl62, width: 128"][/TD]
[TD="class: xl62, width: 182"]Values[/TD]
[/TR]
[TR]
[TD="class: xl62"]a[/TD]
[TD="class: xl62"]=IF(F2<>"",C2,"")[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]1[/TD]
[/TR]
[TR]
[TD="class: xl62"]b[/TD]
[TD="class: xl62"]=IF(F3<>"",C3,"")[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]2[/TD]
[/TR]
[TR]
[TD="class: xl62"]c[/TD]
[TD="class: xl62"]=IF(F4<>"",C4,"")[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]3[/TD]
[/TR]
[TR]
[TD="class: xl62"]d[/TD]
[TD="class: xl62"]=IF(F5<>"",C5,"")[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]4[/TD]
[/TR]
[TR]
[TD="class: xl62"]e[/TD]
[TD="class: xl62"]=IF(F6<>"",C6,"")[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]5[/TD]
[/TR]
[TR]
[TD="class: xl62"]f[/TD]
[TD="class: xl62"]=IF(F7<>"",C7,"")[/TD]
[TD="class: xl62"][/TD]
[TD="class: xl62"]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

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.

Leave the Data Validation List exactly as you had it when you first posted this code, then add the following event code...

[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
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[darrenbutt]

Thank you to everyone who has replied, it is greatly appreciated.

Rick,

I entered the code you supplied and this works a treat. Thanks a bunch.

Regards,

Darren

 

[darrenbutt]

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.

 

[bhos123]

Sub LockCells()
Sheet1.Unprotect
Range("N1:N18").Select
Selection.Locked = True
Sheet1.Protect
End Sub


Sub UnlockCells()
Sheet1.Unprotect
Sheet1.Range("N1:N18").Locked = False 'Unlock the range A1 to B6
Sheet1.Protect
End Sub

First macro to lock and second to unlock the cells