• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

Using Worksheet_SelectionChange to run a Class subroutine

I want to use Worksheet_SelectionChange to run a refresh routine in Excel when the data on the front sheet is changed.  The refresh routine lies within a class object.  I can call the routine from an ordinary module but when I embed it in Worksheet_SelectionChange I get "Run time error '91' - Object variable or With block variable not set"

The clsRange_display class module contains all the necessary declarations and a routine as indicated below

Sub UpdatePoints()

Dim i As Integer, j As Integer

'   Read the current values
    
For i = 1 To 5

    strEntry(i) = Range("RangeInput").Cells(1, i).Value

Next i

...

End Sub

Open in new window


The Module code that runs first and calls this is as follows

Sub Analyse2()

Dim Display As clsRange_display

Set Display = New clsRange_display

Call Display.UpdatePoints

Call Display.Draw

End Sub

Open in new window


This works as expected but the event handler as follows does not

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Display As clsRange_display

    Call Display.UpdatePoints

End Sub

Open in new window


I'm new to object oriented stuff so I expect I'm getting tangled in the class declaration
0
sjgrey
Asked:
sjgrey
1 Solution
 
sjgreyAuthor Commented:
SOLVED

I had to make the class declaration public in the ordinary module, as follows

Option Explicit
Option Base 1
Public Display As clsRange_display

Open in new window


then get rid of unnecessary declarations in subroutines - all fixed
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now