Update File after region. parameters change w/o closing file

[Mat]

Hi,

Is there a way of updating a file after changing parameter in windows regional parameters, without closing file and re-open it?

I have a workbook open, check for the decimal separator, if it's the wrong one, then change it in regional parameters, and if I want to see the change I must close and re-open the file... can I update w/o closing?

 

[Andrew Poulsom]

Re: Update File after region. parameters change w/o closing

For me (Windows 2000, Excel 2000) the change occurs in Excel immediately.

 

[Mat]

Re: Update File after region. parameters change w/o closing

Oh thanks for the informations... (I'm on Win98 here, xl97)
I'll test on WinXP with Xl2000 at home then.

Do you think it's function of the xl version, or win version?

 

[Andrew Poulsom]

Re: Update File after region. parameters change w/o closing

It's also instantaneous on Windows 95 with Excel 97.

 

[Mat]

Re: Update File after region. parameters change w/o closing

Ohhh ok

I tried again, it's instantaneous with xl97 on win98 also, but it isn't instantaneous when I change it with VBA :
This is what I use:

Option Explicit

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

Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()

Private Const LOCALE_SDECIMAL = &HE
Private Sub ChangeDecSep()
Dim DecSep
Dim iAns As Integer
Dim sMsg As String

DecSep = Application.International(xlDecimalSeparator)

'change the setting of the character displayed as the decimal separator.
If DecSep = "," Then
     Call SetLocalSetting(LOCALE_SDECIMAL, ".") 'change to "."
End If
       
End Sub

Private Function SetLocalSetting(LC_CONST As Long, Setting As String) As Boolean
    Call SetLocaleInfo(GetUserDefaultLCID(), LC_CONST, Setting)
End Function

Is there a way to modify it so it will update instantaneously like if I was changing it with the control panel?

 

[Andrew Poulsom]

Re: Update File after region. parameters change w/o closing

I don't know. But why are you finding it necessary to change the Regional Settings?

 

[Mat]

Re: Update File after region. parameters change w/o closing

Because I got some problem of pasting values of textboxes in cells, for number with decimals.

Sure I could use the code (with substitute) that you gave me before, but since the spreadsheet is almost finished and I see that bug after, I would have a lot of modifications to do to handle the "," as the decimal separator.

And it's not only because of pasting textboxes value in cell... I also have macros that look for a value in a cell, then search for a page with the same name. As an example, say my variable is X, in vba I put :
X= 1.1
then range("a1").value = x
and sheets("test").name = "Number " & X

So the range will become 1,1 and the sheet name will be Number 1.1
and I need both to be 1.1...

Sure I could made all the modifications I need, but I'll need to make a lot of tests to be sure that all is ok. So I found it easier to force the user of using the "." as the decimal separator, since I'm sure it is working correctly.