Syntax error when trying to make a drop down list hidden if certain values are selected

[benprelf]

I am trying to hide a userform when certain values from a drop down list matching a range of text are selected.

If the dropdown choice contains a different value than what is stored in the range I'd like the userform to be shown.

[B]Sub EXMProceed2_Click()[/B]


[B]Dim CheckFibre As String[/B]
[B]Dim FibreInsertsVBA As String[/B]

[B]CheckFibre = Worksheets("CreateConnectorEXM").range("J10") 'Drop down list[/B]
[B]FibreInsertsVBA = Worksheets("DATA").range("FibreInserts")     'Data Range is named "FibreInserts" via the excel name manager functions[/B]

[B]If CheckCable = ("MM") And CheckFibre <> FibreInsertsVBA Then[/B]
[B]FiberArrangements.Show[/B]
[B]    Else[/B]

[B].....[/B]

[B]End If 
[/B]

It is returning a Syntax error, any help would be much appreciated.

 

[Andrew Poulsom]

Which line is causing the error? Why is range in lower case rather than proper case?

 

[benprelf]

FibreInsertsVBA = Worksheets("DATA").range("FibreInserts")

This is the line causing the error. The latest error is a "type mismatch" with this line.

Apologies I am not sure what you mean by the range being in the lower case rather than the proper case.

 

[Andrew Poulsom]

range should be Range. Do you have a procedure named range in your workbook?

 

[benprelf]

Apologies, when I use the term "range" it automatically goes to lowercase. There is a procedure in the workbook named "Range" in the proper case however when I use it in the code it auto corrects itself to lowercase.

The code works if I take out the "range" and replace it with a single text value. I just can't seem to get it to work for the rest.

Dim CheckCable As String
Dim CheckFibre As String
Dim FibreInserts As Variant


CheckCable = ActiveSheet.range("D10")
CheckFibre = Worksheets("CreateConnectorEXM").range("J10")
FibreInserts = Worksheets("DATA").range("A6:A11").Value 'There is a group of cells on this worksheet under the title FibreInsertsVBA but when I reference this group nothing happens.

If CheckCable = ("MM") And CheckFibre <> FibreInserts Then

FiberArrangements.Show
    Else

 

[Andrew Poulsom]

What happens if you remove the procedure Range?

 

[benprelf]

If I remove Range I receive a "Type mismatch" error on this line:

If CheckCable = ("MM") And CheckFibre <> FibreInserts Then

 

[Andrew Poulsom]

What happens if you remove the parentheses around "MM"?

 

[benprelf]

If I remove the parentheses I receive a "Type mismatch" error on the same line as before.

 

[Andrew Poulsom]

Does this work for you?

    Dim CheckCable As String
    Dim CheckFibre As String
    Dim FibreInserts As Range
    CheckCable = ActiveSheet.Range("D10").Value
    CheckFibre = Worksheets("CreateConnectorEXM").Range("J10").Value
    Set FibreInserts = Worksheets("DATA").Range("A6:A11")
    If CheckCable = "MM" And WorksheetFunction.CountIf(FibreInserts, CheckFibre) = 0 Then