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

Mat

Mat

Well-known Member
Joined
Sep 17, 2003
Messages
509
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Re: Update File after region. parameters change w/o closing

For me (Windows 2000, Excel 2000) the change occurs in Excel immediately.
 
Upvote 0
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?
 
Upvote 0
Re: Update File after region. parameters change w/o closing

It's also instantaneous on Windows 95 with Excel 97.
 
Upvote 0
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:

Code: 
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Add a rule to the VBA to remove filters
Replies
4
Views
114
Ramadan
R
T
  • Question Question
thisworkbook.saved changes inexplicably
Replies
9
Views
220
tomluigi
T
N
  • Question Question
Slow vba with opening 60 files
Replies
7
Views
311
NickvdB
N
A
  • Question Question
Looking for help in Excel Macros scripts
Replies
0
Views
124
Adnan199
A
B
  • Question Question
Using VBA to retrieve specific pages from PDFs
Replies
1
Views
330
Big_Al_fae_the_rigs
B

Forum statistics

Threads
1,225,521
Messages
6,185,457
Members
453,292
Latest member
Michandra02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top