ktMsgBoxAddin

[ What is ktMsgBox ? ]

'ktMsgBox' far exceeds functionality and usability of MsgBox !

MsgBox is the most frequently used functions of VBA, but it has many failings :

  • You cannot control the position of the message box.
  • You cannot control the color of the message box.
  • You cannot add any additional properties to a message box.

Are you satisfied with that ? We're not !

We created ktMsgBox . With ktMsgBox, it's a piece of cake to :

  • Control the position of your message box.
  • Change the fonts line-by-line in your message box.
  • Change the font and backgrounds colors in your message box.
  • Apply any sound file (*.wav) to the message box.

We've provided an interface that is easier to use than writing the code
for a MsgBox and the code that is created can be modified directly .

I translate this document from Japanese with the aid of the translation software.
Therefore there may be much poor expression.

[ AddinBox Home (Japanese) ] [ English Top ]

K.Tsunoda(AddinBox) Mail:addinbox@h4.dion.ne.jp CopyRight(C) 2001 Allrights Reserved.


[ Special Features ]

Ver 3.40 is supported OS / Excel ( x64 ).
1. The basic Syntax of ktMsgBox inherits an interface of MsgBox of the VBA function.
The only difference is the function name of "MsgBox" and "ktMsgBox".
You only do this small change and can use ktMsgBox.
2. This Add-In have included two Versions.
The 1st one is called "ktMsgBox" and it has same interface as MsgBox,
and the 2nd one is called "ktMsgBoxEX" which is an Extended Version.
3. | Just as in the case of MsgBox, when you change the setting of "Screen Properties" in the Control Panel, this will also define the fixed Font which will be used by ktMsgBox.
4. The following settings are impossible with the MsgBox, but ktMsgBox allows to set them through the corresponding arguments:
a ) | Display Position. Using ktCell2Position (added Ver3.30), you can display ktMsgBox/ktMsgBoxEX at the position of the designated Cell.
b ) | Font color and Background color.
c ) | Font family ( Arial, Tahoma etc ... ).
d ) | Font Size.
e ) | Bold style. (these features are only enabled in ktMsgBoxEX)
f ) | Font and Font Color set by line. (these features are only enabled in ktMsgBoxEX)
( Japanese Version ) ( English Version )
5. | This Add-In has a built-in Chime Function, which you may use it to get the User's attention.
6. You may change the Icon to display and the Wave sound to play to your own.
a ) | This makes it possible to display your Company's Logo (or whatever you wish).
b ) | You could also save a Voice Wave File saying something as: "I found an Error!" or "All the Processing has been done!" so you may have a Message Box with your own voice!
c ) | There are 17 built-in Icons which may be specified for use. (the 4 Standard Icons of MsgBox plus 13 additional icons of ktMsgBoxAddin)
7. The Button's Caption may be set to anything through the User Definition Button feature.
The User's Default Button may be set to be any Button from the 1st to the 4th.
And the Button Caption may also be defined as something such as "Yes_To_All".
8. A TextBox for may be specified to allow entering text and, if this feature is used, ktMsgBox becomes an alternative for the InputBox function, which, additionally, allows you to use the following features which do not exist in the InputBox Function:
a ) | The Width of the Text Box may be specified.
b ) | In addition to Characters entry, you may also specify attributes for a Numerical or Date value.
c ) | It is also possible to use Password feature which replaces all typed character by "*", hiding them.
9. A Background Image may be specified.
In this case, the window size may be specified to fit so the image will be displayed complete.
(Usually, the window size is made to fit the length of the text automatically.)
10. | It is possible to have ktMsgBox close automatically after displaying for a few seconds.
11. You may also display HyperLink Messages both for URL Addresses such as (http:// ... ) as
for Email Addresses (mailto: ... ).
12. There are many Arguments in ktMsgBox, but most may be ommitted, filling in only those Arguments which you decide to use.
Furthermore, if you use our "ktMsgBox Wizard", the Macro Code for the Message Box will be interactively completed for you.


[ System requirements ]

--- Development environment ---
Windows10 , Excel 2003SP3 / 2007SP3 / 2010SP2 / 2016

--- Operating environment ---
OS : (32bit) Windows98SE and later , (64bit is supported)
Excel : (32bit) Excel2000 and later , (64bit is supported)

[ How To Use ]

The Help Document (ktMsgBox_Help.chm) has detailed explanation.
Please read that one.

First of all, you must perform the Add-In Registration in Excel in order to use ktMsgBox add-in.
And, you set reference to ktMsgBoxAddin.xla in a Workbook using "ktMsgBox" or "ktMsgBoxEX".
(ktMsgBox add-in is available in the Reference Setting only without the Add-In Registration.)

  1. Extract the distribution file on your PC.
    1. ktMsgBox_ReadMe.txt
    2. ktMsgBoxAddin.xla ( Excel Add-In )
    3. ktMsgBox_Help.chm (This Help File )
    4. ktMsgBoxDemo.xls ( Self-contained Workbook for demonstration )
    5. AddinBox.gif (The image for the opening message with ktMsgBoxDemo.xls )

    ( Note )
    The file that you downloaded from the network may be blocked by security.
    The each files that you extracted may be blocked by security, too.
    If so, please use the [Unblock] Button in the Properties Window to make it usable.

  2. Save [ ktMsgBoxAddin.xla ] to your Add-in Directory .
    You may save the other files in any directory you wish.
    Please save AddinBox.gif and ktMsgBox_Help.chm in the folder same as ktMsgBoxDemo.xls.

  3. -- Available the Add-In --
    Open New Workbook, and Load Add-In Applications.

    -- Excel2000 to Excel2003 --
    1. On the [ Tools ] menu, click [ Add-Ins ].
    2. In the Add-Ins available box, select the check box next to [ ktMsgBoxAddin ], and then click [OK].
    -- Excel2007 and later --
    1. Click [ Office Button / File Tab ] , and then click [ Excel Options ].
    2. Click [ Add-Ins ] section.
    3. In the Manage Box, select [Excel Add-ins] and click [Go].
    4. In the Add-Ins available box, select the check box next to [ktMsgBoxAddin] and click [OK].

  4. -- Set reference to ktMsgBoxAddin.xla --
    1. Open the Workbook in which you want to use these Functions.
    2. In the Visual Basic Editor, Select [Tools / References...] on the Main Toolbar.
    3. If the "ktMsgBoxAddin" appears in the Available References List, select its check box,
      if not, use the [Browse] Button to find "ktMsgBoxAddin.xla" and include it.
      (make sure the Type is "Microsoft Excel Files (*.xlsm, *.xlam, *.xls, *.xla)" so it will appear)

ktMsgBox and ktMsgBoxEX will be available in VBA as soon as you finish the above-mentioned steps.

( Note )
"Source Code Generation Wizard" is included in the ktMsgBoxDemo.xls.
You may easily create the needed Code using the Wizard without learning all the syntax and rules.


[ Add-in directory ]

There are two Directories for saving Add-Ins.
ktMsgBoxAddin will appear in the Add-Ins Registration Dialog List once you store it in either Directory.

ktMsgBoxAddin does not include a Worksheet Function. It is only VBA Function.
Therefore, you may save ktMsgBoxAddin.xla in either the Office Add-In Directory or the User's Add-In Directory.

  1. Office Application Add-Ins directory ("Library" directory )
    1. This is the Library Directory within the Office Directory.
      It is the same Directory in which the "Analysis Tool Add-In" resides.

      You can get the Path of your PC by follows.
      -- VBA Macro --
      MsgBox Application.LibraryPath
      -- Immediate Window of VBE --
      Type the following Instruction in the Immediate Window of
      the Visual Basic Editor and hitting Return:
      ?Application.LibraryPath

    2. In Windows Vista and later, it is a Directory handled by the UAC (User Account Control),
      therefore it will be necessary to turn off UAC in Admin User before saving the Add-In.

      [ What is User Account Control? ]
      http://windows.microsoft.com/en-us/windows7/What-is-User-Account-Control

    3. The Directory is the same in every Version of Windows.
      However, Directories differ according to the Excel Version.

      --- for Excel 2003 ---
      C:\Program Files\Microsoft Office\Office11\Library

      --- for Excel 2007 ---
      C:\Program Files\Microsoft Office\Office12\Library

      --- for Excel 2010 ---
      C:\Program Files\Microsoft Office\Office14\Library

      --- for Excel 2013 / 2016 ---
      In Excel2013, the path varies according to an installation method.
      [ by Windows installer ]
      C:\Program Files\Microsoft Office\Office15\Library
      [ Pre-Install or Click-To-Run ]
      C:\Program Files\Microsoft Office 15\Root\Office15\Library

      (Note) In Excel 2016, the part of "Office15" changes to "Office16".

      --- In the case of Windows (64bit) ---
      Excel (32 bit) --> C:\Program Files (x86)\.....
      Excel (64 bit) --> C:\Program Files\.....

    4. It is necessary to save it in this directory in the add-in providing "Worksheet Function" to use in a cell.
      (ktMsgBoxAddin does not include a Worksheet Function. It is only VBA Function.)

  2. User's Add-ins directory ( "Addins" directory )
    1. This is the Microsoft Add-Ins Directory which is selected as default
      when you save a File as an Add-In (*.xla , *.xlam) in the "Save As" dialog box.

      You can get the Path of your PC by follows.
      -- VBA Macro --
      MsgBox Application.UserLibraryPath
      -- Immediate Window of VBE --
      Type the following Instruction in the Immediate Window of
      the Visual Basic Editor and hitting Return:
      ?Application.UserLibraryPath

    2. In Windows Vista and later, this Directory which is not controlled by the UAC (User Account Control).
      However, it is a Hidden Directory and, therefore it is necessary to turn on
      the [Show hidden files, folders, and drives] in the Folder Options Control Panel.

    3. This is a common Directory for every Version of Excel.
      However, Directories are different according to the Windows Version and, furthermore,
      there is a specific Directory for each User registered by the Operative System.

      --- for Windows XP ---
      C:\Documents and Settings\USER \Application Data\Microsoft\Addins

      --- for Windows Vista and later ---
      C:\Users\USER \AppData\Roaming\Microsoft\Addins


[ ktMsgBox ] Function

It is the substitute function of MsgBox function that the designation of the font or the display position are possible.

"ktMsgBox wizard" is included in the workbook for demonstration.
It is OK even if it doesn't learn all syntax and rules.
It is possible to create a code easily if using the wizad.

[ Syntax ] [ Return Value ] [ Arguments ] [ Commentary ] [ Examples ]

[ Syntax ( ktMsgBox ) ]

ktMsgBox | ( Prompt, [Buttons], [Title], [Top], [Left],
 | [PromptColor], [BackColor], [Chime], [WaveFile],
 | [IconFile], [FontName], [FontSize], [WaitSecond],
 | [InputType], [UserDefBtn], [BackImage],
 | [ImageWidth], [ImageHeight], [ChimeColor],
 | [HyperLink1], [HyperLink2], [HyperLink3] )

Return Value | -- | Variant ( Integer / String / Boolean )
Prompt | -- | String
Buttons | -- | Long ( Optional Variant, vbMsgBoxStyle by default )
Title | -- | String ( Optional Variant )
--- Below, it is [ "ktMsgBox" Original Interface ] from here. ---
Top | -- | Variant ( Integer / Empty ) ( Optional Variant )
Left | -- | Variant ( Integer / Empty ) ( Optional Variant )
PromptColor | -- | Long ( Optional Variant, -1 by default As vbWindowText )
BackColor | -- | Long ( Optional Variant, -1 by default As vbButtonFace )
Chime | -- | Boolean ( Optional Variant, False by default )
WaveFile | -- | String ( Optional Variant )
IconFile | -- | String ( Optional Variant )
FontName | -- | String ( Optional Variant )
FontSize | -- | Integer ( Optional Variant )
WaitSecond | -- | Integer ( Optional Variant )
InputType | -- | String ( Optional Variant )
UserDefBtn | -- | String ( Optional Variant )
BackImage | -- | String ( Optional Variant )
ImageWidth | -- | Integer ( Optional Variant )
ImageHeight | -- | Integer ( Optional Variant )
ChimeColor | -- | Long ( Optional Variant, -1 by default As Brown )
HyperLink1 - HyperLink3 | -- | String ( Optional Variant )

It is a function only for VBA

[ Return Value ( ktMsgBox ) ]

The number of the clicked button returns. The buttons argument settings are same as MsgBox. The following shows Constant, Value and Description.
vbOK | 1 | [ OK ]
vbCancel | 2 | [ Cancel ]
vbAbort | 3 | [ Abort ]
vbRetry | 4 | [ Retry ]
vbIgnore | 5 | [ Ignore ]
vbYes | 6 | [ Yes ]
vbNo | 7 | [ No ]
When you use 'User defined Button', the buttons named '1st. Button' to '4th Button' from left to right, the following values returns. ( The inside of a parenthesis is the name of the constant used in Add-in. )
1st.Button | 9 | ( kt_MsgBox_UserBtn1 )
2nd.Button | 10 | ( kt_MsgBox_UserBtn2 )
3rd.Button | 11 | ( kt_MsgBox_UserBtn3 )
4th.Button | 12 | ( kt_MsgBox_UserBtn4 )
When use 'Wait Function', always [ vbOK ( 1 ) ] returns.
When use InputBox Function, the following values returns. --- Click Cancel button --- When get it as Variant type variable, [ False ] as Boolean type When get it as String type variable, "False" as String type --- After text is entered, press [ Enter ] key or Click [ OK ] button. --- Inpted text returnes as TEXT type. Even if it ware Numeric/Date type, it returnes as TEXT type as it is.
'#VALUE!' is returned, in case this function is wiritten in Cells.

[ Arguments ( ktMsgBox ) ]

The ktMsgBox function syntax has these named arguments:
The Description of [ Prompt , Buttons , Title ] are same as MsgBox.

'Fixed font' stands for the font is set of 'design / screen / messagebox property of control panel'.
( ForeColor )
It is the color which is gotten at system color constant [ vbWindowText ].
( BackColor )
It is the color which is gotten at system color constant [ vbButtonFace ].

In ktMsgBox, there is not [ Boldface ] specification in the font.
When it is necessary to specify the boldface, uses [ ktMsgBoxEX ].

Prompt
String expression displayed as the message in the dialog box.

If prompt consists of more than one line, you can separate the lines using [ vbCrLf ], between each line.

The size of the form depending on the width of the characters used.

The message is shown with 'fixed font' by default.
Buttons
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.

The buttons of ktMsgBox argument settings are:
--- Kind of Buttons ---
 | vbOKOnly ( 0 )
 | vbOKCancel ( 1 )
 | vbAbortRetryIgnore ( 2 )
 | vbYesNoCancel ( 3 )
 | vbYesNo ( 4 )
 | vbRetryCancel ( 5 )
--- Kind of Icons ---
 | vbCritical ( 16 )
 | vbQuestion ( 32 )
 | vbExclamation ( 48 )
 | vbInformation ( 64 )
<< ktMsgBoxAddin Original Icons >>
 | kt_MsgBox_MelodyIcon ( 80 )
 | kt_MsgBox_LimitIcon ( 96 )
 | kt_MsgBox_ErrIcon ( 112 )
 | kt_MsgBox_PencilIcon ( 4194320 )
 | kt_MsgBox_KeyIcon ( 4194336 )
 | kt_MsgBox_TrashIcon ( 4194352 )
 | kt_MsgBox_PinIcon ( 4194368 )
 | kt_MsgBox_ClipIcon ( 4194384 )
 | kt_MsgBox_MemoIcon ( 4194400 )
 | kt_MsgBox_CalendarIcon ( 4194416 )
 | kt_MsgBox_SandglassIcon ( 8388624 )
 | kt_MsgBox_CoffeeIcon ( 8388640 )
 | kt_MsgBox_AlarmClockIcon ( 8388656 )

--- Default Button ---
 | vbDefaultButton1 ( 0 )
 | vbDefaultButton2 ( 256 )
 | vbDefaultButton3 ( 512 )
 | vbDefaultButton4 ( 768 )

If omitted, the default value for buttons is 0.
Same as [ vbOKOnly + vbDefaultButton1 ]　with no icon.

If [ UserDefBtn ] is Specified, the Kind of Buttons (vbYesNo etc.) would be ignored.

On InputBox Function, the buttons are fixed as [ vbOKCancel + vbDefaultButton ] only.

The button is not displayed at all when I appoint Wait function.
Therefore, the designation of [ Kind of Buttons ] and [ Default Button ] becomes invalid.
Title
String expression displayed as the caption in the dialog box.
If omitted, the default string for caption is [ ktMsgBox ].
Top and Left
Setting the Left and Top property of the ktMsgBox like an userform.

If one or both are omitted, ktMsgBox is shown as Center-Owner.

If one or both are appointed Empty, ktMsgBox is shown as Center-Owner.
( added since Ver 3.30 )

When you display it on a certain Cell,
you appoint the value that converted the coordinate of the Cell by ktCell2Position function.
( added since Ver 3.30 please refer to Example)

(Note)
Press space key when it overflowed the screen and the position which cannot be clicked is specified by mistake. Since a setup of a default button is effective, ktMsgBox can be closed. If omitted, it is interpreted as [vbDefaultButton1(0)].
PromptColor
Specifies the color of a message.
You can use any integer that represents a valid color or Hexadecimal number.
You can also specify a color by using the RGB function with red, green, and blue color components.

It is possible to specify by 'the color name' using the ktWebColor2Num function.
( Eg. ) PromptColor := ktWebColor2Num("AquaMarine")

If omitted, -1 by default as [vbWindowText ].
BackColor
Specifies the color of a ktMsgBox's background.
You can use any integer that represents a valid color or Hexadecimal number.
You can also specify a color by using the RGB function with red, green, and blue color components.

It is possible to specify by 'the color name' using the ktWebColor2Num function.
( Eg. ) BackColor := ktWebColor2Num("AquaMarine")

If omitted, -1 by default as [ vbButtonFace ].
Chime
When you would like to make the sound to the chime by continuation reproduction, specify [ True ].

If omitted [WavFile]:
It is performed as [ System sounds : New Mail Notification ] not related to the presence of the icon.

Specify [WavFile]:
It is performed by specified [ wav ] file.
WaveFile
When you would like to performe your own wev file, specify the pathname of wavfile include the directory or folder, and the drive. So it is performed as follows.

When No icon:
[ System sounds : Default Beep ]

When icon is specifyied:
Icon is vbCritical | [ System sounds : System Error ]
Icon is vbQuestion | [ System sounds : Message (Question) ]
Icon is vbExclamation | [ System sounds : Message (Exclamation) ]
Icon is vbInformation | [ System sounds : Message (Asterisk) ]
other | [ System sounds : Message (Asterisk) ]
IconFile
When you would like to display your own icon, specify the pathname the picture file (ico, gif, bmp ) include the directory or folder.

The display size would be [ 36 x 36 pixel ]

Besides, in case IconFile is specified, specified icon such as [ vbCritical ] is ignored.
FontName
Specify this when you would like to use other font except 'fixed font'.
The FontName argument has following 5 kind (You can specify by both of Uppercase or Lowercase ) of argument, or specifies the font-name installed in your PC.

"ARI" | Arial
"CENT" | Century
"COUR" | Courier New
"ROMAN" | Times New Roman
"TAHOMA" | Tahoma
Except above, Not installed font-name | 'fixed font'
FontSize
Specify this when you would like to use other font size except fixed font size.
The font size argument has following 8 kind of argument.
[ 9, 10, 11, 12, 14, 16, 18, 20 ]
When except follwing argument is specified, it would be fixed fontsize.
WaitSecond
Specify this as seconds when you would like to close automatically, NOT dependent on click the button by user.You can specify 'from 5 to 60' sec.

The present time is displaied in the window upper part as real time duling 'Wait'.

Besides, when you specify [ WaitSecond ], [ Buttons ] [ InputType ] [ UserDefBtn ] [ HyperLink ] are ignored and there are no buttons on the message box.
InputType
If the width of input argument and the input column and a default display value are specified, it will function as InputBox.

Specifies continuously argument [ N( Numeric ) , D( Date ) , S( String ) , P( Password ) ] and width of input [ 1 to 8 ]. Defauld value should be written following to semi-colon ;.
( Eg ： "N1;500" , "D2;10/30/2002" )

On InputBox Function, the buttons are fixed as [ vbOKCancel + vbDefaultButton ] only.

When specified Numeric/Date, inputed data will be validity checked.

When specified Password, it becomes the following operation.
a ) A type character is displayed in "*".
b ) The defalut argument value is not displayed.
c ) When specified defalut argument, it is compared with the entry character.

