VBA to change UserForm

[K1600]

Hi, I have a setup of two workbooks (WB1 & WB2) where WB1 contains my UserForm and WB2 contains the master data.

As it stands, when you open WB1 it automatically loads the UserForm using the below code, however, I would like to option of using a command button in WB2 to change the UserForm shown in WB1 from UserForm to UserForm2. UserForm2 is basically an 'Under Maintenance' page asking users to come back later. I would then have a second command button to do the reverse once maintenance is complete.

Is this possible?


Thanks in advance,

Glynn

Private Sub Workbook_Open()
    Application.WindowState = xlMinimized 'user cannot see what is entered in Excel
    UserForm.Show
End Sub

 

[Domenic]

To change the userform shown in WB1 from another workbook, first you'll need to add a procedure in WB1 that will show UserForm2. Then you'll be able to call this procedure from your other workbook using Application.Run.

WB1, regular module:

[FONT=Courier New][COLOR=darkblue]Public Sub[/COLOR] ShowUserForm2()
    [COLOR=green]'UserForm1.Hide[/COLOR]
    UserForm2.Show
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

From your other workbook...

Application.Run "'WB1.xlsm'!ShowUserForm2"

Hope this helps!

 

[Jeffrey Mahoney]

Please try this. Create a named range on a sheet called setup as a workbook level named range called "UserFormName".


This is the code for the button to set the userform name before opening the WB1. You need to reset this after opening the file. Put this in the code for the button or in a standard module and call it from the button sub.

Sub SetUserForm2()
    Sheets("Setup").Range("UserFormName").Value = "UserForm2"
  
End Sub

Put this in a standard module

'Code provided by ZVI from the MrExcel.com Site
' Get UserForm object defined by its string name
Function Form(Name As String) As Object
  Set Form = CallByName(UserForms, "Add", VbMethod, Name)
End Function

This is the code that runs in your WB1 workbook. Put in your workbook module

Private Sub Workbook_Open()
    
  Dim WB As Workbook
  Dim Sht As Worksheet
  Dim NR As Name
  Dim UFN As String
  Dim ShtName As String
  Dim A As String


  'Application.WindowState = xlMinimized 'user cannot see what is entered in Excel


  For Each WB In Workbooks                                'Look at each open workbook (the file name may change, so were just going to look for the named range)
    For Each NR In WB.Names                               'Go through each named range and find the Named range on the setup sheet in WB2
      If NR.Name = "UserFormName" Then             'Found the named range
        A = NR.RefersTo                                                  'contains the formula reference to the sheet and range
        A = Replace(Mid(A, 2, InStr(A, "!") - 2), "'", "")      'Clean out just the sheet name
        Set Sht = WB.Sheets(A)                                       'set the sheet
        UFN = Sht.Range("UserFormName").Value            'get the userform name
        Exit For
      End If
    Next NR
  Next WB
  
  If UFN <> "" Then                                                    'if not blank open that form
    Form(UFN).Show
  Else                                                                        'it is blank, just open userform1
    UserForm1.Show
  End If
    


End Sub

 

[K1600]

Thanks both,

Despite trying I can't seem to get your (Jeffrey) suggestion to work. I am very new to VBA so I'm not sure if I'm missing something or if I need to change anything. I've done what you have said (at least I think I have done it correctly) and at present when I click the attached button is fills the 'UserFormRange' with "UserForm2" but nothing seems to change and when I try and open WB1 it cycles through a variety of errors.

Please accept my apologies if I am being a little thick here.


Glynn

 

[Jeffrey Mahoney]

Please confirm that you put the first code section in the WB2 file, which I think you did. The other two sets of code need to be in the WB1 workbook. I got it to work on my end. Can you tell which lines were in error and what the error was. That way I can help you.

Jeff