Disable the redX in a form

[Ewing25]

Okay so i have a inputbox that pops up from this code.

Dim Message2, Title2, MyValue
Message = "Please Enter Last 5 Digits of Your Card Number"
Title = "AMEX Number" '<--Change
MyValue = InputBox(Message, Title)

If MyValue = "" Then
Sheets("Expense Amex").Protect password:="welcome"
Exit Sub

End If


Range("Cardholder_Number").AutoFilter
Range("Cardholder_Number").AutoFilter Field:=1, Criteria1:=MyValue,
visibleDropDown:=False
Sheets("Expense Amex").Protect password:="welcome"
On Error Resume Next

What i want to do is make it so they cant press the red X in the top right
of the form. how do i do this?

Thanks,
Alex

 

[Coder1215]

Okay so i have a inputbox that pops up from this code.

Dim Message2, Title2, MyValue
     Message = "Please Enter Last 5 Digits of Your Card Number"
    Title = "AMEX Number"  '<--Change
    MyValue = InputBox(Message, Title)

 If MyValue = "" Then
   Sheets("Expense Amex").Protect password:="welcome"
  Exit Sub

 End If

    Range("Cardholder_Number").AutoFilter
    Range("Cardholder_Number").AutoFilter Field:=1, Criteria1:=MyValue,
visibleDropDown:=False
    Sheets("Expense Amex").Protect password:="welcome"
On Error Resume Next

What i want to do is make it so they cant press the red X in the top right
of the form. how do i do this?

Thanks,
Alex

Hi Alex,

Try to include following code in your form:

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

regards

Peter

 

[PCLIVE]

Alex,

My understanding is that you cannot disable the Close X on an InputBox.
However, using code you can disable one on a userform. I believe that is
what the other reply was trying to tell you, even though you aren't
currently using a UserForm.

Using this code in your UserForm will force a user to click a command button
to close the form. If the user clicks the X, a message will appear telling
them it is disabled.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then
Cancel = True
MsgBox "The X is disabled, please use a button on the form.",
vbCritical
End If

End Sub



Regards,
Paul

 

[Ewing25]

I added it like this and it doesnt seem to work. Any suggestions?

Dim Message2, Title2, MyValue
Dim Cancel As Integer
Dim CloseMode As Integer
If CloseMode = vbFormControlMenu Then
Cancel = True



Message = "Please Enter Last 5 Digits of Your Card Number"
Title = "AMEX Number" '<--Change
MyValue = InputBox(Message, Title)

If MyValue = "" Then
Sheets("Expense Amex").Protect password:="welcome"
Exit Sub

End If
End If

 

[Ewing25]

So to achieve what im trying to do i have to create a userform and put the
code behind that?

 

[Gary Brown]

Do While MyValue = ""
MyValue = InputBox(Message, Title)
Loop

....will keep the inputbox showing until something is entered.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Ewing25]

That works great! thanks!

Do While MyValue = ""
MyValue = InputBox(Message, Title)
Loop

...will keep the inputbox showing until something is entered.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Jim Thomlinson]

While I am not saying don't do what you are planning on doing, I have found
that users don't like mandatory fields that they can not get out of. IMO you
are better off to catch where they have exited the input without entering
anything and letting them know the consequences of leaving that field blank.
Then allow them to try again ro whatever is needed...

 

[Harald Staff]

Very true, Jim. Users will Ctrl Alt Delete their way out of the painful
experience and refuse to touch it ever after.

Best wishes Harald