( Note )
There is no function specify cell range by mouse, like InputBox Method of Excel VBA. It's the function instead of [ InputBox Function ] of VB Function. The author will not be include such a function. Thanks for your understand.
UserDefBtn
You can use user defined buttons ( Max 4 buttons ).
If [ UserDefBtn ] is Specified, the Kind of Buttons ( vbYesNo etc. ) would be ignored.

'The Caption of the Button' and 'The Access Key' should be written divided by semi-colon ( ; ) and Buttons are divided by comma ( , ).

"Cap1 [ ; Key1 ] [, Cap2 [; key2 ] [ , Cap3 [ ; key3 ] [ , Cap4 [ ; key4 ] ] ] ]"

Eg : "YesToAll(1);1,NoToAll(2);2,Yes;Y,No;N"

The width of buttons are fixed, so it cannot display so much long text.
BackImage
When you would like to use Background picture as background of msgbox, Specify the full path of the image file. [ jpg ,gif ,bmp ,wmf ] are can be specified.It would be arranged based on the left of the message window.

( The feature of "Tiling" spreading one image on UserForm all over is not supported. )
ImageWidth / ImageHeight
It would be enabled when [ BackImage ] is specified.

Genarally, the size of the message window would be fixed automatically according to text size and number of icon or buttons.
But when [BackImage] is specified, you can specify window size in order to make background image fit.

