Link to home
Start Free TrialLog in
Avatar of mrosier
mrosierFlag for United States of America

asked on

Japanese characters in VBA button in Excel 2010

Hi Folks! I have a problem. I have a user using 2010 Office who is creating VBA buttons for it. This file is being created in Japanese and it is working fine in the Excel sheet, but in the VBA button caption is turning up only ????? characters. We are trying to take japanese text from a spreadsheet cell and pasting it into the caption field of a VBA button's properties window. I have attached a picture with a circle demonstrating the caption field with ???? characters that are the result of pasting text from the spreadsheet that is also circled. My obvious question is what do I need to do get the japanese characters to show properly? My user is on Windows 7 Pro 64bit. Everything is up to date. Thanks!!
Japanese-Character-issue.jpg
Avatar of Ryan
Ryan
Flag of United States of America image

I suspect its a problem with that field only accepting Unicode-8 Text. Characters 0 to 255. Rather than Unicode-16 which contains 65535 characters.

I'm not sure, but you might try coding the workbook under an event, like
Private Sub Workbook_Open()
and setting the caption of that button through code to whatever it should be.  You may have to use
Chrw(<character code for char1>) & Chrw(<character code for char2>) & Chrw(<character code for char3>) & ...
Avatar of mrosier

ASKER

thanks for all the info! My user is not sure what is meant by “coding the workbook under an event”. Can you elaborate more on how to do this? Maybe an example of how to do this in relation to what we are trying to do?
Open Excel, press Alt+F11
Open the Workbook module and paste the following.  you'll have to find the character codes for the characters being used, and replace then with the <XXXX> stuff below.

Private Sub Workbook_Open()
  button1.caption = Chrw(<character code for char1>) & Chrw(<character code for char2>) & Chrw(<character code for char3>)
end sub

Open in new window

Avatar of mrosier

ASKER

ok my user tried this using his button named "Continue":

Private Sub Workbook_Open()

Continue.Caption = ChrW(c12353) & ChrW(c12354)

End Sub
 

No luck.  It give me a debug error:  “Object Required”
FYI, Continue in Continue.Caption is highlit yellow
Avatar of mrosier

ASKER

is there no way to just install japanese characters into the VBA component of Excel the way you would for Office in general?
Sorry, I didn't test my idea. When I did do it, it seems very difficult.  Anyway, this code should work.

Private Sub Workbook_Open()
    ActiveSheet.Shapes.Range(Array("Continue")).Select
    Selection.Characters.Text =  ChrW(12353) & ChrW(12354)
end sub
Avatar of mrosier

ASKER

thanks MrBullwinkle but he got this error attached
Error.JPG
Replace ActiveSheet with whatever the name of the sheet is.

Private Sub Workbook_Open()
    Sheets("Sheet1").Shapes.Range(Array("Continue")).Select
    Selection.Characters.Text = ChrW(12353) & ChrW(12354)
End Sub
I also noticed once I ran that code to update the characters, I didn't need to run it again, so you could delete the code. Provided you don't want to change it again.

Not sure why you couldn't just type it in to start with.
Avatar of mrosier

ASKER

he is still getting that last error: “Object doesn’t support this property or method”  Just to clarify he is usig VBA in Excel 2007 if that makes a difference in what the programming object can support?
Avatar of mrosier

ASKER

Sorry, yes, I realize I gave the wrong version of Excel in the beginning. It is not 2010, it is 2007. Sorry!
Avatar of mrosier

ASKER

can we reverse just a little bit? In the initial picture I sent showing the ???, that is most likely a unicode 8 as opposed to 16 being accepted. Is there simply a way to get that VBA object's caption property to use Unicode 16 instead?
If you want to get special characters in that caption, then just run the code I've supplied, then delete the code.  You may be able to copy paste into there, I dunno. But you already have the solution.

I'm running E2010 which I tested it on. If you're on E2007, that may be why the code isn't running, AND might be why the characters aren't showing (maybe 2007 doesn't support them at all).  I can't test that from here.
Avatar of mrosier

ASKER

ok I will have someone using 2010 test this and get back to you. Is there a way to get input from a 2007 Excel user involved in this thread as well though?
Likely, since there are so many comments most other experts won't look at this question.

I got that solution by recording a macro of me changing the text to something. Then going and editing the code that the macro generated.
Avatar of mrosier

ASKER

I had my 2010 user take a shot and he had the same problem. Is it at all possible to view what you did in video form so that my users can see exactly the process you took?
ASKER CERTIFIED SOLUTION
Avatar of Ryan
Ryan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mrosier

ASKER

macros are indeed turned on. I will pass this along and see what they see. Thanks!
Avatar of mrosier

ASKER

Ok I see the difference now! My user needs to use an ActiveX control while that is a form control. Are you able to offer assistance given this difference?
You'll have to use the form control. I just tested the Active X control and it doesn't support those characters as best I can tell.  I can't figure out the code to do what I did with the form control either.
Avatar of mrosier

ASKER

so short of getting a japanese configured computer, we won't be able to use japanese characters in the captions?
Change the button to a form button instead of an Active X control. Im pretty sure they both have same functionality.
Avatar of mrosier

ASKER

unfortunately the users need to use ActiveX controls in this project. I see the solution works for the form ones, but my users need to use ActiveX. Is this as far as EE can take me on this issue then?
This question thread is likely dead.

You could start a new question, explicitly saying its an ActiveX button, on Excel 2007, and linking to this question saying that the thread got cluttered before we isolated the issue.  That way they won't think you just double posted.

You can choose to accept the 6th post, as it would be the solution for a Form control, or close the question.
Avatar of mrosier

ASKER

This is a good solution to the form control issue. My issue was a bit different, and I failed to offer enough information to make it clear, but this is a good solution for people having this sort of problem!