Userform - picking name from sheet

[Glaswegian]

Hi

I'm stuck with something I feel I should know - looks like another bad day...

Users work their way through several userforms in a workbook, but they also need the ability to save their progress, as they may need to obtain additional info to allow them to complete the workbook.

When the user opens the workbook to continue, they need to return to the form they last used. I have built in a 'Save As' function and at the same time captured the form name from which the save has been initiated. However, I'm having a problem recalling the previous form.

I know which form I want as the form name has been saved to a hidden sheet, but I cannot seem to open that form again.
For example, using the workbook open event


Dim frmName
If ThisWorkbook.Name <> "New Version Nov 12 v1.xls" Then
frmName = Sheets("Saves").Cells(Rows.Count, 1).End(xlUp).Value
frmName.Show
Else
frmOpen.Show
End If
which does not work (it opens the frmOpen form just fine).

I can't work out what I'm missing :banghead: .

Thanks for any help.

[Bob Phillips]

Sub Test()

If ThisWorkbook.Name <> "New Version Nov 12 v1.xls" Then
ShowUserFormByName Sheets("Saves").Cells(Rows.Count, 1).End(xlUp).Value
Else
frmOpen.Show
End If
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

[Glaswegian]

Hi xld

Apologies but I did not receive an email notification of your reply.

Needless to say that works beautifully - many thanks!

[Bob Phillips]

Iain, I see you are running 2010 on XP. Have you tried PowerPivot yet?