It's OK only specify width or height.
Omitted one would be fitted automatically.

Besise, if the value specified is smaller than autofit size, autofit would be enabled. When specify the value learger than picture image, the background of the window is sometimes shown, so I reccomend a littele smaller value than the picture size.
ChimeColor
Specify to change text color of upper side of the window when 'Chime Function' or 'Wait Function' is used. If omitted, the default color would be BROWN.

It is possible to specify by 'the color name' using the ktWebColor2Num function.
( Eg. ) ChimeColor := ktWebColor2Num("AquaMarine")
HyperLink1 - HyperLink3
When specify URL or Email address, text would be Hyperlink style with blue colored underline. Displays at fixed font and size.

The mouse icon would be changed to finger style, Web browser or mail application would be shown by click.

In case URL, specify from [ http: ]
In case mail address from [ mailto: ], and write email address without space.

( Note )
If [PromptColor / BackColor / ChimeColor] is specified by the hexadecimal number
like [&H 8000 - &H FFFF], it will be recognized as Minus Integer type,
so to be as Long type please add Ampersand to the end of [ End of character ]
like this.
[&H 8000& - &H FFFF& ]

[ Commentary ( ktMsgBox ) ]

The question like "I would like to specify a display position with a MsgBox function" is often posted, but MsgBox Function by VBA has not arguments about it.

This "ktMsgBox Function" was developed as the Function for the completely same usage as a "MsgBox function", and position specification is possible. Additionally, also [ Font Color / Background Color / Wev Sound / Icon ] can be customized.

Besides, the wave sounds are used in this "ktMsgBox Function" are same as "MsgBox Function". The Wave files are located in [ "Media" Directory ] of [ "Windows" Directory ] as follows.

When No icon | System sounds : Default Beep
When icon is specifyied |
Icon is vbCritical | System sounds : System Error
Icon is vbQuestion | System sounds : Message (Question)
Icon is vbExclamation | System sounds : Message (Exclamation)
Icon is vbInformation | System sounds : Message (Asterisk)
other | System sounds : Message (Asterisk)
Fixed Sound of "Chime Function" | System sounds : New Mail Notification

It can be customized so you can use properly this "ktMsgBox Function" according to the meaning of your message. (Eg. When you would like to use it for Error, [ Red Text ], or when abnormal occurrence [ Red Background ] and so on. )

It is better to specify a light color to background color, but to decide
' what color and which value are better ? '
actually, you can use the "Pallet form ( Full Color )" from the "ktMsgBoxWizard" of workbook named "ktMsgBoxDemo.xls". By using it, you can choose colors by your own eyes and can get RGB value.

Also icon can be changed freely, for example it's possible to display the "company logograph".

( Supplement )
All of the icons which are displayed when using
[ vbCritical / vbQuestion / vbExclamation / vbInformation ]
are my own making.

As other example, save as Wave file the voice like 'I found an Error!' or 'Processing has been done.' By using the files you can have message box with an original voice!

[ The Examples of Use ( ktMsgBox ) ]

