Using cell name to call userform

[pjbur2005]

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

 

[Bob Phillips]

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)

 

[RB Smissaert]

Sub ShowForm(strForm As String)

Dim oForm As Object
Set oForm = VBA.UserForms.Add(Application.Proper(strForm))
oForm.Show

End Sub

Sub Test()
ShowForm "UserForm1"
End Sub

Now just use the text of the cell to load the form.

RBS

 

[pjbur2005 via OfficeKB.com]

Hi Bob

thanks very much for your excellent reply, I just could not see the wood for
the trees, I made a small change to suit my direct needs but worked great.
if you see any potential problems with this let me know.

Public Sub cellnames()

Dim cellname As Name
On Error Resume Next
Set cellname = Selection.Name ' this rather than cells as range
If cellname Is Nothing Then
Exit Sub
Else

ShowUserFormByName cellname.Name & "form"
End If
End Sub

show userformbyname sub needed no change, may not end up using error trap as
i exit during cellnames if nothing but have left it just in case.

My sincere thanks for your reply

best wishes

Paul

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)

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

[quoted text clipped - 47 lines]

Thanks
Paul

 

[Bob Phillips]

Paul,

I must admit that names bit confused me in the original post.

I can't see any problems with that, should work fine.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

Hi Bob

thanks very much for your excellent reply, I just could not see the wood for
the trees, I made a small change to suit my direct needs but worked great.
if you see any potential problems with this let me know.

Public Sub cellnames()

Dim cellname As Name
On Error Resume Next
Set cellname = Selection.Name ' this rather than cells as range
If cellname Is Nothing Then
Exit Sub
Else

ShowUserFormByName cellname.Name & "form"
End If
End Sub

show userformbyname sub needed no change, may not end up using error trap as
i exit during cellnames if nothing but have left it just in case.

My sincere thanks for your reply

best wishes

Paul

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)

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
[quoted text clipped - 47 lines]

Thanks
Paul