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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,918
Members
430,244
Latest member
Ireland1

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
Top