question about Msgbox vbOKCancel

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
Hi guys,


I've created a routine that starts with a message box saying the following:

"Please Confirm 'DTmacTest.xls' is the ONLY open Excel workbook"

So I have an "OK" button and a "Cancel" button.

but no matter which one I press, it runs the routine.

How do I assign the cancel button to stop the routine when it's pressed?

thanks,

Paul.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Paul

Are you actually returning a value from the message box, or just showing it?

Can you post the actual code?
 

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
if you set a variable equal to the msg box
aka resp = msgbox("Mesg",4)
if resp is equal to 7, they hit cancel, if its 6 they hit ok

This is stolen off the excel help

<code>
Displays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked.

Syntax

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The MsgBox function syntax has these named arguments:

Part Description
prompt Required. String expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return – linefeed character combination (Chr(13) & Chr(10)) between each line.
buttons Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If omitted, the default value for buttons is 0.
title Optional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
helpfile Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
context Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.



Settings

The buttons argument settings are:

Constant Value Description
vbOKOnly 0 Display OK button only.
vbOKCancel 1 Display OK and Cancel buttons.
vbAbortRetryIgnore 2 Display Abort, Retry, and Ignore buttons.
vbYesNoCancel 3 Display Yes, No, and Cancel buttons.
vbYesNo 4 Display Yes and No buttons.
vbRetryCancel 5 Display Retry and Cancel buttons.
vbCritical 16 Display Critical Message icon.
vbQuestion 32 Display Warning Query icon.
vbExclamation 48 Display Warning Message icon.
vbInformation 64 Display Information Message icon.
vbDefaultButton1 0 First button is default.
vbDefaultButton2 256 Second button is default.
vbDefaultButton3 512 Third button is default.
vbDefaultButton4 768 Fourth button is default.
vbApplicationModal 0 Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal 4096 System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton 16384 Adds Help button to the message box
VbMsgBoxSetForeground 65536 Specifies the message box window as the foreground window
vbMsgBoxRight 524288 Text is right aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Hebrew and Arabic systems



The first group of values (0–5) describes the number and type of buttons displayed in the dialog box; the second group (16, 32, 48, 64) describes the icon style; the third group (0, 256, 512) determines which button is the default; and the fourth group (0, 4096) determines the modality of the message box. When adding numbers to create a final value for the buttons argument, use only one number from each group.

Note These constants are specified by Visual Basic for Applications. As a result, the names can be used anywhere in your code in place of the actual values.

Return Values

Constant Value Description
vbOK 1 OK
vbCancel 2 Cancel
vbAbort 3 Abort
vbRetry 4 Retry
vbIgnore 5 Ignore
vbYes 6 Yes
vbNo 7 No



Remarks

When both helpfile and context are provided, the user can press F1 (Windows) or HELP (Macintosh) to view the Help topic corresponding to the context. Some host applications, for example, Microsoft Excel, also automatically add a Help button to the dialog box.

If the dialog box displays a Cancel button, pressing the ESC key has the same effect as clicking Cancel. If the dialog box contains a Help button, context-sensitive Help is provided for the dialog box. However, no value is returned until one of the other buttons is clicked.

Note To specify more than the first named argument, you must use MsgBox in an expression. To omit some positional arguments, you must include the corresponding comma

</code>
 

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
Paul

Are you actually returning a value from the message box, or just showing it?

Can you post the actual code?


I think I'm just showing it.
I'll read through what Kavy put up and get back to you if I stil lcan't get it working.
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
I've created a routine that starts with a message box saying the following:

"Please Confirm 'DTmacTest.xls' is the ONLY open Excel workbook"

Would it not make more sense to use VBA to close any workbooks that aren't named ''DTmacTest.xls'?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Or only reference that workbook in the code?:)
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,885
Office Version
  1. 365
Platform
  1. Windows
Paul

You could just use IF to test for the cancel button, and exit the routine if Cancel's been pressed:
Code:
If MsgBox("Please Confirm 'DTmacTest.xls' is the ONLY open Excel workbook", vbOKCancel) = vbCancel Then Exit Sub
Rest of your code here...........
 

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
:x

I'm only starting!! with this VBA stuff.

Gimme a break!! :LOL:

Can't close the open workbooks automatically coz people tend to have loads of unnamed/unsaved ones open at the same time.
Also I'm not that confident yet.
Perhaps the code is a little shakey, but I'm determined to figure this out.
this is just a failsafe to ensure excel doesn't copy the infromation into the wrong sheet.
I do select the workbook before I.....


hold on a sec....



****!! I had one macro running BEFORE I selected the workbook.
feckin stupid mistake!! :x :x :x

Anyhou.
I still wanna get the Msgbox working even if I don't need it.
so I'll know next time. :wink:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Paul

It's sort of hard to help with code without actually seeing it.:)
 

Forum statistics

Threads
1,181,399
Messages
5,929,743
Members
436,687
Latest member
Glass of Gin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top