MsgBox buttons argument settings - reverse engineering.

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
80
Hi

I would like to be able to use the standard MsgBox button argument settings to pass a value to a custom message box form that
I have developed.

For example :

vbYesNo : 4
vbRetryCancel : 5
vbCritical : 16

and to be able to combine them as required.

For example :

vbYesNo + vbCritical

This example passes a value of 20 which gets reversed engineered by Excel so that the MsgBox function can use the values of 4 and 16
to display the 'Yes' and 'No' buttons and the Critical icon.

Excel seems to display a level of intelligence and handles inappropriate combination of settings in way in which is obvious to the developer.

Does anybody know of a way to create an array of the individual argument values or the approach that I could take in
developing my own?

Cheers

HighAndWilder
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,772
The basic technique is to use numbers that are powers of 2 as your base numbers.

vbYesNo = 4, vbOKOnly = 0, vbOKCancel = 1, vbCritical = 16 vbInformation = 64 and your argument is BoxStyle

So you code would look like

Code:
butYes.Visible = CBool(BoxStyle And 4)
butNo.Visible = CBool(BoxStyle And 4)
butCancel.Visible = CBool(BoxStyle And 1)

If CBool(BoxStyle And 16) Then
    Me.Caption = "Critical"
ElseIf CBool(BoxStyle and 64) Then
    Me.Caption = "Information"
End If
Using the arithmetic And operator allows you to add arguments to combine their effect.

Consider that arrays of boolean values
{True, False, True, True, False, False, False}
Are the same as strings TFTTFFF
Are the same as other strings 1011000
Are the same as binary representations of numbers
are the same as numbers 88

NOTE binary numerals are read right to left, but arrays are read left to right, this can cause confusion
I prefer the system where Array(True, False, True, True, False, False, False) is equivalent to 0001101 (reversing the order) is equivalent to 13 (8+4+1). But you gotta keep your head straight whatever system you prefer.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
Assuming your users will specify the sum of the same arguments as are available for a normal MessageBox, here are some functions that you may find useful. Except for the last one, they all return a text string indicating which option has been specified... change them to text strings that you like better or change them to some coded number system of your own choosing (remember to change the function type declaration from String to the appropriate number type if you do do this).
Code:
Function WhichButton(ByVal MsgBoxArg As Long) As String
  Select Case CLng(MsgBoxArg) Mod 16
    Case 0: WhichButton = "OK"
    Case 1: WhichButton = "OK, Cancel"
    Case 2: WhichButton = "Abort, Retry, Ignore"
    Case 3: WhichButton = "Yes, No, Cancel"
    Case 4: WhichButton = "Yes, No"
    Case 5: WhichButton = "Retry, Cancel"
  End Select
End Function

Function WhichIcon(ByVal MsgBoxArg As Long) As String
  Select Case (CLng(MsgBoxArg) Mod 128) - (CLng(MsgBoxArg) Mod 16)
    Case 16: WhichIcon = "Critical"
    Case 32: WhichIcon = "Question mark"
    Case 48: WhichIcon = "Exclamation"
    Case 64: WhichIcon = "Information"
  End Select
End Function

Function WhichDefault(ByVal MsgBoxArg As Long) As String
  Select Case (CLng(MsgBoxArg) Mod 1024) - (CLng(MsgBoxArg) Mod 128)
    Case 0:   WhichDefault = "First button"
    Case 256: WhichDefault = "Second button"
    Case 512: WhichDefault = "Third button"
    Case 768: WhichDefault = "Fourth button"
  End Select
End Function

Function WhichModal(ByVal MsgBoxArg As Long) As String
  If CLng(MsgBoxArg) Mod 8192 > 4096 Then
    WhichModal = "System"
  Else
    WhichModal = "Application"
  End If
End Function

Function HasHelpButton(ByVal MsgBoxArg As Long) As Boolean
  HasHelpButton = CLng(MsgBoxArg) And 16384
End Function
 

Forum statistics

Threads
1,081,418
Messages
5,358,567
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top