Error message because of worksheet name

[ipbr21054]

Morning,
I have the code shown below.

I see messages like expected end of statement.
This is because the worksheet is called MC LIST

As a test i changed the name to LIST & its fine but why am i unable to have MC LIST

Does the space have any issues between the two words ?

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        Dim fndRng As Range
        Dim FindString As String
        Dim i As Integer
        Dim wsMC LIST As Worksheet
        
        FindString = Me.TextBox1.Value
        If Len(FindString) = 0 Then Exit Sub
        
        Set wsMC LIST = ThisWorkbook.Worksheets("LIST")
        i = 1
        Do
            Set fndRng = Nothing
            Set fndRng = wsMC LIST.Range("A:A").Find(What:=FindString & Format(i, " 000"), _
                                                        LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                                                        SearchDirection:=xlNext, MatchCase:=False)
            If Not fndRng Is Nothing Then
                i = i + 1
                Cancel = True
            End If
        Loop Until fndRng Is Nothing
        
        Me.TextBox1.Value = FindString & Format(i, " 000")
        Cancel = False
        
    End Sub

 

[RoryA]

You cannot have a space in the name of a variable.

 

[ipbr21054]

So i must use MCLIST then ?

 

[RoryA]

No, you could use wsMC_LIST for example. The basic rules are:

• You must use a letter as the first character.
• You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
• Name can't exceed 255 characters in length
• Avoid using names that are keywords in VBA or the object models you are using (some you can't use, the rest are just better avoided)

 

[ipbr21054]

Ok thanks,Its strange as other tabs are say MC NAME MC INFO etc etc & work fine.
I will do what youve advised

Thanks

 

[RoryA]

The variable name has nothing to do with what is on the tab of a worksheet.

 

[ipbr21054]

But in the vba code its shown as same as tab which is why i dont understand

 

[RoryA]

It can't be. You cannot have a space in a variable name.