Error in VBA

Solved/Closed
Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016 - May 9, 2016 at 07:50 AM
Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016 - May 11, 2016 at 08:03 AM
Hey,

So I Have the following code:

Sub GesamtlisteAenderung(ByVal Target As Range)
Dim x As Integer
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
x = Target.Offset(0, -1).Value
ActiveSheet.Unprotect Password:="AufkeinenFallloeschen!"
Target.Offset(0, -1) = x
ActiveSheet.Protect Password:="AufkeinenFallloeschen!"

End Sub

and it works brilliantly! Buuuutttt.... Every Company has those that just can't handle Excel, no matter how simple we make it.

Problem is this: In Column B, there is a Dropdown Box with three Options: Mr., Ms., and Unknown.

Useres Need to pick on of the Options, and the code above kicks into gear in Column A.

But Some People inevitably try to enter words other than allowed. This creates an Error in VBA which calls for a Debugging. I Need to make that stop.

I Need this Workbook done ASAP, any Ideas or suggestions?

Thanks!

Newmie

1 reply

Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016
May 11, 2016 at 08:03 AM
Sub GesamtlisteAenderung(ByVal Target As Range)
On Error GoTo Abbrechen
Dim x As Integer
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
x = Target.Offset(0, -1).Value
ActiveSheet.Unprotect Password:="AufkeinenFallloeschen!"
Target.Offset(0, -1) = x
ActiveSheet.Protect Password:="AufkeinenFallloeschen!"

Abbrechen:
Exit Sub


End Sub
0