PDA

View Full Version : [SOLVED] Using a variable to call either a form or a sheet



paulked
12-22-2017, 03:15 PM
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
12-22-2017, 05:00 PM
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
12-22-2017, 06:49 PM
That's great Paul, thanks :beerchug:

Aflatoon
12-27-2017, 08:00 AM
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
12-28-2017, 08:24 AM
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