Userform opens twice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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.

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

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Try putting a breakpoint here,
Code:
 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

Well-known Member
Joined
Oct 5, 2015
Messages
730
Office Version
2007
Platform
Windows
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.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
How are you closing the userform?
Using this.

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
Try putting a breakpoint here,
Code:
 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


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

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
730
Office Version
2007
Platform
Windows
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.
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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.

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

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Sounds to me as though this is triggering the Activate event again.
Code:
Range("A4").Select
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
Sounds to me as though this is triggering the Activate event again.
Code:
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)

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

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top