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.

 

[JLGWhiz]

I think that what is happening is this scenario:
1. You have the UserForm show statement in a Worksheet_Change event code.
2. Your OKButton_Click code initiates a change to at least one cell on the sheet.
3. The change triggers the change event code to run thus attempting to again Show the UserForm.
4. Ergo the error message.
Try this modified code for your OKButton

Private Sub OKButton_Click()
Application.EnableEvents = False
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
Application.EnableEvents = True
End Sub

This will turn off the event trigger while the OKButton code runs, and turn it back on at the end of that macro. If it should ever error in between the two statements, and leave the event trigger disabled just run this little snippet:

Sub reinstateEvents()
Application.EnableEvents = True
End Sub

 

[94mustang]

Thank you so very much. It works. I did have to include the Unload Me at the end of the code. Now, I am having issues with the dialoatg box reappearing even when I manually go to the cells and enter the values. Basically, this is what I want it to do: The user opens the workbook and has a range to paste data (values) into the worksheet. The pasting operation by the user triggers the macro to run but if the two values were forgotten to be entered, then I want the UserForms Macro to run so the user can enter those values.

What's happening is when I physically go enter the values into the two fields, it runs the UserForms Macro and I do not want it to do that. Also, I want the user to enter values only and not a letter (need code for this). Also, if the user clicks the OK button when either the p or n value is not entered or both, I want the dialog box to stay until the user enters the values. This is my very first experience with UserForms. Any help here will definitely be much appreciated. I have used code from the internet to "piece mill" what I have in the UserForm macro.

What you have already done is AWESOME and is definitely appreciated.

 

[JLGWhiz]

Thank you so very much. It works. I did have to include the Unload Me at the end of the code. Now, I am having issues with the dialoatg box reappearing even when I manually go to the cells and enter the values. Basically, this is what I want it to do: The user opens the workbook and has a range to paste data (values) into the worksheet. The pasting operation by the user triggers the macro to run but if the two values were forgotten to be entered, then I want the UserForms Macro to run so the user can enter those values.

What's happening is when I physically go enter the values into the two fields, it runs the UserForms Macro and I do not want it to do that. Also, I want the user to enter values only and not a letter (need code for this). Also, if the user clicks the OK button when either the p or n value is not entered or both, I want the dialog box to stay until the user enters the values. This is my very first experience with UserForms. Any help here will definitely be much appreciated. I have used code from the internet to "piece mill" what I have in the UserForm macro.

What you have already done is AWESOME and is definitely appreciated.

You might need to re-think how you have organized your code sequence. Bear in mind that the Worksheet_change event code is triggered by ANY change to the worksheet, with the exception of changes by calculation. So, the code will be triggered whether by pasting the value from the userform controls or by manual entry. What happens within the code is controlled by If...Then statements, which set conditions for parts of the code to execute. It is good practice to draw a block diagram of each step in your process to identify the decision points which must be controlled by the If...Then statements. Then you can construct the code to satisfy those conditions. I am sure there is more to what you are doing than described in the post, but here is an approach to resolving the issue.
1. When User opens the workbook, have a Workbook_Open event code to display a message box asking if there are entries to be made to the sheet. If there are, then display the UserForm

Private Sub Workbook_Open()
act = MsgBox("Do you want to make changes to the worksheet?", vbYesNo, "CHANGE OPTION") 'put worksheet name in here
 If act = vbYes Then
  pnVal.Show
 End If

2. Then in the UserForm OKButton Code you would have the quality check for the entries to the two cells.
3. The UserForm.Show statement would be removed from the Worksheet_change event since it is now controlled by the Workbook_Open Event and will show only upon opening the workbook.
These are just some alternatives to your present construct, to give you some ideas on how to get around these problems.

 

[Jon Peltier]

Not to be pedantic, but this code will annoy your users:

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

The user wants out, and you know how to get out, so do it this way:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    OKButton_Click
  End If
End Sub

 

[94mustang]

Based on your comments, I did go back and re-think how this structure needed to flow. You have been very helpful with the structure and advice. Thanks again.

 

[94mustang]

Mr. Peltier,

When the user opens the workbook, the user has an opportunity to enter the values into the appropriate cells as they are highlighted for the user not to forget to enter these values before moving on. If the user fails to enter these values, then the UserForm is displayed because these values are extremely critical for the entire worksheet to anayze a Regression model. What else can I do if this is the user's second chance?

 

[Jon Peltier]

Sorry, I didn't really get the purpose behind your userform. Maybe I understand it now a bit more, after rereading your posts. Here are my suggestions.

