Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Language problem with VBA

    I have a VBA procedure that will export partial data from an large Excel database to a new Excel file.
    I've managed to export a VBA module to be reimported into the new file.
    My problem now is when I try to link a dropdown to a macro, there is a language difficulty.
    I live in Switzerland, and write my VBA on an English version of Office.
    Most people here use French.
    I use these 2 lines to link the dropdown to the macro:

    ActiveSheet.Shapes("Drop Down 2").Select
    Selection.OnAction = ActiveWorkbook.Name & "!FamFiltr"

    The problem is that in the French version 'Drop Down 2' is 'Zone Combinée 2'.
    I could probably get around this by getting the avtive Office language version as a variable and writing something like

    If English
    ActiveSheet.Shapes("Drop Down 2").Select Else
    ActiveSheet.Shapes("'Zone Combinée 2") Select
    End If

    But it would be much better if there was a way to universally identify the dropdown so that it would work in any language.
    I'd be grateful for any ideas.
    Dave
    Last edited by dyfedg; 2011-08-12 at 10:35.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    When are the dropdowns being created? When they are created they could be explicitly named instead of relying on the default name given by excel...

    Steve

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve,
    Sorry, I've been on holiday.
    The dropdown is created using the following, and assigned to a macro.
    I have searched for a way to give the dropdown a specific name when it is created, but so far no luck.
    There is quite a bit of code before this and all that works fine.
    Any help would be much appreciated.

    ' Now create the dropdown box and set the range
    ActiveSheet.DropDowns.Add(67.5, 3, 151.5, 23.25).Select
    With Selection
    .Placement = xlFreeFloating
    .PrintObject = False
    End With
    ' Link the family codes to the combobox
    With Selection
    .ListFillRange = "FamilyCodes!B1:B22"
    .LinkedCell = ""
    .DropDownLines = 22
    .Display3DShading = True
    End With

    ' Then set the combobox to use the macros for filtering
    ActiveSheet.Shapes("Drop Down 2").Select
    Selection.OnAction = ActiveWorkbook.Name & "!FamFiltr"

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can do it all with one With construct and you never have to select or call the name. If desired you can add the line to name it if you want to refer to it later in code (or you can just use it as a variable and not bother naming and keeping track of the names]...

    Code:
    With ActiveSheet.DropDowns.Add(67.5, 3, 151.5, 23.25)
      .Placement = xlFreeFloating
      .PrintObject = False
    ' Link the family codes to the combobox
      .ListFillRange = "FamilyCodes!B1:B22"
      .LinkedCell = ""
      .DropDownLines = 22
      .Display3DShading = True
    ' Then set the combobox to use the macros for filtering
      .OnAction = ActiveWorkbook.Name & "!FamFiltr"
    'Name the combobox if desired
      .Name = "MyCombo"
    End With
    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    dyfedg (2011-08-30)

Tags for this Thread

Posting Permissions

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