1

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.

5
  • Break on Change shows the object being set to 'Nothing' on call of End Sub in showEventPickerForm and assigning the value in the selection change event gives the same error message. Commented Jan 27, 2018 at 8:55
  • Some discussion here: ozgrid.com/forum/forum/help-forums/excel-general/… Commented Jan 27, 2018 at 9:02
  • In relation to link ^^ I think you can store values in named ranges that don't actually have a sheet range address. Trick I think I have seen elsewhere. Commented Jan 27, 2018 at 9:04
  • And see here: stackoverflow.com/questions/17288997/… Commented Jan 27, 2018 at 9:06
  • Thank you, moving the showEventPickerForm sub to a module along with the public variable fixed the issue. I looked at that question earlier but did not appreciate the distinction of moving just the public variable and both the sub and public variable Commented Jan 27, 2018 at 9:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.