UserForms VBA Unloading or Closing the Dialog Box (Clicking OK Button)

[94mustang]

I am needing help with some VBA code. I am getting the "Run-time error '400': Form already displayed; can't show modally" when I click the OK button on my dialog box. My code is to enter two values and move on but I continue to run into this error when I use Unload Me or even Hide. What am I doing wrong?

This code is my OK Button for the UserForm.
Private Sub OKButton_Click()
Range("B3").Value = Val(txtpval.Text)
Range("B4").Value = Val(txtnval.Text)
If Trim(Me.txtpval.Value) = "" Then
Me.txtpval.SetFocus
MsgBox "Please enter a value for both p and n"
End If
If Trim(Me.txtnval.Value) = "" Then
Me.txtnval.SetFocus
MsgBox "Please enter a value for both p and n"
End If
End Sub

This code prevents the user from clicking the "X" in the top left corner to close the dialog box.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please click the OK Button after entering values!"
End If
End Sub

This is the main code that I am running.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngempty As Long
Dim txtinrange As Long
Dim pnvalue As Integer
txtinrange = WorksheetFunction.CountIf(Range("PastedData"), "*")
rngempty = WorksheetFunction.CountA(Range("PastedData"))
pnvalue = WorksheetFunction.CountA(Range("pnData"))
'Turn Screen Updating Off
Application.ScreenUpdating = False
'If the range is empty or contains text then exit the subroutine.
If rngempty = 0 Then Exit Sub
If txtinrange > 0 Then Exit Sub
'Format the "PastedData" table with borders, font size and font type
'if the user happens to not paste the values.
Range("PastedData").Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("PastedData").Interior.Color = RGB(220, 230, 241)
With Selection.Font
.Name = "Arial"
.Size = 12
End With

If pnvalue < 2 Then
pnval.Show
End If

The rest of my code continues below the End If statement.

 

[94mustang]

Yes, the stack is filling up. How would I check or debug the when/how I am am disabling events to pevent the filling up and how can I check the stack since my code is no longer running? When I was viewing the stack overflow dialog, I did not try to delete what was in the stack because I figured that it was a read only dialog box. I would like to clear the stack if at all possible.

 

[Jon Peltier]

It's hard to do this real time. Step back, and step through the code, and see how often the event procedure is called. Keep track of the state of EnableEvents, and see where it could be used more effectively. Your code doesn't seem so intricate that it should cause such a problem.

 

[94mustang]

Mr. Peltier,

Let me make sure I understand what you mean. Do you mean when the event procedure does something to a cell or range such as formatting a cell or range? Do you also mean when the event enters data into cells? Do you mean when the event copies cell data as well as ranges from a sheet and pastes it to another sheet? Are those the things we are talking about?
How can I attach a picture to a post that is on my computer's hard drive. I would like to send you a snapshot pic of my first worksheet as well as a pdf of a flow chart of what my code is doing. This may help you help me more efficiently as to what is going on. I see that I can use a URL but I believe I tried this before to take a file from my computer and it did not work. Let me know if I am on the right track with the questions above and if you are able to help me get better info. to you, that would be "icing on the cake". Thanks again.

 

[Jon Peltier]

What we don't want is the event procedure doing something that calls itself, so we disable events before any such thing is done. The Worksheet_Change procedure is called by changing the contents of a cell, but not by selecting a cell, calculating a cell, or formatting a cell.

You can't just clear the stack in process and expect the code to keep operating. Each item in the stack is another time the code called itself, so you have to execute to the end of the latest time the procedure is running, the the earlier time, and earlier and earlier, etc.

You can only link to a picture you've uploaded elsewhere.

 

[94mustang]

Mr. Peltier,

Well, I went back and placed all my code in a standard module. In my code, I am copying/pasting ranges into other worksheets. I am also making decisions in the code to re-create formulas should they get deleted by accident. It is working wonderfully. I will have to study more about the ApplicationEnable Events statement to truly understand how I can use it in other code structures. I have taken the information you have given to me in this post and added it into my code. Thank you very much for your patience and helping me make my code better. For now, we can consider this dialog to be closed. Your input was extremely valuable. I consider this forum to be one of the best if not the best one on the internet. It has provided more help to me in the creation of my very first macro. I did get the Power Programming book by J. Walkenbach. From just skimming some of the pages, I can see this book is going to be a tremendous help in writing more effective code. Again, thanks so much and appreciate all your help.