Use cell values as object names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
Back
Top