• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If you have non-English install, can you test a line of VBA for me?

jeffreyweir

Active Member
Howdy folks. I'm trying to work out if a line of VBA code works in non-English versions, and across multiple versions of Excel.


Can some kindly folk run this sub after changing something in the worksheet (e.g. just type something in a cell somewhere, then run this sub).

It just reads the undo stack, and reports what the last thing listed in the undo stack was.

Code:
Sub test()
MsgBox Application.CommandBars(14).FindControl(ID:=128).List(1)
End Sub

If you just changed some text, it should say something like "Typing 'test' in L18"

I need to know:
  1. Whether it worked
  2. What international version of Excel you have (e.g. German, Dutch)
  3. What version of Excel you used (e.g 2010, 2013)
  4. What the message in the messagebox actually was in the language it was reported. i.e. in English, the message would say the above. I need to know what Execl displays for other languages, if possible. In particular, I'm curious to know whether it puts apostophes around the word 'test' (or whatever word you entered)
Many thanks.
 
Last edited:

LoL ! (your french is so good ! Spoken or a good translator ?)

I'll try on a french Excel 2007 later …
 
Hi Jeffreyweir,

I do not have non-english version, but was curious to test but I too got the same run time error 91 object variable or with block variable not set.

Should I add any reference lib?

Regards,
Prasad DN
 
Hi Jeff,

I am not sure if the index you used is incorrect or it differs from location to location.

I ran following:
Code:
Sub test1()
Dim cmdbar As CommandBar
Dim cntrl
On Error Resume Next
For Each cmdbar In Application.CommandBars
  Set cntrl = cmdbar.FindControl(ID:=128)
  If Not cntrl Is Nothing Then
  Debug.Print cmdbar.NameLocal & cmdbar.Index
  End If
Next cmdbar
End Sub

I found the ID to be correct and following works for me. Commandbar Index is 12 for me.
Code:
Sub test()
MsgBox Application.CommandBars(12).FindControl(ID:=128).List(1)
End Sub
I use standard US settings and gives word in quotes 'test'.
 
Hi prasaddn. Looks like this is a version thing and not a language thing. What version are you running? i.e. 2003/2007/2010/2013
 
Marc: Google Translate, je le crains.

Reminds me of a joke. A French guy want's to ask an English gal out, so he attempts to say in English:
When I look in your eyes, time stands still.

Unfortunately he translates it a little too literally:
Your face would stop a clock.
 
Hi Jeff,

I am not sure if the index you used is incorrect or it differs from location to location.

I ran following:
Code:
Sub test1()
Dim cmdbar As CommandBar
Dim cntrl
On Error Resume Next
For Each cmdbar In Application.CommandBars
  Set cntrl = cmdbar.FindControl(ID:=128)
  If Not cntrl Is Nothing Then
  Debug.Print cmdbar.NameLocal & cmdbar.Index
  End If
Next cmdbar
End Sub

I found the ID to be correct and following works for me. Commandbar Index is 12 for me.
Code:
Sub test()
MsgBox Application.CommandBars(12).FindControl(ID:=128).List(1)
End Sub
I use standard US settings and gives word in quotes 'test'.

I used your code and found it is 15 in my system. After changing 14 to 15 in Jeff's code i am getting exactly the same output as expected by Jeff!!
 

Works too with index #12 in french Excel 2007 with this message :

Saisie de « test » dans L18
 

Same previous post message with index #3 in french Excel 2003 …

Instead of index number, can't you use the name ("Standard") ?
 
Hi Mark. I believe "Standard" is "Staandard" in the Dutch version, and so was trying to get around possible international issues. With no luck.
 

Only with NameLocal property but
Name property (internal command bar name) is in English
even on a French version, what else ?!

Code:
Sub FindControlCommandBar()
    Dim CmdBar As CommandBar
    On Error Resume Next

    For Each CmdBar In Application.CommandBars
        Set cntrl = CmdBar.FindControl(ID:=128)
        If Not cntrl Is Nothing Then Debug.Print CmdBar.Name & " : " & CmdBar.Index
    Next CmdBar
End Sub

For example French command bar "Formulaires" (NameLocal)
is command bar "Forms" (Name) …

It's like worksheet functions in French in an Excel worksheet
but stays in English in VBA, better for international needs …​
 
Last edited:
Back
Top