Userform opens twice

[ipbr21054]

Morning,
Please could you check my code & advise a fix.

I open my workbook.
It defaults to INCOME (1) to open first.
I see the msgbox asking me to select MONTH & YEAR
I choose to select CLOSE FORM
The form quickly coses then reopens.
I choose to select CLOSE FORM again & it closes.
Now i can do this a few times & this doesnt happen again.
Should i close workbook & reopen the cycle seems to start again

The codes in use is supplied below.

Private Sub Worksheet_Activate()
If Range("B1") = "" Then
Range("A4").Select
MYFINCOMEONE.Show
Else
End If
End Sub

Private Sub TransferButton_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 2
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT BOTH OPTIONS", 48, "MONTH & YEAR TRANSFER MESSAGE"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2)
    
    With ThisWorkbook.Worksheets("INCOME (1)")
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(1, i + 2) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(1, i + 2) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i


    End With
    
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    
    MsgBox "Month & Year Have Been Updated", vbInformation, "SUCCESSFUL MESSAGE INCOME 1"
    Unload MYFINCOMEONE
End Sub

 

[Norie]

Try putting a breakpoint here,

 Private Sub Worksheet_Activate()

then active the relevant sheet, and when the code goes into break mode you can step through line by line with F8 and find out why the form re-opens.

 

[jmacleary]

Norie's suggestion is a good one - I think maybe what may be happening is that when you update the values, or close the form, control reverts to the worksheet, which causes the activate code to run again.

 

[Norie]

How are you closing the userform?

 

[ipbr21054]

How are you closing the userform?

Using this.

Private Sub CloseFormButton_Click()
Unload MYFINCOMEONE
Range("A4").Select
End Sub

 

[ipbr21054]

Try putting a breakpoint here,

 Private Sub Worksheet_Activate()

then active the relevant sheet, and when the code goes into break mode you can step through line by line with F8 and find out why the form re-opens.

Ive never been any good doing this.
So with the code shown below.
I click to the left of Private Sub Worksheet Activate.
Now i see a Red circle & Private Sub Worksheet Activate text is also now Red.

From here on im lost

Private Sub Worksheet_Activate()
If Range("B1") = "" Then
Range("A4").Select
MYFINCOMEONE.Show
Else
End If
End Sub

 

[jmacleary]

OK thats good. Now just click into the worksheet - your activate code should start to run and the Private Sub Worksheet_Activate line will turn yellow. This stops the code executing at that point (called the breakpoint). By pressing F8, you can tell the VBA to execute the next instruction, which will turn yellow (that should be the 'If Range("B1") = "" Then' line). Continue to slowly press the F8 key and see what steps get executed. This should help you work out when the code is activated again.

 

[ipbr21054]

OK
So the code mentioned above i see the red dot & now red text.
I click the worksheet then go back to code page.
The red text is now yellow so i press F8
Next line is now yellow
Same again the next line is now yellow.
Then next line is now yellow
Now when i press F8 again i see the userform open.
So i select to close it.
Then i am taken to a different code where the first line Private sub CloseFormButton_Click is now yellow.

Private Sub CloseFormButton_Click()
Unload MYFINCOMEONE
Range("A4").Select
End Sub

I then F8 again and it then works its way down each line turning it yellow.
After End Sub i am taken back to the first piece of code.

Private Sub Worksheet_Activate()
If Range("B1") = "" Then
Range("A4").Select
MYFINCOMEONE.Show
Else
End If
End Sub

Where End If is now yellow
I then F8 again then after the End Sub the same cycle happens.

 

[Norie]

Sounds to me as though this is triggering the Activate event again.

Range("A4").Select

 

[ipbr21054]

Sounds to me as though this is triggering the Activate event again.

Range("A4").Select

As a test i deleted that line of code.
Saved, closed & opened.
Worked fine.

Did this a few times but then it happened again.

Having said that i use the code below on other worksheets in the same workbook & never seen it happen.
I only see it on worksheet INCOME (1)

Private Sub Worksheet_Activate()
If Range("B1") = "" Then
Range("A5").Select
MYFEXPENSESTWO.Show
Else
End If
End Sub