"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]

Disregard my last post -- I fixed that run time error -- there is a space after RATE in the sheet name.

 

[ocp_zoe]

Not related to the original error but hoping it's okay to post here:

If I want the message to pop up that several cells must have values is there a way to set that?

For example if the cells in range "B8:B11" need a value but could I also get B5 in there somehow?

 

[Fluff]

Yes, like this

Set rngCheck = Ws.Range("B5,B8:B11")

 

[ocp_zoe]

Thank you!!!

 

[Fluff]

You're welcome

 

[ocp_zoe]

I'm back...

This works when I hit F5 to run the command but does not work when the user is moving between cells. I'd like it to be instant.

Should I keep troubleshooting this here or find a different thread to address getting the error to pop up?

 

[Johnny C]

That's an easy fix.

Right-click on the tab name,and select 'View code'

Then copy this and paste it in there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim cel As Range
   Dim j As String
   Dim i As Integer

   i = 0
   For Each cel In activesheet.Range("B8:B11")
      If IsEmpty(cel) Then
         i = i + 1
         j = j & cel.Address & vbNewLine
      End If
   Next cel
   
   If i = 0 Then Exit Sub
   
   MsgBox "Sorry, you must enter a value in: " & vbNewLine & j
End Sub

You don't need to use Set for the range, the loop works quite happily with the range on it's own. It's good to know how to use Set for worksheets and workbooks but not necessary here.

You will see Target in the sub heading. It's superfluous here but usually if you're using a worksheet_SelectionChange object you want to do something with the selected cell. You would use that in the same way as you used the 'Me.' prefix, say you wanted test if the cell was blank you would use

If Target.Value = ""

I would personally replace

      If IsEmpty(cel) Then

with

      If cel.value = "" Then

but that's personal choice, for me it makes it easier to read

 

[ocp_zoe]

That's an easy fix.

Right-click on the tab name,and select 'View code'

Then copy this and paste it in there.

Johnny C,
I currently have this running in a module.
Right-clicking on the tab name brings up the worksheet under the Object listing.
Is that where you recommend putting this code? Rather than in a module?

Thank you!

 

[Johnny C]

No, not in the VBA editor. Go back onto the spreadsheet, and right click on the tab at the bottom. Where you'd change tab colour or hide it. You should see the option 'View code'. Each tab has it's own little area of VBA.

When it brings up the VBA window, there are 2 dropdown boxes at the top. Click the left one and choose 'Worksheet' (the only option).

This will automatically open a sub called 'Worksheet_SelectionChange(ByVal Target As Range)'

This is an 'event' subroutine. Which is to say, when the event 'SelectionChange' happens in the worksheet, i.e. you change the selected active cell, the code runs automatically. so every time you select a new cell, this code will run. Very useful!

Copy and paste the above code into that.

if you click on the right hand dropdown at the top, you will see there are a number of other specified events that can have code run automatically when they happen. So for example, when you double click a cell, if you select the 'Change' event, you can enter code that runs every time someone changes a value in the worksheet.

That particular one is very useful if you have a worksheet where you want to log every change - I used it recently to track changes to bank account details, the worksheet created bank account money transfer files (BACS), it needed to track every time someone changed bank account details to track potential fraud. The code logged the user login code, the time, what cell and values the bank details were changed for. This was kept in an audit worksheet which was changed to 'Very Hidden' in the VBA worksheet properties, so the user couldn't even see the very hidden worksheet to unhide the sheet and erase their footprint.

You can run code that happens when someone double clicks, when a pivot table is updated, loads of useful things.

A silly joke is to put the following code into the worksheet event code on a coworkers spreadsheet

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

MsgBox "Oy! The mouse isn't feeling very well today. Go easy on that mouse button!"

End Sub

Every time they double click the msgbox pops up.

 

[ocp_zoe]

Johnny C, I'm starting to catch on (I think). Your code worked for that set of cells and that message. How would I set a different message for a different set of cells? I tried this but got a highlight error on the first row of text:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim cel As Range
Dim j As String
Dim i As Integer


i = 0
For Each cel In ActiveSheet.Range("B9,B14:B16")
If IsEmpty(cel) Then
i = i + 1
j = j & cel.Address & vbNewLine
End If
Next cel

If i = 0 Then Exit Sub

MsgBox "Seriously, Dave? Fill in the box. "



For Each cel In ActiveSheet.Range("F16,B17")
If IsEmpty(cel) Then
i = i + 1
j = j & cel.Address & vbNewLine
End If
Next cel

If i = 0 Then Exit Sub

MsgBox "OMG! Fill in the box. "
End Sub