Unicode captions in UserForm and MessageBox?

[Michael Mueller]

Hi.

I'm having problems using chinese Excel.
While writing some vba I would like to use userforms and messageboxes.
But I'm not able to set chinese letters as caption of this userforms.
or any messages in the messageboxes.
I only get '?' displayed. Excel itself is capable of displaying chinese.

but how can I display chinese Letters in UserForm.Caption ans with msgbox?

Thanks
Michi

PS:
I'm using
W2K3 Chinese
O2k3 with Chinese User Interface

 

[Jonathan West]

Hi Michael,

Hmm. I wonder whether the font used by the caption has all the Chinese
glyphs in it.

Try adding a label to your form, setting the Font of the label to Arial
Unicode MS, and put some chinese characters into the label and see if they
display. If you don't have Arial Unicode MS, it should be available on your
Office CD somewhere.

If the labels displays OK, then the problem is definitely with the font used
for the caption. As far as I'm aware, this is set by Windows, not by
Office, - useforms pick up the font and background from the current Windows
theme.

 

[Tony Jollans]

This is not a font issue. The VBA Msgbox function interfaces to the ANSI
version of the Windows facility. To get unicode text in a message box you
need to use the MessageBoxW API and pass the unicode string without letting
VBA automatically convert it.

At the start of you module put this declaration:

Private Declare Function APIMsgBox _
Lib "User32" Alias "MessageBoxW" _
(Optional ByVal hWnd As Long, _
Optional ByVal Prompt As Long, _
Optional ByVal Title As String, _
Optional ByVal Buttons As Long) _
As Long

and then, where you want the message box, code like this:

Dim ChinesePrompt As String, Response As String
ChinesePrompt = Selection.Text ' or however you get Chinese text
Response = APIMsgBox(Prompt:=StrPtr(ChinesePrompt), Buttons:=vbYesNo)

I'm sorry but I don't know how to do it with Userforms - or even if it's
possible. I shall keep an eye on the thread, though

 

[Karl E. Peterson]

This is not a font issue.

I think you are both right, to various degrees. Jonathan is correct that
UserForms are drawn with a system font for non-client (titlebar) text, and
you're right that VBA uses the ANSI export for the MessageBox API. The
jury's probably still out on which (A/W) is being used for SetWindowText,
but I'd suspect it's A and in that case even a Unicode font isn't likely to
be of much use.

I'm sorry but I don't know how to do it with Userforms - or even if
it's possible. I shall keep an eye on the thread, though

<AOL!>

 

[Karl E. Peterson]

If the labels displays OK, then the problem is definitely with the
font used for the caption.

Hmmm, more to it than that, it appears. I just set my caption font
(systemwide) to "Arial Unicode MS", and ran the following code in an Excel
(2000) userform:

Private Declare Function SetWindowText Lib "USER32" Alias
"SetWindowTextW" (ByVal hWnd As Long, ByVal lpString As Long) As Long
Private Declare Function GetForegroundWindow Lib "USER32" () As Long

Private Sub UserForm_Click()
Dim cap As String, i As Long
Dim nc As New CNonClientMetrics

For i = &H4E10 To &H4E23
cap = cap & ChrW(i)
Next i
Call SetWindowText(GetForegroundWindow(), StrPtr(cap))

Set Label1.Font = nc.CaptionFont 'see note below
Label1.Caption = cap
End Sub

The label worked just fine, of course. But the titlebar still displayed all
question marks. This may not be a problem that has an answer.

Note: For those unfamiliar, the CNonClientMetrics class I used here is
available at http://vb.mvps.org/samples/NCMetrics

Later... Karl

 

[Karl E. Peterson]

Hmmm, more to it than that, it appears.

Too true, I'm afraid. Added this to the routine in the last post:

Private Declare Function IsWindowUnicode Lib "USER32" (ByVal hWnd As
Long) As Long

If IsWindowUnicode(GetForegroundWindow) Then
Label2.Caption = "Unicode"
Else
Label2.Caption = "ANSI"
End If

Guess which caption we ended up with?

Conclusion: No way, no how, will a Unicode caption be displayed on a VBA
UserForm (short of taking over the entire drawing of the titlebar yourself).

 

[Karl E. Peterson]

Conclusion: No way, no how, will a Unicode caption be displayed on a
VBA UserForm (short of taking over the entire drawing of the titlebar
yourself).

Further confirmation. This code ends up displaying "Unicode" in Label2:

Const GW_CHILD = 5
If IsWindowUnicode(GetWindow(GetForegroundWindow(), GW_CHILD)) Then
Label2.Caption = "Unicode"
Else
Label2.Caption = "ANSI"
End If

 

[Tony Jollans]

Thank you Karl. Some good information.

 

[Jonathan West]

Further confirmation. This code ends up displaying "Unicode" in Label2:

Const GW_CHILD = 5
If IsWindowUnicode(GetWindow(GetForegroundWindow(), GW_CHILD)) Then
Label2.Caption = "Unicode"
Else
Label2.Caption = "ANSI"
End If

