Excel 2000 Load Userform when clicking cell
 

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. :

--
Message posted from http://www.ExcelForum.com

Excel 2000 Load Userform when clicking cell
 

Also, it could be any cell in column c...

--
Message posted from http://www.ExcelForum.com

Excel 2000 Load Userform when clicking cell
 

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


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


"scain2004 " wrote in message
...
How would be the best way to load a userform when a cell in column c is
clicked.

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

Thank you for your help. :)


---
Message posted from http://www.ExcelForum.com/

Excel 2000 Load Userform when clicking cell
 

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" wrote in message
...
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


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


"scain2004 " wrote in message
...
How would be the best way to load a userform when a cell in column c is
clicked.

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

Thank you for your help. :)


---
Message posted from http://www.ExcelForum.com/

Excel 2000 Load Userform when clicking cell
 

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" wrote in message
...
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" wrote in message
...
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


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


"scain2004 " wrote in message
...
How would be the best way to load a userform when a cell in column c
is
clicked.

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

Thank you for your help. :)


---
Message posted from http://www.ExcelForum.com/

Excel 2000 Load Userform when clicking cell
 

hide the form
harvest the values
unload the form

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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


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


"scain2004 " wrote in
message
...
How would be the best way to load a userform when a cell in column c
is
clicked.

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

Thank you for your help. :)


---
Message posted from http://www.ExcelForum.com/

Excel 2000 Load Userform when clicking cell
 

Tom,

Thanks for your input.

This is the method I'm now using.

Cheers

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


"Tom Ogilvy" wrote in message
...
hide the form
harvest the values
unload the form

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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


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


"scain2004 " wrote in
message
...
How would be the best way to load a userform when a cell in column
c
is
clicked.

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

Thank you for your help. :)


---
Message posted from http://www.ExcelForum.com/

Excel 2000 Load Userform when clicking cell
 

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)

"Rob van Gelder" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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


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


"scain2004 " wrote in
message
...
How would be the best way to load a userform when a cell in column c
is
clicked.

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

Thank you for your help. :)


---
Message posted from http://www.ExcelForum.com/

Excel 2000 Load Userform when clicking cell
 

Your the one that indicated there was a difference.

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in message
...
Tom,

Thanks for your input.

This is the method I'm now using.

Cheers

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


"Tom Ogilvy" wrote in message
...
hide the form
harvest the values
unload the form

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in
message
...
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" wrote in message
...
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" wrote in
message
...
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


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


"scain2004 " wrote in
message
...
How would be the best way to load a userform when a cell in
column
c
is
clicked.

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

Thank you for your help. :)


---
Message posted from http://www.ExcelForum.com/