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!