I am trying to pass a Range Object from the Worksheet_SelectionChange event to another function and then set it to a public variable. This is a snippet of the code I have so far.
Code in "Sheet1"
Private Sub Worksheet_SelectionChange(ByVal Target as Range)
If Not Application.Intersect(Range("C8:C65000"), Range(Target.Address)) Is Nothing Then
eventPicker.showEventPickerForm Target
End If
End Sub
Code in "eventPicker" Form
Public eventTarget as Range
Sub showEventPickerForm(ByVal targetCell as Range)
Set eventTarget = targetCell
eventPicker.show
End Sub
Private Sub UserForm_Initialize()
MsgBox(eventTarget.Address)
End Sub
Running this code gives me the following error
Object variable or With block variable not set
with the debugger highlighting eventPicker.showEventPickerForm Target.
Commenting out the MsgBox(eventTarget.Address) line allows the code to run as expected.
I have tried moving the public variable declaration to outside the form in its own module, changing the MsgBox call to the following
With eventTarget
MsgBox(.Address)
End With
I've also tried passing the parameter ByRef as opposed to ByVal all returning the same error as above.
My thinking is that the object is being reset to null at some point as I can refer to eventTarget after the Set call in the showEventPickerForm function, but my research suggests that publicly declared variables will hold their value for as long as the workbook remains open. I am unsure how to proceed from here.
End SubinshowEventPickerFormand assigning the value in the selection change event gives the same error message.