i need to disable the X button 2

[Mikeb1970]

Hi all,

When my spreadsheet opens, i have a userform showing, (workbook open), then all user has todo is enter a date and hour, and press a start button, then code will start and make calculations, visualize things. This works very good , until someone decides to click the close X button on top of userform.

I already added a second userform, warning, asking poeple not to click to X button, but it is like telling a child not to play with fire and place matches on the table.

how can i disable the X button, and when possible show a userform when they click on it? the showing of userform is optional.

I spend most of my time writing code to catch errors made by users, maybe i need a different approach all together.

With regards

Mike

 

[SBendBuckeye]

In the VBE, right click on the UserForm and select show code. Then Click on the General and select UserForm from the list. Then on the right side select the QueryClose event. This exposes a Cancel parameter, which if set to true stops the form from closing.

There may be a more elegant way, but this will hopefully get you started. Since I don't know your technical level, please accept my apologies if I gave too much detail. Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[DrBowes]

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

Look at the Object Browser for the other CloseModes.

 

[Bowers74]

Using the UserForm_QueryClose is the right way to go, so use DrBowes solution.

Just to add on this though:

You stated that you display a UserForm (!) to inform the user that they should not use the "x" button. This is not good practice and I am wondering if you have heard about the Message Box (MsgBox) functionality of VBA?

The Message Box (MsgBox) is the typical way that programmers inform their users of things that they need to know so you do not have to create an entirely new UserForm to display a message to the user.

In DrBowes solution, if you want to inform the user that they should not use the "x" button after clicking it, the you just need to insert a Message Box (MsgBox line of code. For example:
[tt]
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "This button has no use!",vbInformation,"No Close"
End If
End Sub
[/tt]
To find out more information on the Message Box (MsgBox) go to the Help File in the VBE Window.


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Bowers74]

Something else to consider would be the InputBox functionality in VBA.

Instead of creating a UserForm to get two bits of information (Date and Hour), you could use two seperate InputBoxes and their values to start your calculations.

For Example:
[tt]
Sub GetDateAndTime()
[A1] = InputBox("Please Enter the Date:", "Enter Date", Date)
[B1] = InputBox("Please Enter the Time:", "Enter Time", Time)
End Sub
[/tt]
This bit of code will create two seperate InputBoxes requesting that the user enters the Date and Time. The current date and current time are used as the default values and the values can be edited. The InputBoxes in this example return the date to cell A1 of the active sheet and the time to cell B1 of the active sheet.

To understand more about the InputBoxes, check out the Help File in the VBE.


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Mikeb1970]

Hi,

Thnx to all three of you for the help.

Just wanted to lett Bowers know why i opted for the userform instead off a msgbox.

First off all the logo of our company is incorporated in that userform, and more important, the version number and date of the apllication. This has been added to get users attention and help in debugging.

The inputboxes are a good idea, will be considered in my next application

once again, thank you

With regards

Mike

 

[SBendBuckeye]

FYI,

'From VBA.vbQueryClose enumeration
'Private Const vbAppTaskManager = 3
'Private Const vbAppWindows = 2
'Private Const vbFormCode = 1
'Private Const vbFormControlMenu = 0
'Private Const vbFormMDIForm = 4

Have a great day!

j2consulting@yahoo.com