Public Sub cellnames()
Dim cell As Range
For Each cell In Selection
If cell.Value < "" Then
ShowUserFormByName cell.Value & "form"
End If
Next cell
End Sub

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = UserForms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & _
" was not found.", vbExclamation, "Load userforn by name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Load userforn by name"
End Select
End Sub




--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pjbur2005" <u18722@uwe wrote in message news:5be305b1c3006@uwe...
Hi there I have a worksheet form that has vba to manage it what i am stuck
on
is i want to take the name of an active cell add the word "form" to it
and
then use this to call a userform

I expect to have more user forms than below in time
here is the code I have been using (if thens etc)

Public Sub cellnames()
Dim cellname As Name
Dim cellval

On Error Resume Next
Set cellname = Selection.Name
If cellname Is Nothing Then
Exit Sub
Else
If cellname.Name = "Reason" Then
reasonform.Show
End If
If cellname.Name = "carecategory" Then
carecategoryform.Show
End If
If cellname.Name = "Team" Then
Teamform.Show
End If
If cellname.Name = "caretype" Then
Caretypeform.Show
End If
If cellname.Name = "Eligibility" Then
eligibilityform.Show
End If
End If

End Sub

What i want to be able to do is something like

list = worksheets("panel form").range("formlist") ' would store list on
hidden worksheet
for each list in formslist
if cellname.name = list then ' say cellname.name = team
showform = list & "form" ' say list = team
showform.show ' i actually want to show user form teamform not showform
end if
next

Would be gfrateful for some help as the later code is far tidier and would
be
easier to manipulate.

Thanks
Paul