Use cell values as object names
 

I'm trying to ".Show" a user form by using a cell value as the UserForm name.
The user forms are already created and the "(Name)" property of each form is
included in a list on a worksheet. I would like to use the "SelectionChange"
event to "Show" the user form that matches the value of the cell the user
selects. My problem is getting the cell value (string) into the proper
format for
the Show statement: "(substitute cell value here).Show"
Can anyone provide the secret to getting a string value to work as a
legitimate
object name (UserForm in this case)?

Thanks.

MST

Use cell values as object names
 

Assuming that the names of the userforms are in cells A1:A3, use code like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A3")) Is Nothing Then
With VBA.UserForms
Do Until .Count = 0
.Item(0).Delete
Loop
.Add Target.Text
.Item(0).Show
End With
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"MST" wrote in message
...
I'm trying to ".Show" a user form by using a cell value as the UserForm
name.
The user forms are already created and the "(Name)" property of each form
is
included in a list on a worksheet. I would like to use the
"SelectionChange"
event to "Show" the user form that matches the value of the cell the user
selects. My problem is getting the cell value (string) into the proper
format for
the Show statement: "(substitute cell value here).Show"
Can anyone provide the secret to getting a string value to work as a
legitimate
object name (UserForm in this case)?

Thanks.

MST

Use cell values as object names
 

That works. Thanks. I never would have figured it out on my own

MST

"Chip Pearson" wrote:

Assuming that the names of the userforms are in cells A1:A3, use code like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A3")) Is Nothing Then
With VBA.UserForms
Do Until .Count = 0
.Item(0).Delete
Loop
.Add Target.Text
.Item(0).Show
End With
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"MST" wrote in message
...
I'm trying to ".Show" a user form by using a cell value as the UserForm
name.
The user forms are already created and the "(Name)" property of each form
is
included in a list on a worksheet. I would like to use the
"SelectionChange"
event to "Show" the user form that matches the value of the cell the user
selects. My problem is getting the cell value (string) into the proper
format for
the Show statement: "(substitute cell value here).Show"
Can anyone provide the secret to getting a string value to work as a
legitimate
object name (UserForm in this case)?

Thanks.

MST