How do i modify the List Seperator Character

I have a question regarding the regional settings on a PC.

I have two applications, one is an Excel workbook with lots of VBA and another is a Word document also with lots of VBA.

The Word document will only work properly when the LIST SEPARATOR is set to ;
The Excel sheet will only work properly when the LIST SEPARATOR is set to ,

This is set via:

Start
Control Panel
Regional & Language Options
Regional Options Tab
Customize
List Separator  , or ; depending on application.

Its not possible to modify things to make it work with a general list separator... tried that earlier ... see

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21817252.html


However, my question is, is there any way of setting the regional settings via VBA when the sheet opens so that an error does not occur ?

Alternatively can i check the value of the List Seperator and if its not a ; as required then use a pop up box to issue a warning to the user.

Thanks

Gordon
GordonMassonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dmangConnect With a Mentor Commented:
Found a sample on the net that was originally taken from winapi.txt, and tweaked it some!

You can retrieve and set regional settings using the following APIs....

Declare Function GetLocaleInfo Lib "kernel32" Alias _
"GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String, ByVal cchData As Long) As Long

Declare Function SetLocaleInfo Lib "kernel32" Alias _
"SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean

Declare Function GetUserDefaultLCID% Lib "kernel32" ()


'parm value
Public Const LOCALE_SLIST = &HC

'Subs to retireve / set:

Public Sub Get_locale() ' Retrieve the regional setting

      Dim Symbol As String
      Dim iRet1 As Long
      Dim iRet2 As Long
      Dim lpLCDataVar As String
      Dim Pos As Integer
      Dim Locale As Long
     
      Locale = GetUserDefaultLCID()
     
      iRet1 = GetLocaleInfo(Locale, LOCALE_SLIST, _
      lpLCDataVar, 0)
      Symbol = String$(iRet1, 0)
     
      iRet2 = GetLocaleInfo(Locale, LOCALE_SLIST, Symbol, iRet1)
      Pos = InStr(Symbol, Chr$(0))
      If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           MsgBox "Regional Setting = " + Symbol
      End If

End Sub

Public Sub Set_locale() 'Change the regional setting

      Dim Symbol As String
      Dim iRet As Long
      Dim Locale As Long      

      Locale = GetUserDefaultLCID() 'Get user Locale ID
      Symbol = "-" 'New character for the locale
      iRet = SetLocaleInfo(Locale, LOCALE_SLIST, Symbol)
     
End Sub




0
 
GrahamSkanRetiredCommented:
HI Gordon.
Where, in Word, does the list separator need to be a ";"?
0
 
GordonMassonAuthor Commented:
Hi Graham

Its a historic document that i cant really change... i dont think???

code is to do with inserting a table of contents at a bookmark ...TOC

Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""

I assumee that changing this to:
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
would fix the problem????? b ut im not sure if there is anything else in the code that uses this.... is it only formulas that use the list seperator ?

perhaps i can do somthing like ...... (using syntax that detects the list seperator

If ListSeperator = ";" Then
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""
Else
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
End If

Thanks  Gordon
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GrahamSkanConnect With a Mentor RetiredCommented:
I have done a few quick tests (Word 2003), and I think that the TOC field, while being sensitve to the List separator setting, seems to work as  one would expect.
0
 
GordonMassonAuthor Commented:
Sorry Graham ... not with you ... are you saying that the code below will work (or some varient of that which has the correct syntax)....

If ListSeperator = ";" Then
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""
Else
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
End If


I think this is the safest way for me to go so that the code would detect how the List Seperator is set up on the PC (some pc's that use this have it set as ' others as ;) and then run the line of code which suits the application / PC.

If this is correct can you tell mw what the code would be to detect the PC's List Seperator setting in VBA?

Thanks

gordon
0
 
GordonMassonAuthor Commented:
Hi folks

I got this working using this....

Dim fld As Field
If ActiveDocument.Bookmarks.Exists("TOCApp") = True Then
       
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldTOC Then
            If InStr(fld.Code.Text, "SUBAPPENDIX") Then
                fld.Code.Text = "TOC \F \N \T " & "APPENDIX" & Application.International(wdListSeparator) & "1" & Application.International(wdListSeparator) & "SUBAPPENDIX" & Application.International(wdListSeparator) & "2"
                Exit For
            End If
        End If
    Next fld
   
End If


Gets me round the problem of PC's with different List Separators set.

i have split the points...thanks for your help...

Gordon
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.