+ Reply to Thread
Results 1 to 6 of 6

Programatically change the caption of all Worksheet (ActiveX) controls

  1. #1
    Registered User
    Join Date
    04-02-2011
    Location
    Ypres, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    19

    Programatically change the caption of all Worksheet (ActiveX) controls

    How can I change programatically the caption(s) of all (ActiveX) controls on a worksheet (NOT a UserForm)?

    I know how to do it in VBA for just ONE control, but I can't figure it out for ALL controls at the same time.

    The purpose is to change the captions when the user selects a radio button (Dutch or French) to select the language for the control captions.

    I have made a separate worksheet containing 3 columns:
    - control names
    - Dutch text of the controls captions
    - French translation of the controls captions

    Each row contains the strings for each control.

    The text of the captions must be searched in that worksheet mentioned above using Application.WorksheetFunction.VLookup.

    Any help would be greatly appreciated.

    Thanks

    redseujac
    Last edited by redseujac; 04-04-2011 at 01:17 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Programatically change the caption of all Worksheet (ActiveX) controls

    Here you go,
    Please Login or Register  to view this content.
    The attached uses Two different ways to change the caption
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-02-2011
    Location
    Ypres, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Programatically change the caption of all Worksheet (ActiveX) controls

    Thanks a lot Dave.

    Both methods are working. However the second one (using .Offset) is rather slow especially with a large language sheet.

    Also I noticed an error 1004 ("property VLookup from Class WorksheetFunction cannot be retrieved") if not all the OLEObjects are included and translated in the language sheet. So some error handling should be done here.

    Meanwhile I have found a workbook "multilingual wizard.xlsm" (demonstrating how to allow the user to choose from 3 languages in a dialog box) on the CDROM that accompanies the book "Excel 2007 Power Programming with VBA" written by the well-known John Walkenbach. Unfortunately it handles a User Form and the code does not suit OLEObjects on a worksheet.

    I would be grateful if you could take a look at the part of the code concerning especially the translation of the captions and adapt it for worksheet Objects.

    Thanks again

    redseujac
    Last edited by davesexcel; 04-03-2011 at 07:28 AM.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Programatically change the caption of all Worksheet (ActiveX) controls

    Please Post your own workbook.

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!


    FYI-JWalks workbook also uses the Vlookup approach to get the caption

  5. #5
    Registered User
    Join Date
    04-02-2011
    Location
    Ypres, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Programatically change the caption of all Worksheet (ActiveX) controls

    Quote Originally Posted by davesexcel View Post
    Please Post your own workbook.

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook
    I'm afraid that's not possible. My workbook contains 21 worksheets, 12.670 code & comment lines, 586 procedures and 1.200 controls. So...
    Quote Originally Posted by davesexcel View Post
    FYI-JWalks workbook also uses the Vlookup approach to get the caption
    I know. The VLookup approach is the one I want to use, but JWalks workbook handles Userform controls and not worksheet OLEObjects. I have tried to use his code adapting it for my worksheet objects, but unfortunately without success.

    In my initial post I think I have explained what I am trying to achieve: the purpose is to change the captions of all worksheet objects when the user selects a radio button (Dutch or French) to select the language for the control captions.

    Your method explained in your first reply post is working, but I'd like to test also the code used by JWalk in the mentioned workbook. Unfortunately I'm not succesful in adapting it
    Jacques

  6. #6
    Registered User
    Join Date
    04-02-2011
    Location
    Ypres, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Programatically change the caption of all Worksheet (ActiveX) controls

    Finally I have succeeded in converting JWALK's workbook code, so it's working now for worksheet OLEObjects.

    Problem solved.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1