UserForm2 unloads but remains displayed until UserForm1 also unloads

clayb04

New Member
Joined
Aug 9, 2010
Messages
6
Hi.. I am creating a VBAProject that simply updates 3 cells in a worksheet based on previous conditions being set or not set. Everything works as it should bar one thing.
<o:p> </o:p>
If the condition in ‘STEP 1’ is TRUE the Unload Me does not close UserForm2 straight away. UserForm1 displays over the top of UserForm2 until the CommandButton labeled ‘Exit’ on UserForm1 is clicked and then both UserForm’s close.
<o:p> </o:p>
However, if the condition for ‘STEP 1’ is FALSE then ‘STEP 2’ and ‘STEP 3’ run and exactly the same Unload Me and UserForm1.Show works perfectly – UserForm2 closes immediately and only UserForm1 displays.
<o:p> </o:p>
I have played with having only one set of Unload Me UserForm1.Show at the very end of the Sub and also I’ve also played with Application.ScreenUpdating = True / False in all possible combinations and the problem persists.
<o:p> </o:p>
Any help will be much appreciated and thanks in advance.



Code:
Private Sub CellValueUpdate_Click()

'Note 1: Values in Sheet1 Cells A1, A2, A3 default to 0 and are changed
        'to 1 by other Macros if updates are to be made

'Note 2: UserForm2 (This UserForm) is displayed using a CommandButton on
        'UserForm1 via Unload Me and UserForm2.Show
        
'Note 3: UserForm1 also has a second CommandButton labeld Exit which is
        'coded Unload Me

'----------------------------------------------------------------------'
' STEP 1

    'Check Condition #1 set by Macro1 and exit UserForm2 if value is set to 0
    If Sheets("Sheet1").Range("A1").Value = 0 Then

        'Unload UserFrom2
        Unload Me

        'Display UserForm1 if no updates to apply
        UserForm1.Show

    Else
'----------------------------------------------------------------------'
        'STEP 2

        'Check Condition #2 set by Macro2 - reset and display UserForm3
           'if value is not set to 0
        If Sheets("Sheet1").Range("A2").Value <> 0 Then

            'Reset Condition #2 to zero
            Sheets("Sheet1").Unprotect Password:="ABC"
            Sheets("Sheet1").Range("A2").Value = 0
            Sheets("Sheet1").Protect Password:="ABC"

            'Display UserForm3 if conditional exit (Updates to be applied) - 
                'return via CommandButton1 in UserFrom3 using Unload Me
            UserForm3.Show
'----------------------------------------------------------------------'
            'STEP 3

            'Check Condition #3 set by UserForm3 - and proceed if value is
                'set to 1
            If Sheets("Sheet1").Range("A3").Value = 1 Then

                'Reset Condition #3 to zero and Unprotect Sheet1
                Sheets("Sheet1").Unprotect Password:="ABC"
                Sheets("Sheet1").Range("A3").Value = 0

                'Turn Off screen updating
                Application.ScreenUpdating = False

                ' Update Sheet1 Cells B1, B2, B3 and Protect Sheet1
                Sheets("Sheet1").Range("B1").Value = _
                                        Sheets("Sheet1").Range("C1").Value
                Sheets("Sheet1").Range("B2").Value = _ 
                                        Sheets("Sheet1").Range("C1").Value
                Sheets("Sheet1").Range("B3").Value = _ 
                                        Sheets("Sheet1").Range("C3").Value
                
                'Protect Sheet1
                Sheets("Sheet1").Protect Password:="dev01"

                'Avtivate Sheet2 and select Cell A1
                Sheets("Sheet2").Activate
                Sheets("Sheet2").Range("A1").Select
            
                'Turn On screen updating
                Application.ScreenUpdating = True
            
                'Unload UserForm2 after Updates Applied
                Unload Me
            
                'Display UserForm1 following updates being applied
                UserForm1.Show

            End If

        End If
    
    End If

End Sub
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,731
Messages
5,513,069
Members
408,936
Latest member
savageanman

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top