Linked Questions

Popular Questions

I have a class module which hosts a worksheet_change sub, and in that sub a Userform has to pop up. I want to use a number of variables from the class module in the Userform's code. Whatever I do, however, I can't get it to work.

I have tried to apply the method from this very lenghty guide, but to no avail. Other threads on SO weren't able to help me.

Private cell As Range

Public WithEvents m_wb As Workbook

Property Get cellr() As Range
    Set cellr = cell
End Property

Property Set cellr(cellrange As Range)
    Set cell = cellrange
End Property

Public Property Set Workbook(wb As Workbook)
    Set m_wb = wb
End Property

Public Property Get Workbook() As Workbook
    Set Workbook = m_wb
End Property

Public Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'simplified, but accurate
    Application.EnableEvents = False

    For each cell in Target
        ReplaceTask.Show
    Next cell

    Application.EnableEvents = True
End Sub

In the userform_initialize macro, I need to be able to get the name of the m_wb workbook, as well as the cell (preferably as a range variable, otherwise just the address) in the For each cell in Target loop. For each variable in the code below I get

Error '424' object required

which shows the variables are not public..

Private Sub UserForm_Initialize()
Debug.Print cellrange.Address
Debug.Print cell.Address
Debug.Print cellr.Address
Debug.Print m_wb.Name
'....

I am positive it's my inability to understand how these properties work that's holding me back.. If someone could shine some light on what I am doing wrong, please!

Related Questions