VBA - INDEXMATCH for a Worksheet Change Event or another approach by not showing already selected items in dropdown

[The Godfather]

Hi,

I have an excel with two sheets as "form" and "database".
In the form sheet, various users type their input into rows 9-12, and before posting each user selects his/her username, type password, and select week in Cell F7 (which is a dropdown where options are Week 1, Week 2, Week 3, etc.)

In the database sheet, usernames are stored in C Column and weeks are stored in D Column.


I want to disable users to select weeks that they have already posted before. For instance, user1 should not be able to post again for weeks 12 and 13, but he will be free to post to week11 (or before) and week 14 (and after).

At first, into the CommandButton to post entries, I was able to put some code to warn users that they've already posted that week before. However, this option is not so good because when the user will select another week, all the input in the form sheet resets (I need it that way for some reason), and they will have to manually enter lots of info again.

So, I have two ideas to solve this, but was not able to implement any of those approaches. I'd appreciate any help to implement both approaches (I'll implement one in the final version of workbook, however I'd like to learn the solutions to both approaches);

Approach 1 (which is more ideal): In form sheet, username should also be typed into cell F5. So, with some help of VBA, maybe only the weeks that have not been posted by that user may be shown in dropdown (Dropdown is being sourced from another sheet, no VBA used)

Approach 2 (which is not ideal but OK): When a week that have already been posted is selected by the user, immediately a MsgBox will appear stating "You've already posted to that week before", and not allow to post.

I could not found any solution to Approach 1. However for Approach 2 I tried to type some code into Worksheet Change event using INDEXMATCH. But it returns the following error;

The code typed as follows;

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim frm As Worksheet
    Dim database As Worksheet
 
    Set frm = ThisWorkbook.Sheets("Form")
    Set database = ThisWorkbook.Sheets("Database")
    Dim a As Range

If Not Intersect(Range("I7"), Range(Target.Adress)) Is Nothing Then
    Dim b As Variant
    b = Application.WorksheetFunction.Index(database.Range("D"), Application.WorksheetFunction.Match(a, database.Range("C"), 0))
If b = False Then
MsgBox "this record already selected"
End If
End If

End Sub

Thank you for reading.

 

[The Godfather]

Dear all, any help would be appreciated. Thanks.

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).