Userform opens twice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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.
 
Upvote 0
How are you closing the userform?
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Sounds to me as though this is triggering the Activate event again.
Code:
Range("A4").Select
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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