Don't display the form modally if you have textboxes on the form for data entry. It's tricky enough to program around, and confusing enough for the user, when the user can access the worksheet behind the dialog, but in the new screwed up SDI interface in Excel 2013, it's a real PITA to keep a modeless dialog on top of the active window.

Disable the OK button until both textboxes are populated. This means you need to build some validation into the textboxes, so a filled textbox keeps the normal background color and an empty one (or a nonnumeric one) is shaded light red, to indicate a missing or problematic entry. Don't show any message when the user clicks the red X, but color the background of any empty textbox light red when they click the red X. The following is somewhat "pseudocode" and I haven't really checked it as carefully as I would normally, because it's late, but it should help:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  Dim bOkay As Boolean
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    bOkay = True

    If IsNumeric(txtpval.Text) Then  '' assume you need numbers; change validation if needed
        txtpval.backgroundcolor = vbwindowbackground '' check this syntax
    Else
        txtpval.backgroundcolor = rgb(255,125,125) '' check this syntax, adjust the color
        bOkay = False
    End If

    If IsNumeric(txtnval.Text) Then
        txtnval.backgroundcolor = vbwindowbackground '' check this syntax
    Else
        txtnval.backgroundcolor = rgb(255,125,125) '' check this syntax, adjust the color
        bOkay = False
    End If

    ' enable the OK button
    OKButton_Click.Enabled = bOkay
    ' or save the user a click:
    'OKButton_Click

  End If
End Sub

Private Sub txtpval_Change()
    If IsNumeric(txtpval.Text) Then  '' this assumes you need numbers; change validation if needed
        txtpval.backgroundcolor = vbwindowbackground '' check this syntax
        If IsNumeric(txtnval.Text) Then  '' both are okay
            OKButton.Enabled = True
        Else
            OKButton.Enabled = False
        End If
    Else
        txtpval.backgroundcolor = rgb(255,125,125) '' check this syntax, adjust the color
        OKButton.Enabled = False
    End If   
End Sub

Private Sub txtnval_Change()
    If IsNumeric(txtnval.Text) Then  '' assuming you need numbers; change validation if needed
        txtnval.backgroundcolor = vbwindowbackground '' check this syntax
        If IsNumeric(txtpval.Text) Then  '' both are okay
            OKButton.Enabled = True
        Else
            OKButton.Enabled = False
        End If
    Else
        txtnval.backgroundcolor = rgb(255,125,125) '' check this syntax, adjust the color
        OKButton.Enabled = False
    End If   
End Sub

Private Sub UserForm_Initialize()
    OKButton.Enabled = False
End Sub

 

[94mustang]

Mr. Peltier,

I really like what you have added here and I will incorporate it into my macro. Thank you for this new code. Is it possible to write a structure of code that would pre-populate the the user form with data entered from a spreadsheet. As an example, when you are filling out a form on the internet and you have missed a few fields that are required and then click the submit button, the form is not submitted but rather shows you by different color (say red) what was missed and the user does not have to populate all the data that he/she is already filled in. Only the data that is not completed is necessary to be completed. How can a UserForm bring in data from a cell and pre-populate it without the user having to re-type a value that he/she has already entered.

Also, I am getting a Call Stack Over flow error. When I press Ctrl+L, the Call Stack Overflow Dialog box appears. In this box, I have about 90 occurrences of the following: VBAProject.Sheet1.Worksheet_Change [<non-basic code="">]. How do I prevent the Call Stack from filling up? If I am filling it up, there must be a way of clearing it and managing it. Need help here. My code has stopped running until I find a fix for this.

Finally, this is my very first macro to ever write. What I have written is from what I learned reading and what I could find on the internet. I found an ebook called "Excel VBA Programming for Dummies". Is there a more comprehensive book on writing VBA? Is there any courses on-line or classroom taught material on VBA?</non-basic>

 

[Jon Peltier]

The same way that you put values from the textboxes into the sheet at the end using

Range("B4").Value = Val(txtnval.Text)

you can do the reverse at the beginning (in the UserForm_Initialize code that I introduced) using

Val(txtnval.Text) = Range("B4").Value

or if you want to make sure the value is formatted appropriately,

Val(txtnval.Text) = Format(Range("B4").Value, sFormatString)

where sFormatString may be something like

$#,##0.00
0.00
etc.

If any of the cells are blank, instead of trying to apply a value, you could apply the background shading as I did above when a value was no provided.

Once you get a few of these ideas into your head, you'll be amazed at the flexibility of this approach, and at how easy they are for your users to work with.