Pass a variable to a userform

[Andy Gee]

Hi, I'm new to userforms hence all the questions! I have a userform which pops up when a cell is selected, it then enters the data into the cell selected. I thought I could just copy the userform and modify slightly for the other 200 cells in the column. Doh!

I think a better solution would be to use just one form and pass a variable to it, letting it know which cell to enter the details into.
The code I have for activating the userform is this...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$E$4" Then Ex1.Show
End Sub

Thanks Audiojoe!
But I need to send that cell to the userform.

Any ideas? Thanks in advance for any help.

[Audiojoe]

What do you mean when you say that you need to send that cell to the userform? Do you mean that it's value appears on there somewhere?

[Andy Gee]

No, I would like it to remember what cell was clicked, store it in a variable, and use that variable to enter the form details back into the clicked cell.



[ This Message was edited by: Andy Gee on 2002-04-11 08:56 ]

[Andy Gee]

Maybe if I could just store the Target.Address in a variable then use it when the command button is pressed I could get it to work.

I tried this with
cellName = (Target.Address)
When clicking the cell

and
Range(cellName).Value = Textbox1.Value
when the command button is pressed.
But I get an error message...
Method 'Range' of object '_Global' failed

Please help!

[LMF]

juts wondering how can u have spinner in the user form? it doesn't work for me, so i am wondering what have i done wrong?

i set the cell link to of the spinner to a offset refernce, can any1 please help me on this?

[Andy Gee]

I don't have a spinner! I need to be able to store a variable in one macro and use it in another.
Give me some more info on what you need and I'll try to help!

[ This Message was edited by: Andy Gee on 2002-04-11 09:21 ]