Using Worksheet_SelectionChange to run a Class subroutine

Posted on 2012-08-30
Last Modified: 2012-08-30
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
Question by:sjgrey
    1 Comment
    LVL 1

    Accepted Solution


    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

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    25th of every month 7 38
    excel forecast function 1 28
    Adding to a VBA? 6 34
    .xltm file opens as .xlsx file 3 18
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now