Unicode captions in UserForm and MessageBox?
Michael Mueller
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
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.
--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
"Michael Mueller" <michael....@de.bosch.com> wrote in message
news:e10ekp$e52$1...@ns2.fe.internet.bosch.com...
Tony Jollans
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 :)
--
Enjoy,
Tony
"Jonathan West" <jw...@mvps.org> wrote in message
news:OMf3V3NW...@TK2MSFTNGP03.phx.gbl...
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!>
--
Working without a .NET?
http://classicvb.org/
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
> Jonathan West wrote:
>> 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.
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
--
Enjoy,
Tony
"Karl E. Peterson" <ka...@mvps.org> wrote in message
news:eyehzQPW...@TK2MSFTNGP05.phx.gbl...
Jonathan West
>> 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
Thanks Karl. Its good to have a true VB hacker helping out the mere Office
mortals :-)
Karl E. Peterson
>
> "Karl E. Peterson" <ka...@mvps.org> wrote in message
> news:eyehzQPW...@TK2MSFTNGP05.phx.gbl...
> > Karl E. Peterson wrote:
> >> 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
>
> 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
>
> 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
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
>> 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...
Sure seems like a Bug (albeit, almost certainly "by design") to me...
Karl E. Peterson
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.
Michael Mueller
>
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
>>>> 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 :-)
Shhhh... That's supposed to be a secret... ;-)