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)

"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

[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" <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

[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

Bob Phillips wrote:
>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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200602/1

[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)

"pjbur2005 via OfficeKB.com" <u18722@uwe> wrote in message
news:5bea0eb4e0105@uwe...
> 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
>
> Bob Phillips wrote:
> >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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1