Is it possible to pass a range to a custom property?
I am trying to adapt the code I found here (the third method, as this is what seems to be the best practice) to suit my needs, but I have no luck - the code I have so far, more or less copied from this page. as follows:
In the form module:
Private calling_cell As Range
Property Set range_to_form(ByRef r As Range)
Set calling_cell = r
End Property
Private Sub UserForm_Activate()
Debug.Print calling_cell.Address
End Sub
In workheet_change-event:
Dim frm As ufRegLuft
If Not IsUserFormLoaded("ufRegLuft") Then
Set frm = New ufRegLuft
Else
Set frm = VBA.UserForms("ufRegLuft")
End If
Set frm.range_to_form = Target
ufRegLuft.Show
Problem:
This does not work. I get an error on debug.print
-line saying "Runtime Error 91: Object variable or with block variable not set". I must admit that I am currently strong at a dead end, feel like I've tried all kinds of combinations, come on, get it, etc. So, anyone here please help me figure out if it is possible to pass Range
- an object to a custom form, and if so, tell me what am I doing wrong?
source to share
Your variable is calling_cell
initialized in the form frm
, not inufRegLuft
Change the line ufRegLuft.Show
to frm.Show
;)
Here's an easy way to test it.
Form module
Private calling_cell As Range
Property Set range_to_form(ByRef r As Range)
Set calling_cell = r
End Property
Private Sub UserForm_Activate()
MsgBox calling_cell.Address
End Sub
Regular module
Sub ShowFormProp()
Dim frm As ufRegLuft
Set frm = New ufRegLuft
Set frm.range_to_form = Sheet1.Range("A1")
frm.Show
End Sub
source to share
You have to pass the variable to sub in the form (I'm sure the user form_Initialize will fire)
I'm not sure where r is set, but create a public element on the form that sets the range, call that before you show the form, and show the form like below.
In the form module:
Private calling_cell As Range
public sub range_to_form(ByRef r As Range)
Set calling_cell = r
End Property
Private Sub UserForm_Activate()
Debug.Print calling_cell.Address
End Sub
In workheet_change-event:
Dim frm As ufRegLuft
If Not IsUserFormLoaded("ufRegLuft") Then
Set frm = New ufRegLuft
Else
Set frm = VBA.UserForms("ufRegLuft")
End If
frm.range_to_form r
'Could be "range_to_form r" not above
frm.Show
source to share