ktMsgBox "Done"

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Dim Resp As Integer
Resp = ktMsgBox("Done" & vbCrLf & "Would like to continue？", vbOKCancel)
If (Resp = vbNo) Then
Exit Sub
End If

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Specify the display position //
------ Fixed position------
ktMsgBox "Input Error", _
vbOkOnly + vbInformation, "Input Date", _
Top:=100, Left:=200

----Adjust according to position of Input Item ot Position of the UserForm.----
ktMsgBox "Input Error", _
vbOkOnly + vbInformation, "Input Date", _
Top:=(Me.Top + 20), Left:=(Me.Left + 50)

ktMsgBox "Input Error", _
vbOkOnly + vbInformation, "Input Date", _
Top:=(Me.Top + TextBox1.Top - 10), _
Left:=(Me.Left + TextBox1.Left + 50)

---- Display it near [F5] Cell (Using ktCell2Position (Usable since Ver3.30) ----
Dim vntPos As Variant
Dim vntTop As Variant
Dim vntLeft As Variant
vntPos = ktCell2Position(ActiveSheet.Range("F5"))
If IsError(vntPos) Then
'[F5] Cell is out of an indication range --> omit Top/Left
vntTop = Empty
vntLeft = Empty
Else
vntTop = vntPos(0) + 5
vntLeft = vntPos(1) + 5
End If
ktMsgBox "Input Error", _
vbOkOnly + vbInformation, "Input Date", _
Top:=vntTop, Left:=vntLeft

( Note )
Press space key when it overflowed the screen and the position which cannot be clicked is
specified by mistake. Since a setup of a default button is effective, ktMsgBox can be closed.
If omitted, it is interpreted as [vbDefaultButton1(0)].

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Emphasize input error (Red font) //
ktMsgBox "Input Error", _
vbOkOnly + vbInformation, "Input Date", _
PromptColor:=vbRed

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Make background color to be a light color //
ktMsgBox "Done", _
vbOkOnly + vbInformation, "Monthly Work", _
BackColor:=&HE0E0E0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Notify by the chime, when the end of long processing is over //
ktMsgBox "Done" & vbCrLf & "Please return to your seat quickly.", _
vbOkOnly + vbInformation, "Monthly Work", _
PromptColor:=vbBlue, Chime:=True

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Notify by changing Background color (to thin crimson) - with chime //
ktMsgBox "Inputed data is unusual." & vbCrLf & "Processing is interrupted.", _
vbOkOnly + vbExclamation, "Monthly Work", _
BackColor:=&HC0C0FF, Chime:=True

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Change fixed sound to the sound of Mail arrival //
ktMsgBox "Done", _
WaveFile:="C:\Windows\Media\Notify.wav"

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Change Icon //
ktMsgBox "Done",_
IconFile:="C:\Windows\WinUpd.ico"

( Supplement )
Displayed size would be [ 36 x 36 pixcel ] when you use [ ico, gif, bmp ] file.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Change Font Style (to Arial / 14p) //
ktMsgBox "Inputed data is unusual." & vbCrLf & "Processing is interrupted.", _
vbOkOnly + vbExclamation, "Monthly Work", _
FontName:="ARI", FontSize:=14

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// User Defined Buttons //
ktMsgBox ".....................", _
UserDefBtn:="YesToAll(1);1,NoToAll(2);2,YES;Y,NO;N"

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// InputBox Features //
Dim dtmMyDate As Date
Dim rc As String
rc = ktMsgBox("Input the Date", _
InputType:="D2;" & Format(Date, "d/mmm/yyyy"))
If (rc <> "False") Then
dtmMyDate = CDate(rc)
End If

( Note )
The contents to receive in [rc] are the character string as it typed in.
When handling as the serial value, the contents of [rc] must be changed
into the date type at the CDate function.
Because there is an error checking feature, in case of this example, it is
guaranteed that the received character string is "the character string which
can be recognized as the date".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Uses the Image at Background //
// Closes automatically after a few seconds //
ktMsgBox "Hi. Good morning." & vbCrLf & _
"Today, too, let's exert." & vbCrLf & vbCrLf & _
"Sorry, this window closes automatically at 30 seconds." & vbCrLf & _
"Please keep waiting.", _
WaitSecond:=30,
BackImage:="C:\My Documents\ ........ \good_morning.gif", _
ImageWidth:=200, ImageHeight:=150

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// HyperLink //
ktMsgBox "xxxxxx Ver1.00" & vbCrLf & vbCrLf, _
HyperLink1:="http://www. ........ /index.htm", _
HyperLink2:="mailto:xxxx@........"


[ ktMsgBoxEX ] Function

It is the substitute function of MsgBox function that the designation of the font or the display position are possible.

"ktMsgBox wizard" is included in the workbook for demonstration.
It is OK even if it doesn't learn all syntax and rules.
It is possible to create a code easily if using the wizad.

[ Syntax ] [ Return Value ] [ Arguments ] [ Commentary ] [ Examples ]

[ Syntax ( ktMsgBoxEX ) ]

ktMsgBoxEX | ( Prompt, [Buttons], [Title], [Top], [Left], [BackColor],
 | [Chime], [WaveFile], [IconFile], [WaitSecond],
 | [InputType], [UserDefBtn], [BackImage],
 | [ImageWidth], [ImageHeight], [ChimeColor],
 | [HyperLink1], [HyperLink2], [HyperLink3] )

Return Value | -- | Variant ( Integer / String / Boolean )
Prompt | -- | kt_MsgBoxPromptType ( User defined type )
Buttons | -- | Long ( Optional Variant , vbMsgBoxStyle by default )
Title | -- | String ( Optional Variant )
---Below, it is [ "ktMsgBoxEX" Original Interface ] from here. ---
Top | -- | Variant ( Integer / Empty ) ( Optional Variant )
Left | -- | Variant ( Integer / Empty ) ( Optional Variant )
BackColor | -- | Long ( Optional Variant , -1 by default As vbButtonFace )
Chime | -- | Boolean ( Optional Variant , False by default )
WaveFile | -- | String ( Optional Variant )
IconFile | -- | String ( Optional Variant )
WaitSecond | -- | Integer ( Optional Variant )
InputType | -- | String ( Optional Variant )
UserDefBtn | -- | String ( Optional Variant )
BackImage | -- | String ( Optional Variant )
ImageWidth | -- | Integer ( Optional Variant )
ImageHeight | -- | Integer ( Optional Variant )
ChimeColor | -- | Long ( Optional Variant , -1 by default As Brown )
HyperLink1 - HyperLink3 | -- | String ( Optional Variant )
It is a function only for VBA

[ Return Value ( ktMsgBoxEX ) ]

Please refer to [ ktMsgBox ]

[ Arguments ( ktMsgBoxEX ) ]

As for the explanation except [ Prompt ], refer to [ ktMsgBox ].

Prompt
At user's side, argument named [ Prompt ] needs declare variables as follows.

Dim Prompt As kt_MsgBoxPromptType

User defined type [ kt_MsgBoxPromptType ] is declared as follows in
[ ktMsgBoxAddin.xla ]. You can use it for the referencing.

Public Type kt_MsgBoxPromptType
Message (1 To 10) As String
FName (1 To 10) As String
FSize (1 To 10) As Integer
FColor (1 To 10) As Long
FBold (1 To 10) As Boolean
End Type

When you would like to Initialization this User defined type, call Subroutine for
Initialization [ ktMsgBoxPromptTypeInit ] as follows. This subroutine is included
in [ ktMsgBoxAddin.xla ]. You can use it for the referencing.

Dim Prompt As kt_MsgBoxPromptType
Call ktMsgBoxPromptTypeInit( Prompt )

Can be specified within 10 groups.
The font ( Name, Size, Color ) can be specified in each group unit.

It would be Fixed font when you keep default value by [ ktMsgBoxPromptTypeInit ].

( Message )
To clear the value should be [ "" ] Empty Char.
Within Message, Line feed code (vbCrLf) is also effective.

When Empty Char ( "" ) comes out, message ends.

Whe you would like to insert space between the lines, use Line feed code or prepare the group containing a space.
( FName )
To clear the value should be [ "" ] Empty Char.
Specify this when you would like to use other font except 'fixed font'.
The FontName argument has following 5 kind ( You can specify by both of Uppercase or Lowercase ) of argument, or specifies the font-name installed in your PC.

"ARI" | Arial
"CENT" | Century
"COUR" | Courier New
"ROMAN" | Times New Roman
"TAHOMA" | Tahoma
Except above, Not installed font-name | 'fixed font'
( FSize )
To clear the value should be -1.
Specify this when you would like to use other font size except 'fixed font' size.
The font size argument has following 8 kind of argument.
[ 9, 10, 11, 12, 14, 16, 18, 20 ]
When except follwing argument is specified, it would be 'fixed font' size.
( FColor )
To clear the value should be -1.
Specifies the color of a message.
You can use any integer that represents a valid color or Hexadecimal number.
You can also specify a color by using the RGB function with red, green, and blue color components.

It is possible to specify by 'the color name' using the ktWebColor2Num function.
( Eg. ) Prompt(1).FColor = ktWebColor2Num ( "AquaMarine" )

If omitted, -1 by default as [ vbWindowText ].
( FBold )
To clear the value should be False.
When making a bold font, it sets "True".
There is a font which becomes a bold font automatically, too, when making a size big.

( Note )
If [ PromptColor / BackColor / ChimeColor ] is specified by the hexadecimal number
like [ &H 8000 - &H FFFF ], it will be recognized as Minus Integer type,
so to be as Long type please add Ampersand to the end of [ End of character ]
like this.
[ &H 8000& - &H FFFF& ]

[ The Examples of Use (ktMsgBoxEX) ]

Dim Prompt As kt_MsgBoxPromptType

Call ktMsgBoxPromptTypeInit(Prompt)
With Prompt
.Message(1) = "*　An error occurred.　*"
.FSize(1) = 14
.FName(1) = "CENT"
.FColor(1) = vbRed

.Message(2) = "The reason why the error occurred xxx" & vbCrLf & _
"-----" & vbCrLf & "-----"
.FSize(2) = 10
.FColor(2) = vbBlue

.Message(3) = "Detailed information1:xxxxx" & vbCrLf & _
"Detailed information2:xxxxx" & vbCrLf & _
"Detailed information3:xxxxx"
.FSize(3) = 9
.FColor(3) = vbBlack
End With

ktMsgBoxEX Prompt, _
vbOkOnly + vbExclamation, "Monthly Work", _
Chime:=True


[ ktMsgBoxPromptTypeInit ] Subroutine

It is the subroutine to initialize [ Prompt ] argument of the ktMsgBoxEX function.

[ Syntax ]

ktMsgBoxPromptTypeInit | ( Prompt )
Prompt | -- | kt_MsgBoxPromptType ( User defined type , ByRef )

[ Arguments ]

[ Prompt ] ( ByRef )
 | It appoint the variable of [ kt_MsgBoxPromptType ] type. The contents of each element in the variable are set with a predetermined initial value. User defined type [ kt_MsgBoxPromptType ] is declared as follows in [ ktMsgBoxAddin.xla ]. You can use it for the referencing. Public Type kt_MsgBoxPromptType Message (1 To 10) As String FName (1 To 10) As String FSize (1 To 10) As Integer FColor (1 To 10) As Long FBold (1 To 10) As Boolean End Type

[ Commentary ]

Please refer to [ ktMsgBoxEX ].

[ The Examples of Use ]

Dim Prompt As kt_MsgBoxPromptType

Call ktMsgBoxPromptTypeInit(Prompt)
With Prompt
.Message(1) = "*　An error occurred.　*"
.FSize(1) = 14
.FName(1) = "CENT"
.FColor(1) = vbRed

.Message(2) = "The reason why the error occurred xxx" & vbCrLf & _
"-----" & vbCrLf & "-----"
.FSize(2) = 10
.FColor(2) = vbBlue

.Message(3) = "Detailed information1:xxxxx" & vbCrLf & _
"Detailed information2:xxxxx" & vbCrLf & _
"Detailed information3:xxxxx"
.FSize(3) = 9
.FColor(3) = vbBlack
End With

ktMsgBoxEX Prompt, _
vbOkOnly + vbExclamation, "Monthly Work", _
Chime:=True


[ ktWebColor2Num ] Function

It is a function to convert Color Names using by HTML into RGB.

[ Syntax ]

ktWebColor2Num | ( WebColor, [ Kind ] )
Return Value | -- | Variant ( Long / String )
WebColor | -- | String
Kind | -- | String (Optional Variant , "D" by default )

[ Return Value ]

The value of RGB corresponding to Color Names returns.

Kind = "D" ......... Numerical value returns.
Kind = "H" .......... Hex string ("&HBBGGRR", 8 characters fixation ) returns.
Kind = "W" ......... Hex string ("#RRGGBB", 7 characters fixation ) returns.

When color names are wrong, -1 or a null character ("") returns.

[ Arguments ]

[ WebColor ]
 | Appoint Color Names using by HTML. Please refer to the list of Color Names. The Color Names are all right in both of the capital letter / small letter.
[ Kind ]
 | Appoint the type of the color value. Omit or "D" ......... Numerical value returns. "H" .......... Hex string ("&HBBGGRR", 8 characters fixation ) returns. "W" ......... Hex string ("#RRGGBB", 7 characters fixation ) returns.

[ Commentary ]

This function prepared to appoint [ Prompt Color / Background Color ] in Color Names.


[ ktCell2Position ] Function

When you display UserForm on a cell, it is a function to convert the screen coordinate of the cell into Top/Left of UserForm.
( added Ver 3.30 )

[ Syntax ]

ktCell2Position | ( Target, [ PaneIdx ] )
Return Value | -- | Variant ( Array )
Target | -- | Range
PaneIdx | -- | Integer ( Optional Variant , Zero by default )

[ Return Value ]

A cell coordinate of Target is converted into the value of [ Top & Left ] for UserForm.
It return as Array(Top, Left).
Element(0) is Top , Element(1) is Left. Top & Left are Long type.

When Target is out of a window indication range, [ #VALUE! ] returns.

When window is "Freeze Panes" or "Split Panes", there is the limit by the Excel version.
( a ) before Excel2003
You can appoint only a cell of the 1st Pane.
(When a window is divided into four; the Upper Left area.
When is divided up and down; the Upper area.
When is divided into right and left; the Left area.)

When you appoint the cell of other areas ( 2nd - 4th Pane ), [ #VALUE! ] returns.

( b ) Excel2007 and later
You can appoint the cell of all areas of 1st - 4th Pane.

[ #VALUE! ] is returned, in case this function is wiritten in Cells.

[ Arguments ]

[ Target ]
You appoint the Range object of a Cell displaying the UserForm.

When you appoint Range of Cells, top cell (Upper Left) becomes Target.

When Target is out of a window indication range, [ #VALUE! ] returns.

[ PaneIdx ]
[ Zero or Omit ]
There is no division of the window.
Or the automatic detection of target area (Pane).
[ 1 ]
Upper Left area ( is divides into 4 )
Left side area ( is divided right and left )
Upper side area ( is divided up and down )
[ 2 ]
Upper Right area ( is divides into 4 )
Right side area ( is divided right and left )
Bottom side area ( is divided up and down )
[ 3 ]
Bottom Left area ( is divides into 4 )
[ 4 ]
Bottom Right area ( is divides into 4 )

---- A window is not divided ----
Please omit it ( or Zero ).
Even if 1-4 is appointed, it is ignored.

---- Freeze Panes ----
Please omit it ( or Zero ).
The target area ( Pane ) is detected in Target automatically.
In case of before Excel2003,
when you appoint the cell of 2nd - 4th Panes, [ #VALUE! ] returns.

---- Split Panes ----
When you omit it ( or Zero ), an Active area becomes the target Pane.
When you appoint 1 - 4, the designated area becomes the target Pane.
When you appoint value more than the number of Panes, [ #VALUE! ] returns.
In case of before Excel2003,
when you appoint the cell of 2nd - 4th Panes, [ #VALUE! ] returns.

[ Commentary ]

In cell and UserForm, the units of coordinate of the position ( Top and Left ) are different.
Therefore, you cannot use the screen coordinate of the cell for the UserForm.

ktCell2Position function converts the screen coordinate ( Top and Left ) of the cell
into the indication point ( Top and Left ) of UserForm.

[ The Examples of Use ]

-- UserForm is displayed at the position of the cell which I double-clicked. --

Private Sub Worksheet_BeforeDoubleClick_
(ByVal Target As Range, Cancel As Boolean)
Dim vntPos As Variant

vntPos = ktCell2Position(Target)
If IsError(vntPos) Then
MsgBox "Error"
Cancel = True
Exit Sub
End If

Load UserForm1
With UserForm1
.StartUpPosition = 0
.Top = vntPos(0)
.Left = vntPos(1)
.Label1.Caption = Target.Address(False, False) & vbCrLf & vbCrLf & _
"CellTop = " & Target.Top & vbCrLf & vbCrLf & _
"CellLeft= " & Target.Left & vbCrLf & vbCrLf & _
"Top = " & .Top & vbCrLf & vbCrLf & _
"Left= " & .Left
.Show
End With
Cancel = True
End Sub

-- Example to use in ktMsgBox --
( It is displayed at the position of the F5 cell. )

Dim vntPos As Variant
Dim vntTop As Variant
Dim vntLeft As Variant

vntPos = ktCell2Position(ActiveSheet.Range("F5"))
If IsError(vntPos) Then
'[F5] Cell is out of an indication range --> omit Top/Left
vntTop = Empty
vntLeft = Empty
Else
vntTop = vntPos(0) + 5
vntLeft = vntPos(1) + 5
End If

ktMsgBox "xxxxxxxx", vbOkOnly + vbInformation, _
Top:=vntTop, Left:=vntLeft
End If


[ ktPaletteColorEX ] Subroutine

This subroutine is a palette color tool using for a definition of ForeColor / BackColor
in [ ktMsgBox Wizard ].

[ Syntax ]

Call ktPaletteColorEX ( PaletteEXData , [ PaletteEXBook ] )
PaletteEXData | -- | kt_PaletteEXType ( ByRef , User defined type )
PaletteEXBook | -- | String ( Optional Variant, "Active" by default )

At user's side, argument named [ PaletteEXData ] needs declare variables as follows.

Dim PaletteEXData As kt_PaletteEXType

User defined type [ kt_PaletteEXType ] is declared as follows in ktMsgBoxAddin.xla.
You can use it for the referencing.

Public Type kt_PaletteEXType
 | ClipBoard As Boolean | Copy or NOT to the clipboard when color is selected.
 | SelectRGB As Boolean | Deployment or NOT of RGB selection screen
 | Flg As String | When Call: Empty / "Init" / "Book" / "UnLoad"
 |  | When Return: "Get" / "Cancel"
 | ColorIndex As Integer | 1 to 56，Deployment RGB screen, fixed as [ 99 ]
 | ColorLong As Long | 0 to 16777215
 | ColorHex As String | BBGGRR style ( fixed as 6 digits )
 | ColorB As Integer | 0 to 255 ( Blue )
 | ColorG As Integer | 0 to 255 ( Green )
 | ColorR As Integer | 0 to 255 ( Red )
End Type

[ Arguments ]

PaletteEXData ( ByRef )
These are the arguments for specifies the conditions of operation and get the
information about the selected color. It needs declare variables.
( ClipBoard )
Specify copy or do not copy, the hexadecimal number of the selected color
such as "&HBBGGRR".
True : Copy
False : do NOT copy
( SelectRGB )
Specify enable or disable of deployment of RGB screen.
True : Enable
False : Disable

(Note)
When get color for using to cells, please specify as False.
Can Not be specified colors except for 56 color palette.
( Flg )
This argument is used in both directions,
Instructions of 'Initialization or Release' of form,
or Gets return value of 'Select or Cancel'.

  1. When color was selected , "Get" is returned.
    When Cancel , "Cancel" is returned.

  2. Call for the first time,
    the user needs to specify "Init" for Initialization.

  3. Before 'End macro' or 'Close the Userform',
    specify "Unload" for release the Palette form.

  4. When you would like to call this function many times continuously,
    you do NOT need to Initialization ( "" ) except for above (b) (c).
    It is no problem for calling as argument is. ( Get / Cancel )

  5. When you would like to chage Workbooks for get Color Palette,
    Specify "Book" and Specify the workbook in [ PaletteEXBook ].
( ColorIndex ,ColorLong ,ColorHex ,ColorB ,ColorG ,ColorR )
When color was selected, the value is returned as above.

When Canceled, for each value is returned as Zero or Null character ("").

Further , 'ColorHex' is the 6 digits String.
There is no attached "&H" to a head of the string,if you need it please add it yourself.

The value of 'ColorIndex' when you open RGB screen of the right side is fixed with 99.
PaletteEXBook
When you use the Palette of ActiveWorkbook, specify as "Active" by Constant.

When you use except the Palette of ActiveWorkbook, specify the value of the [ Name property ] of the workbook. ( EG. You can specify like "Book1.xls" as constant, also you can specify the Name property itself. )

Besides, when the workbook is NOT open, the Palette of ActiveWorkbook is applied.

It works only when [ Flg = "Init" or "Book" ].

[ Commentary ]

This subroutine is a palette color tool using for a definition of ForeColor / BackColor
in [ ktMsgBox Wizard ].

[ How to use Palette form ]

  1. Choose 56 colors of the standard among color palette (Style before Excel2003).
    When only a palette of the left side shows, you click a palette button and choose it.

  2. Change RGB with a slider bar and make full color.
    1. You can make any color with a full color making screen of the right side.
      It is completion in the click of the "Color" button.

    2. You change the color level with a slider bar and a spin button of the RGB.

    3. "3Color" spin button changes three colors together.

    4. As for the color that you appointed in [Lock R/G/B], in the case of a change
      by "3Color" spin button, a value is fixed.
      (A change of the chroma is possible artificially.)

  3. Choose only "Web Safe Color".
    When you turn ON "Web Safe Color" button, it is set a limit only in a color to become
    the common color development with Windows and Macintosh.

[ The Examples of Use ]

--- EG. When call it from an UserForm  ---
Call this function by DoubleClick on TextBox [ txtColor ] , and make BackColor
of an Userform as selected color. ( You can choose fullcolor )

[ Userform module ]
Private ColorData As kt_PaletteEXType

Private Sub UserForm_Initialize()
ColorData.ClipBoard = False
ColorData.SelectRGB = True
ColorData.Flg = "Init"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If (ColorData.Flg <> "Init") Then
ColorData.Flg = "UnLoad"
Call ktPaletteColorEX(ColorData, ThisWorkbook.Name)
Else
'No need to [Unload] because it have never
called.
End If
End Sub

Private Sub txtColor_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call ktPaletteColorEX(ColorData, ThisWorkbook.Name)
If (ColorData.Flg = "Get") Then
txtColor.Value = ColorData.ColorHex
Me.BackColor = ColorData.ColorLong
End If
Cancel = True
End Sub

--- EG. When call it from an CommandButton on Worksheet ---
Call this function from an CommandButton on Worksheet, and make BackColor
of an cell as selected color.  ( You can choose only 56 colors in this case. )

[ Std module, Declare section ]
Public ColorData As kt_PaletteEXType

[ ThisWorkbook module ]
Private Sub Workbook_Open()
ColorData.ClipBoard = False
ColorData.SelectRGB = False
ColorData.Flg = "Init"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If (ColorData.Flg <> "Init") Then
ColorData.Flg = "UnLoad"
Call ktPaletteColorEX(ColorData, ThisWorkbook.Name)
Else
'No need to [Unload] because it have never
called.
End If
End Sub

[ Worksheet module ]
Private Sub CommandButton1_Click()
Call ktPaletteColorEX(ColorData, ThisWorkbook.Name)
If (ColorData.Flg = "Get") Then
ActiveCell.Interior.Color = ColorData.ColorLong
ActiveCell.Offset(0, 1).Value = _
"(" & ColorData.ColorIndex
& ") 0x" & ColorData.ColorHex
End If
End Sub


When you click the right button of the column of ForeColor / BackColor in the Wizard tool, "Color-Name choice dialogue" is displayed.
The work such as "the search of this page" and "the copy / paste of color names" is not necessary.

ColorName List ( &H bb gg rr )
[ A ]
AliceBlue (&HFFF8F0) | AntiqueWhite (&HD7EBFA) | Aqua (&HFFFF00)
AquaMarine (&HD4FF7F) | Azure (&HFFFFF0) |
[ B ]
Beige (&HDCF5F5) | Bisque (&HC4E4FF) | Black (&H000000)
BlanchedAlmond (&HCDEBFF) | Blue (&HFF0000) | BlueViolet (&HE22B8A)
Brown (&H2A2AA5) | BurlyWood (&H87B8DE) |
[ C ]
CadetBlue (&HA09E5F) | Chartreuse (&H00FF7F) | Chocolate (&H1E69D2)
Coral (&H507FFF) | CornflowerBlue (&HED9564) | CornSilk (&HDCF8FF)
Crimson (&H3C14DC) | Cyan (&HFFFF00) |
[ D ]
DarkBlue (&H8B0000) | DarkCyan (&H8B8B00) | DarkGoldenRod (&H0B86B8)
DarkGray (&HA9A9A9) | DarkGreen (&H006400) | DarkKhaki (&H6BB7BD)
DarkMagenta (&H8B008B) | DarkOliveGreen (&H2F6B55) | DarkOrange (&H008CFF)
DarkOrchid (&HCC3299) | DarkRed (&H00008B) | DarkSalmon (&H7A96E9)
DarkSeaGreen (&H8FBC8F) | DarkSlateBlue (&H8B3D48) | DarkSlateGray (&H4F4F2F)
DarkTurquoise (&HD1CE00) | DarkViolet (&HD30094) | DeepPink (&H9314FF)
DeepSkyBlue (&HFFBF00) | DimGray (&H696969) | DodgerBlue (&HFF901E)
[ F ]
FireBrick (&H2222B2) | FloralWhite (&HF0FAFF) | ForestGreen (&H228B22)
Fuchsia (&HFF00FF) |  |
[ G ]
Gainsboro (&HDCDCDC) | GhostWhite (&HFFF8F8) | Gold (&H00D7FF)
GoldenRod (&H20A5DA) | Gray (&H808080) | Green (&H008000)
GreenYellow (&H2FFFAD) |  |
[ H ]
HoneyDew (&HF0FFF0) | HotPink (&HB469FF) |
[ I ]
IndianRed (&H5C5CCD) | Indigo (&H82004B) | Ivory (&HF0FFFF)
[ K ]
Khaki (&H8CE6F0) |  |
[ L ]
Lavender (&HFAE6E6) | LavenderBlush (&HF5F0FF) | LawnGreen (&H00FC7C)
LemonChiffon (&HCDFAFF) | LightBlue (&HE6D8AD) | LightCoral (&H8080F0)
LightCyan (&HFFFFE0) | LightGoldenRodYellow (&HD2FAFA) | LightGreen (&H90EE90)
LightGrey (&HD3D3D3) | LightPink (&HC1B6FF) | LightSalmon (&H7AA0FF)
LightSeaGreen (&HAAB220) | LightSkyBlue (&HFACE87) | LightSlateGray (&H998877)
LightSteelBlue (&HDEC4B0) | LightYellow (&HE0FFFF) | Lime (&H00FF00)
LimeGreen (&H32CD32) | Linen (&HE6F0FA) |
[ M ]
Magenta (&HFF00FF) | Maroon (&H000080) | MediumAquaMarine (&HAACD66)
MediumBlue (&HCD0000) | MediumOrchid (&HD355BA) | MediumPurple (&HDB7093)
MediumSeaGreen (&H71B33C) | MediumSlateBlue (&HEE687B) | MediumSpringGreen (&H9AFA00)
MediumTurquoise (&HCCD148) | MediumVioletRed (&H8515C7) | MidnightBlue (&H701919)
MintCream (&HFAFFF5) | MistyRose (&HE1E4FF) | Moccasin (&HB5E4FF)
[ N ]
NavajoWhite (&HADDEFF) | Navy (&H800000) |
[ O ]
OldLace (&HE6F5FD) | Olive (&H008080) | OliveDrab (&H238E6B)
Orange (&H00A5FF) | OrangeRed (&H0045FF) | Orchid (&HD670DA)
[ P ]
PaleGoldenRod (&HAAE8EE) | PaleGreen (&H98FB98) | PaleTurquoise (&HEEEEAF)
PaleVioletRed (&H9370DB) | PapayaWhip (&HD5EFFF) | PeachPuff (&HB9DAFF)
Peru (&H3F85CD) | Pink (&HCBC0FF) | Plum (&HDDA0DD)
PowderBlue (&HE6E0B0) | Purple (&H800080) |
[ R ]
Red (&H0000FF) | RosyBrown (&H8F8FBC) | RoyalBlue (&HE16941)
[ S ]
SaddleBrown (&H13458B) | Salmon (&H7280FA) | SandyBrown (&H60A4F4)
SeaGreen (&H578B2E) | SeaShell (&HEEF5FF) | Sienna (&H2D52A0)
Silver (&HC0C0C0) | SkyBlue (&HEBCE87) | SlateBlue (&HCD5A6A)
SlateGray (&H908070) | Snow (&HFAFAFF) | SpringGreen (&H7FFF00)
Steelblue (&HB48246) |  |
[ T ]
Tan (&H8CB4D2) | Teal (&H808000) | Thistle (&HD8BFD8)
Tomato (&H4763FF) | Turquoise (&HD0E040) |
[ V ]
Violet (&HEE82EE) |  |
[ W ]
Wheat (&HB3DEF5) | White (&HFFFFFF) | WhiteSmoke (&HF5F5F5)
[ Y ]
Yellow (&H00FFFF) | YellowGreen (&H32CD9A) |


[ ktMsgBox Wizard ]

This tool is the Macro code Generation Wizard for ktMsgBox / ktMsgBoxEX .

In ktMsgBox and ktMsgBoxEX, there are a great many arguments because the function is abundant.
However, the setting that you tested in this Wizard is just converted into a macro code.

You can move it immediately if you paste the macro code on a module window from a clipboard.

Test button
The contents which you set are displayed as message box by this Wizard dialogue.

Source Code button
The contents which you set in this Wizard dialogue are output as a macrocode to a clipboard.
Please just paste it on a module window.

Prompt textbox
It is Field inputting a message to show message box.
The newline is [ Shift + Enter ].

Font combobox
Mnemonic symbol of the five fonts and all fonts installed in a PC are set in a ComboBox.
Fixed font stands for the font is set of 'design / screen / messagebox property of control panel'.

Please choose a font to use among a list.

[ Mnemonic symbol ]
"ARI" | : Arial
"CENT" | : Century
"COUR" | : Courier New
"ROMAN" | : Times New Roman
"TAHOMA" | : Tahoma

Size optionbutton
Please choose a font size from [ 9 - 20 ].

Bold checkbox
It becomes the bold-face when you turn it ON.
Only in the case of Ex check box is turned ON, you can appoint 'Bold'.

Ex checkbox & Clear button
turned OFF --> ktMsgBox , turned ON --> ktMsgBoxEx
In ktMsgBoxEX, Bold-faced designation and the designation of the Font & Color is possible
every line group unit.

When you click Clear button,
the contents of all line groups which you registered are removed and return to initial state.

Group spinbutton
It is a spin button changing the line group (Max 10).
The contents of the designated line group are displayed by Prompt column.
Only in the case of Ex check box is turned ON, you can appoint 'Group'.

Resp Code label
The kind of the button which you clicked or
the content that you input by InputBox function on a test is displayed.

PromptColor & BackColor label
& Clear button
It is a column appointing Text Color & Background Color of the message box.
Left click --> Palette form performs pop up.
Right click --> Color Name form performs pop up.
When you cancel color designation, please click Clear button.

Title textbox
It is a column appointing string to display in a Caption of the message box.
It becomes "ktMsgBox" or "ktMsgBoxEX" when you omit it.

Buttons optionbutton
Please choose standard Button to display in message box.

Icon combobox
Please choose Icon in the list to display in message box.
There are 17 built-in Icons which may be specified for use.
(the 4 Standard Icons of MsgBox, plus 13 additional icons of ktMsgBoxAddin)

Default Button optionbutton
It is a button appointing a number of the Default button.

Top ＆ Left textbox
It is a column appointing a position ( Top & Left ) displaying the message box.
If one or both are omitted, the message box is shown as Center-Owner.

When you display the message box on a certain Cell,
you appoint the value that converted the coordinate of the Cell by ktCell2Position function.
( please refer to Example of ktMsgBox )

Chime checkbox
It is a column appointing the consecutive reproduction of the Wave sound.
The Wave sound continues sounding until you push the button of the message box.

Omitted [WavFile] : It is performed by [ Notify.wav : The sound of Mail arrival ].
Specify [WavFile] : It is performed by specified [ wav ] file.

Wait Second textbox
Designation to be closed automatically after having displayed it for several seconds is possible.
It is the column appointing number of seconds ( 5 - 60 ).

Chime/Wait Guidance Color label
& Clear button
It is a column appointing Text Color of the Guidance message for Chime and Wait function.
When you omit it, it becomes Brown.
Left click --> Palette form performs pop up.
Right click --> Color Name form performs pop up.
When you cancel color designation, please click Clear button.

Input Type textbox
When you use InputBox function,
it is a column appointing [ Width, Default value , Attribute ] of the input column.

User Define Button textbox
When you use User Define Button, it is a column appointing the contents of the buttons.

Wave file & Icon File textbox
When you use a Wave sound or an Icon, you appoint the full path of the file.
Open File dialog is displayed by double-click the column.

BackImage & ImageWidth & ImageHeight textbox
When you use a Background Image for message box, you appoint the full path of the file.
Open File dialog is displayed by double-click the column.
You can appoint size of the message box ( Width & Height ).

HyperLink textbox
When you use a Hyperlink message, it is a column appointing the link address.
Site address is [ http:// ....... ] .
Mail address is [ mailto: ...... ] .


[ Color Palette form ]

It is a support tool making a Color in [ Color Palette (style before Excel2003) ] or [ RGB ].
The Color Palette of ActiveWorkbook is applied.
When you decide a color, Hex string of "&HBBGGRR" is copied to a clipboard.

You can call this Form from VBA macro if you use ktPaletteColorEX subroutine.
Before Excel2003, the color that a cell can set is only 56 colors of the Palette.
You can set full color of the RGB designation to the control of UserForm.

---- Left side screen ( Color Palette : 56 colors ) ----

1. 'Web Safe Color' toggle button ( Top Left )
When you turn it ON, you can choose only WebSafeColor ( Color development same
as Windows & Mac).
The combination of each RGB color is only [ 0x00 , 33 , 66 , 99 , CC , FF ].

2. Spin button ( Top Right )
By the spin button, you can change indication having [ Right side screen : RGB ] or not.

3. Color Palette buttons ( 56 colors )
When RGB screen is not displayed,
Hex-String ( &HBBGGRR ) of the color that you chose in Palette is copied to
the Clipboard.
When RGB screen is displayed,
the color that you chose in Pallete reflects it to RGB screen.

When you put mouse cursor on 56 colors of palette,
it is shown popup [ ColoIndex ] and [ Hex-String ( &HBBGGRR ) ] of the color.

4. Workbook Name box ( Bottom Left )
The color palette of workbook displayed here is used.
It is usually ActiveWorkbook.

5. 'Cancel' button ( Bottom Right )
The Close button of userform (X) becomes invalid.
When you cancel input, and then click this button.

---- Right side screen ( RGB : Full color ) ----

6. ScrollBars & SpinButtons ( R , G , B )
You change a value of RGB ( 0 to 255 ) and make a color.
The ScrollBar links the SpinButton.

7. '3 Color' spin button
Three colors ( R & G & B ) increase and decrease together.
It is adjustment of the Luminosity.

8. 'Lock' toggle buttons ( R , G , B )
This button is used with [ 3Color spin button ].
You fix one color that you turned ON among three colors.
It is adjustment of the Chroma.

9. Color indication frame
The color ( and Hex-String : 0xBBGGRR ) that you made by adjusting RGB is displayed here.

10. 'Color' button
When RGB screen ( Right side ) is displayed, it is a decision button.
Hex-String ( &HBBGGRR ) of the color that you made is copied to the Clipboard.


[ Terms of use ]

  • -- CopyRight --
    ktMsgBoxAddin is FREEWARE.

    Keiichi.Tsunoda ( AddinBox ) holds all copyrights of ktMsgBoxAddin.

    In addition, about any damage that occurred by having used this software,
    the author shall not take responsibility at all.

  • -- Redistribution --
    Redistribution to users of the distribution file is free in case to NOT changing the contents.

  • -- Reproduction --
    Reproduction to the homepages of the distribution file is prohibition without permission.

    Please guide the link to the following AddinBox site.
    http://addinbox.sakura.ne.jp/ktMsgBox_Series_English.htm
    ( Old Site ) http://www.h3.dion.ne.jp/~sakatsu/ktMsgBox_Series_English.htm

[ Communication ]

Mail : addinbox@h4.dion.ne.jp Author : Keiichi Tsunoda

My HP : AddinBox ( Jpn ) http://addinbox.sakura.ne.jp/index.htm
( ktMsgBox Eng ) http://addinbox.sakura.ne.jp/ktMsgBox_Series_English.htm

BBS : http://addinbox.webspace.ne.jp/bbs/ (Japanese )

Please write in [ question , impression , demand , bug report ] at an email or BBS,
and please write it by expression simple as possible.
( I do not understand that it is used many abbreviations in the English ).


AddinBox( ktMsgBox function/EXCEL )

[ History ]

-- 16 Apr. 2018 Ver3.40 --
  1. Beep sound processing when displaying MsgBox has been changed.
    I changed the way of specifying the sound set in [ Control Panel > Sound ]
    from the way of directly specifying the sound file in the [ Windows\Media ]
    directory with the file name.

  2. When Width / Height of UserForm was dynamically set in Excel2016, it corresponded
    to a bug that was narrower (7.5 p) than in earlier version.

    [ UserForm is shorter and narrower than in earlier versions of Excel ]
    https://support.microsoft.com/en-us/help/3156159

-- 19 Sep. 2015 Ver3.30 --
  1. I added "ktCell2Position" function.
    This function converts the screen coordinate of the Cell into the value of Top/Left for UserForm.
    You can display ktMsgBox/ktMsgBoxEX at the position of the designated Cell by using this function together.

  2. It became able to appoint "Empty" with the Top/Left argument of the ktMsgBox/ktMsgBoxEX function.

  3. I changed the file name of the Demonstration Workbook from ktMsgBoxTrial.xls to ktMsgBoxDemo.xls.

  4. Excel97 is not supported.
-- 20 Sep. 2011 Ver3.20 --
  1. I supported Windows ( 64bit ) / Excel ( 64bit ).

  2. I changed help file into CHM file system.
-- 28 Jun. 2003 Ver3.13 --
  1. In case of Excel2002, there was a fault which becomes the RunTime-Errorin case of Excel start-up after add-in registration. By to change the way of getting the font-list which is cause, it canceled a fault.
-- 10 Jun. 2003 Ver3.12 --
  1. It added [ktMsgBox_Note.txt] to the distribution file.

  2. In case of Excel97, it corrected because the operation of the color setting form of [ ktMsgBox Wizard ] had a defect.

  3. Excel2000 and lthe ater, "Auto List Members" worked to the argument of [Buttons], but because the defect occurs in case of Excel97, it cancels "Auto List Members".
-- 3 Apr. 2003 Ver3.11 --
  1. There was a fault in [ktMsgBox Wizard] to be provided in [ktMsgBoxTrial.xls].
    Because it became an error when choosing the color of [Web Safe Color] from the color choice dialog for [Color Name], it corrected this fault.

  2. When using WindowsXP (WindowStyle:XP_Mode), the CommandButton is located too closely to the bottom of the UserForm.
    This close location caused the height of the Title-Bar of WindowsXP (WindowsStyle : XP_Mode) to be located higher than the WindowsXP (Classic_Mode ) / 98 / ME / NT / 2000.
    This bug has been corrected.
-- 14 Jan. 2003 Ver3.10 --
  1. 1st. version for English.

-- 22 Dec. 2002 Ver3.10 ( Japanese Ver. only ) --

-- 7 Nov. 2002 Ver3.00 ( Japanese Ver. only ) --

-- 18 Oct. 2002 Ver2.00 ( Japanese Ver. only ) --

-- 30 July 2002 Ver1.00 ( Japanese Ver. only ) --


[ Special Thanks ]

When developing the English version, I had Colo's help with the English translation.

When checking the Add-In's operation in x64, I had the assistance of Kinuyo & Asami.

When reviewing the English sentences such as the Help Documents, I had the assistance of Ricardo A. Badani.

--------------------------------------------------------------------
In order to acquire 'Fixed font information' from the 'Property of a screen', the codes of following site were used.

--- Heropa's Homepage --- ( Japanese only )
http://plaza5.mbn.or.jp/~heropa/
// VB Tips/Acquisition of information of font of non-client area //

--------------------------------------------------------------------
In order to acquire 'Installed Font List',the codes of following site were used.

--- The Spreadsheet Page ( JWalk & Associates ) ---
http://j-walk.com/ss/
// Getting a List of Installed Fonts //
http://j-walk.com/ss/excel/tips/vbafunctions.htm