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