Excel 2000 Load Userform when clicking cell

S

scain2004

How would be the best way to load a userform when a cell in column c i
clicked.

When this column is clicked, it takes text in that cell and places i
in a label in the form.

Thank you for your help. :
 
R

Rob van Gelder

Here's one way:

Create a new userform: UserForm1
Drop a label control on it: Label1

Then in your worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim frm As UserForm1

If Target.Column = Columns("C").Column Then
Set frm = New UserForm1
frm.Label1 = Target.Value
frm.Show
Unload frm
Set frm = Nothing
End If
End Sub
 
B

Bob Phillips

Rob,

Can I ask why you create a form object and show that rather than just show.
Any specific advantages?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Rob van Gelder said:
Here's one way:

Create a new userform: UserForm1
Drop a label control on it: Label1

Then in your worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim frm As UserForm1

If Target.Column = Columns("C").Column Then
Set frm = New UserForm1
frm.Label1 = Target.Value
frm.Show
Unload frm
Set frm = Nothing
End If
End Sub
Click to expand...
 
R

Rob van Gelder

Just my style, but has another purpose too.

The way I originally did it was with .Show. Then the OK / Cancel buttons
would Unload Me to close the form.
But that left me without ability to return values the userform generated.

So I used this other approach which treated the userform more a black box.


I'd be interested to know how you resolve the problem of returning values
from a userform? Always looking at new ideas.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


Bob Phillips said:
Rob,

Can I ask why you create a form object and show that rather than just show.
Any specific advantages?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
B

Bob Phillips

One of two ways Rob.

Either don't unload the form, just hide it, and then as the form is still in
memory the variables and controls are still accessible. The disadvantage
here is obviously that the form is still in memory, but how big an overhead
that is will depend upon the application.

The other way I also use is to save the values in a standard module variable
before exiting the form.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top