Check value in other sheet, sheetname entered in cell

[miicker]

I am using a sheet where a user can enter (pick from drop down list) the name of the sheet, then data is gathered from that sheet using indirect formulas.
This all works great, but there is one type of sheet I don't wan't to allow.

The user enters the sheet name in cell D6. I have a built in parameter in the entered sheet in cell Q2. When that parameter is filled with for example the text "yes", than the user should get a msgbox telling the user that the entered sheet cannot be used, and the cell D6 should stay empty.

So I would like to build some code that checks the following:

IF *name of the sheet entered in cell D6*!Q2 = yes THEN
MsgBox ("The sheet u entered cannot be used")
Range("D6").Value = ""
End If

I cannot figure out how what code to use for the part between **.

Thanks in advance!

 

[My Aswer Is This]

Why not only load the proper sheet names into the drop down list.

 

[miicker]

because it was already a pain in the *** to get it working like it's working now. The sheet names are also changing, and I already exclude some sheet names.