Pass Cell Reference to Submit Button

[quirkycanuck]

I have a Submit Button on a Userform that I'm trying to use to pass data from a listbox back into a specific cell on my worksheet. The sequence is as follows:

Worksheet_BeforeDoubleClick sub calls the userform
Userform displays the list box and user can select multiple options
Submit button under Listbox is clicked, and then the listbox items that are checked go back into the cell that was double-clicked.

The trick is passing the range variable. So far I've gotten half way there. I now have code in my doubleclick sub that passes the range address to a cell on the worksheet from where I can then hopefully retrieve it.

I can't figure out how to go in the other direction and convert "A1" in the cell on my worksheet into a range so that I can use that to populate the data.

How can I pass the address of the doubleclick to my submit button? Is there a simpler way, or would someone at least have the tidbit I need to convert the string "A1" on the spreadsheet back to a range in my VBA?

Thanks

 

[Fluff]

Hi
How about
In the doubleclick event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Set Cl = Target
    UserForm1.Show
End Sub

On your button

Private Sub CommandButton1_Click()
Cl.Value = "Hi"
Unload Me
End Sub

and then at the very top of a standard module put

Public Cl As Range

 

[FormR]

or would someone at least have the tidbit I need to convert the string "A1" on the spreadsheet back to a range in my VBA?

Hi, for example, this will put a 1 in the cell address that is mentioned in cell A1 of the active sheet.

Range(Range("A1").Value).Value = 1