Restricted value sequences in rows

[yukon_phil]

I need to create a warning when specific values sequences are input from a
drop down menu. My example:

I have 31 columns (days in month) and have 25 rows (peoples names) from the
drop down box I have the following values-These are shift designations
(V1,V2,V3,V4, SV1,SV2,SV3,SV4, A1,A2,A3,A4,N,R1,R2,R3,DB,C).

If the selections in cell between B1:AF25 are either SV 1-4 or A 1-4 I want
a warning to appear IF a value of V 1-4, R 1-3, DB or C are attempted to be
put in the immediately following cell.

Example: Cell B3 has A-4 and in cel C3 they attempt to put N, then it
should give a warning "This is not a recommended shift sequence".

Your assistance would be greatly appreciated.

 

[vezerid]

Hi,

Paste the following code in the sheet's VBA code sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lft As Range
If Not Intersect(Range("B1:AF25"), Target) Is Nothing Then
Set lft = Target.Offset(0, -1)
toWatch = Left(lft.Value, 1) = "A" Or Left(lft.Value, 2) = "SV"
If toWatch Then
toComplain = Left(Target.Value, 1) = "V" Or Left(Target.Value, 1) = "R"
Or Target.Value = "C" Or Target.Value = "DB"
If toComplain Then
MsgBox "This is not a recommended shift sequence"
End If
End If
End If
End Sub

To do this, right-click the sheet-tab and choose View Code. This will
take you to the VBA IDE. Paste the code.

HTH
Kostis Vezerides

 

[yukon_phil]

Thanks for this, I copied the code and when I go into the worksheet I get an
error code as follows:

Compile Error:
Ambiguous Name detected; Worksheet_Change.

The following is the sequence of code already there the second one has the
same first line, could this be the issue. I have copied it below to show you
what is already in the "View Code"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long
lZoom = 100
lZoomDV = 120
lDVType = 0

Application.EnableEvents = False
On Error Resume Next
lDVType = Target.Validation.Type

On Error GoTo errHandler
If lDVType <> 3 Then
With ActiveWindow
If .Zoom <> lZoom Then
.Zoom = lZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> lZoomDV Then
.Zoom = lZoomDV
End If
End With
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Su
---------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B:AF"
Dim rng As Range
Dim Found As Range

Set rng = UsedRange.Columns(myColumn)
If Intersect(Target, rng) Is Nothing _
Or Target.Value = "" _
Then Exit Sub
Set Found = rng.Find(Target.Value)
If Found.Address <> Target.Address Then
Target.Select
MsgBox ("Duplicate code")
End If
End Sub

 

[vezerid]

Phil,
yes, the reason is exactly b/c you already have a Worksheet_Change
macro in the sheet. You will need to incorporate both functionalities
under the same name. I.e., you will have to insert just the BODY of my
macro (i.e. w/o the first and last line End Sub), in the existing
macro.

For this to be done properly you need to specify all the checks that
you need to perform. Which ones should disallow any further action,
which ones should just produce a warning and in what order you want to
make the various checks. Write back and we will somehow combine the two
(is there any more?)

Kostis

 

[yukon_phil]

The sequence as I see it is not an issue, I just need to get the "Warning"
messages to advise of the 'potential' issues, they should still be able to
make the choice of proceeding as in some cases it is required.

I do have another issue but I haven't yet been able to compile a clear and
concise explanation of what I am trying to resolve. Once I get it on paper
would you be good enough to look at it for me?

 

[vezerid]

Phil,
judging from the other thread, I believe that you should replace the
Worksheet_Change with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust next constant to your own needs
Const myColumn As String = "B:AF"
Dim rng As Range
Dim Found As Range
Dim lft As Range

Set rng = Target.cells(1,1).EntireColumn
If Intersect(Target, rng) Is Nothing _
Or Target.Value = "" _
Then Exit Sub
Set Found = rng.Find(Target.Value)
If Found.Address <> Target.Address Then
Target.Select
MsgBox ("Duplicate code")
End If

If Not Intersect(Range("B1:AF25"), Target) Is Nothing Then
Set lft = Target.Offset(0, -1)
toWatch = Left(lft.Value, 1) = "A" Or Left(lft.Value, 2) = "SV"
If toWatch Then
toComplain = Left(Target.Value, 1) = "V" Or Left(Target.Value, 1) =
"R" Or Target.Value = "C" Or Target.Value = "DB"
If toComplain Then
MsgBox "This is not a recommended shift sequence"
End If
End If
End If

End Sub

I think this should do it.

Regards,
Kostis

 

[yukon_phil]

I copied and pasted that code and I got a "Compile Error" Syntax Error and it
came up with the line highlighted below;

toComplain = Left(Target.Value, 1) = "V" Or Left(Target.Value, 1) =

I am gathering since it went this far through the code the code prior to the
highlighted line above is working and the problem is at this line, is that a
correct assumption.

 

[vezerid]

Phil,
just join the highlighted line with the next one. Both should be one
line but the web mailer broke them into to. I.e. place the cursor after
the = and press Delete until the next line comes after the =. You
should have a single line with three Or.

HTH
Kostis

 

[Gord Dibben]

That line and the one foillowing are all one line.

Enter a line-continuation character

toComplain = Left(Target.Value, 1) = "V" Or Left(Target.Value, 1) = _
"R" Or Target.Value = "C" Or Target.Value = "DB"

Note the space after the = sign and before the _ char.


Gord Dibben MS Excel MVP

 

[yukon_phil]

That did it, thank you very much.

This has been a big learning adventure for me, I think I have actually
picked up a few things now.

Would you mind if I send a few more questions to you directly OR should I
just post them as usual, these are specific to my work sheets.

 

[yukon_phil]

Thank you Gord for your input, Kostis explained to me the 'broken' line which
I corrected and it works fine now.

 

[vezerid]

Phil,

you are welcome to email directly. I think posting to the forum is the
preferred method for all since more people can benefit, plus you don't
have to rely on a specific individual. Thanks for the feedback too.

Regards,
Kostis