94mustang
Board Regular
- Joined
- Dec 13, 2011
- Messages
- 133
- Office Version
- 365
- 2019
- Platform
- Windows
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.
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.