VBA to change UserForm

K1600

Board Regular
Joined
Oct 20, 2017
Messages
185
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

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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:

Code:
[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...

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

Hope this helps!
 
Last edited:
Upvote 0
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.
Code:
Sub SetUserForm2()
    Sheets("Setup").Range("UserFormName").Value = "UserForm2"
  
End Sub

Put this in a standard module
Code:
'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
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top