CODEWITHSUNDEEP
vbaexcel
Joel da Cunha
Joel da Cunha

Reputation: 13

EXCEL VBA - Make a msgbox warning if cell is matches content of another cell

I'm a school principal and I'm trying to make a scheduling managment app in excel.

I have 1 sheet for each class and 1 sheet for each teacher.

All sheets are the same and have the important information on the same coordinates.

For now, I've been able to make the classes timetables copy over to the teacher's and It works Like a charm. I make the students timetables and it automatically makes the teacher's.

Now I have a bit of a problem.

If I assign a teacher to, let's say Monday at 8.30 to 1 class and then, by mistake assign the same teacher to another class it overlaps and carries over the first class to the teacher. Last week the same teacher was assign to 2 classes at the same time.

So my question is this:

is there a way to make (on cell change) a vba script that checks if cell b29 has the same value on other sheets, regardless of the sheet's possition and name?

ex:

I assign teacher A to Class 1 - Monday 8.30 where the teacher name and date/hour is cell B29, the class is cell d5 and sheet name.

If I assign, in another class, the same teacher to b29 if would display a msgbox saying "There is an overlap".

What I have the most trouble with is that every year the classes and teachers change and the relative position of the sheets also change due to the year of the course and the number of the courses/classes,

Thanks!

Upvotes: 1

Views: 715

Answers (1)

adhy wijaya
adhy wijaya

Reputation: 509

I think the code would be like this: copy this code to sheet(code):

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B29")) Is Nothing Then
        If Selection.Cells.Count = 1 Then
            Result = getTime(Range("B29").Value)
        End If
    End If

End Sub

copy this code to Module1

Function getTime(TimeAdd As Date)

    For Each ws In Worksheets
        If ws.Name <> ActiveSheet.Name Then
            If ws.Range("B29").Value = TimeAdd Then
                Result = True
                Exit For
            End If
        End If
    Next

    If Result = True Then MsgBox "There is an overlap."

End Function

Hope this help

Upvotes: 1

Related Questions