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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.