"VBA Error - "invalid use of Me Keyword"

[Johnny C]

I've got a userform which monitors progress of some VBA.

In a sub called by the userform.activate event, there are a load of Me.<> statements which update things on the userform.

it was working fine but I've made a lot of changes - nothing to do with the userform, just processing changes, and haven't run the macro for a few weeks.

Now I've jsut started it for final testing, and it falls over on compile with the following error:

"Invalid use of Me Keyword"
The line it's falling over on (which was fine before) is

    Me.lblCurrentStatus.Caption = "Initialising - clearing old data"

The error help isn't much use, it's just about using Me in class modules, not ordinary modules, and as I say it was working fine before.

Can anyone shed light on this?

TIA

 

[ocp_zoe]

The first line is highlighted:

Private Sub Worksheet_Deactivate()

 

[Fluff]

Check all the code in the workbook & make sure that you don't have any subs or variables called Me

 

[ocp_zoe]

How would I check that?

*I always feel compelled to mention that I am not very familiar with these type of Excel tasks and thank you 1000x for even responding.

 

[Fluff]

In the VB Editor hit Ctrl F Enter Me into the Find What box, select Current Project & then click Find Next

 

[ocp_zoe]

I see Me in some drop down lists but I don't see any drop down lists in this XL.

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "=DropDownList"
Me.ComboBox1.DropDown
End Sub


Private Sub ComboBox2_GotFocus()
ComboBox2.ListFillRange = "=DropDownList2"
Me.ComboBox2.DropDown
End Sub

I also see Me here:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim xStr As String
Dim xCombox As OLEObject
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Application.EnableEvents = False
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
Application.EnableEvents = True
End Sub

 

[Fluff]

Those are all fine.
What is the name of the sheet with the deactivate event?

 

[Ruddles]

Please let the sheet not be called Me!

 

[ocp_zoe]

Please let the sheet not be called Me!

Even I know that would be hilarious!

Sheet name is RATE

 

[Fluff]

Ok, try this

Private Sub Worksheet_Deactivate()
   Dim rngCheck As Range
   Dim cel As Range
   Dim j As String
   Dim i As Integer
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Rate")
   Set rngCheck = Ws.Range("B8:B11")
   
   i = 0
   For Each cel In rngCheck
      If IsEmpty(cel) Then
         i = i + 1
         j = j & cel.Address & vbNewLine
      End If
   Next cel
   
   If i = 0 Then Exit Sub
   
   Ws.Activate
   MsgBox "Sorry, you must enter a value in: " & vbNewLine & j
End Sub

 

[ocp_zoe]

Run-time error '9':
Subscript out of range