Thanks Karl. Its good to have a true VB hacker helping out the mere Office
mortals

 

[Karl E. Peterson]

Thanks Karl. Its good to have a true VB hacker helping out the mere Office
mortals

Aw shucks...

Truthfully, I did that test on O2K. It'd be interesting to know if it's the
same thing on newer versions. Kinda silly, seems to me, that they set up
the main UserForm as ANSI but the client space within it as Unicode.
Oversight in this version? I tend to think not, because it would seem to me
to be far more work (ie, damn near impossible to do unconciously) to call
both RegisterClassA and RegisterClassW than it is to route both calls to the
same function. So, it might be a fun one to toss back at 'em, and see if
they blink. ;-)

 

[Michael Mueller]

At the start of you module put this declaration:

Private Declare Function APIMsgBox _
Lib "User32" Alias "MessageBoxW" _
(Optional ByVal hWnd As Long, _
Optional ByVal Prompt As Long, _
Optional ByVal Title As String, _
Optional ByVal Buttons As Long) _
As Long

Works fine here, but I found another "feature"
How about placing ANSI-Text for Title and UNICODE for Prompt?
With the above API-Call it converts my ANSI-Title to UNICODE-Letters, so the
ANSI-Text gets unreadable.

Do you have a fix for this, too?

Thanks for your fantastic piece of code!

Michi

 

[Michael Mueller]

How about placing ANSI-Text for Title and UNICODE for Prompt?

Found a solution:

Made an own custom-messagebox call:

Private Declare Function APIMsgBox Lib "user32" Alias "MessageBoxW"
(Optional ByVal hwnd As Long, Optional ByVal Prompt As Long, Optional ByVal
Title As String, Optional ByVal Buttons As Long) As Long

Function cMsgBox(cPrompt As String, _
Optional cButtons As VbMsgBoxStyle = vbOKOnly, _
Optional cTitle As String = "Microsoft Excel",
Optional cIsTitleANSI as Boolean = False) As Long
Dim tTitle
If cIsTitleAnsi Then
For x = 1 To Len(cTitle)
tTitle = tTitle & ChrW(Asc(Mid(cTitle, x, 1))) & ChrW(0)
Next x
cTitle = tTitle
End If
cMsgBox = APIMsgBox(Application.hwnd, StrPtr(cPrompt), cTitle, cButtons)
End Function

Greets
Michi

 

[Jonathan West]

Aw shucks...

Truthfully, I did that test on O2K. It'd be interesting to know if it's
the
same thing on newer versions. Kinda silly, seems to me, that they set up
the main UserForm as ANSI but the client space within it as Unicode.
Oversight in this version? I tend to think not, because it would seem to
me
to be far more work (ie, damn near impossible to do unconciously) to call
both RegisterClassA and RegisterClassW than it is to route both calls to
the
same function. So, it might be a fun one to toss back at 'em, and see if
they blink. ;-)

I haven't had time to test it, but there has been no significant change in
UserForms since Office 2K. Actually, as far as I know there has been no
change at all. So its a pretty safe bet that the same issues exist in later
versions of Office.

It might be something that could be tossed into the Office 2007 beta...


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Karl E. Peterson]

Found a solution:

Made an own custom-messagebox call:

Private Declare Function APIMsgBox Lib "user32" Alias "MessageBoxW"
(Optional ByVal hwnd As Long, Optional ByVal Prompt As Long, Optional
ByVal Title As String, Optional ByVal Buttons As Long) As Long

Function cMsgBox(cPrompt As String, _
Optional cButtons As VbMsgBoxStyle = vbOKOnly, _
Optional cTitle As String = "Microsoft Excel",
Optional cIsTitleANSI as Boolean = False) As Long
Dim tTitle
If cIsTitleAnsi Then
For x = 1 To Len(cTitle)
tTitle = tTitle & ChrW(Asc(Mid(cTitle, x, 1))) & ChrW(0)
Next x
cTitle = tTitle
End If
cMsgBox = APIMsgBox(Application.hwnd, StrPtr(cPrompt), cTitle,
cButtons) End Function

Why not take the same tactic with the Title as you do wtih the Prompt? Just
declare it ByVal As Long, and pass the StrPtr to it.

 

[Karl E. Peterson]

I haven't had time to test it, but there has been no significant
change in UserForms since Office 2K. Actually, as far as I know there
has been no change at all. So its a pretty safe bet that the same
issues exist in later versions of Office.

It might be something that could be tossed into the Office 2007
beta...

Sure seems like a Bug (albeit, almost certainly "by design") to me...

 

[Michael Mueller]

How about placing ANSI-Text for Title and UNICODE for Prompt?

Why not take the same tactic with the Title as you do wtih the Prompt? Just
declare it ByVal As Long, and pass the StrPtr to it.

Damn, sometimes it's so easy

Thanks for your help
Michi

 

[Karl E. Peterson]

Damn, sometimes it's so easy

Shhhh... That's supposed to be a secret... ;-)