Using a variable to call either a form or a sheet

[paulked]

Hi

Is it possible to do this?

    If Left(W, 3) = "frm" Then
        W.Show
    Else
        W.Activate
    End If

I would like to send the user back to whence they came, which could be another sheet of a user form. All my UF's start with "frm" so I thought that the above could work. It doesn't!

Any ideas?

Thanks

Paul Ked

[Paul_Hossler]

Nothing is ever that easy in Excel


test() does a userform, and test2() does a worksheet


Only difference is what W is set to


The only gotcha is a UF must be loaded to be in the UserForms collection (based a 0)

Option Explicit
Sub test()
    Dim W As String
    Dim i As Long
    
    W = "frmOne"
    
    Load frmOne '   MUST be loaded

    If Left(W, 3) = "frm" Then
        For i = 0 To UserForms.Count - 1    '   start at 0
            If UserForms(i).Name = W Then UserForms(i).Show
        Next I
    Else
        Worksheets(W).Activate
    End If
End Sub



Sub test2()
    Dim W As String
    Dim i As Long
    Dim oUF As Object
    
    W = "Sheet1"
    
    Load frmOne

    If Left(W, 3) = "frm" Then
        For i = 1 To UserForms.Count
            If UserForms(i - 1).Name = W Then UserForms(i - 1).Show
        Next I
    Else
        Worksheets(W).Activate
    End If
End Sub

[paulked]

That's great Paul, thanks

[Aflatoon]

How are you assigning a value to W? Assuming it's an Object variable, you can simply test its type using either Typename or TypeOf.

[SamT]

How are you triggering the "Send User Back" event?

ThisWorkbook Code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Set CurrentObject = Sh
End Sub

USerForm(s) Code:

Private Sub UserForm_Activate()
Set CurrentObject = Me
End Sub

Module Code:

Public PreviousObject As Object
Public CurrentObject As Object

Public Sub SendBack()
Dim W As Object
Set W = CurrentObject

If PreviousObject Is Null Then Exit sub 

 If PreviousObject Is UserForm Then '??? How to test? 
  PreviousObject.Load
 Else: PreviousObject.Activate 'Is Worksheet
 End If
 
Set PreviousObject = W

End Sub