International Range style settings

Hi there,

I am using Excel 2007, the Ducth version.
I have created a workbook with macros, partly recorded.
In one of the I am using some code like

With ActiveCell
   If .Value < gdblNormValue Then
      .Style = "Ongeldig"
   End If
End With

In the English version it shoul be

With ActiveCell
   If .Value < gdblNormValue Then
      .Style = "Bad"
   End If
End With

Is there a constant I can ue instead of the text "Bad"?

Thanks

Wouter
0
Wouter
3/17/2010 8:10:42 PM
excel.programming 6508 articles. 2 followers. Follow

10 Replies
813 Views

Similar Articles

[PageSpeed] 41

Styles have both Name and Local name properties, so I *think* the English 
name should work in all language versions. I can't be sure as I only have 
English, but try this -

Dim sty As Style
For Each sty In ActiveWorkbook.Styles
Debug.Print sty.Name, sty.NameLocal
Next

Regards,
Peter T


"Wouter HM" <wouter.magre@sogeti.nl> wrote in message 
news:1efb58e1-e5e4-47bb-8b3a-8eb95a3fc1c8@b30g2000yqd.googlegroups.com...
> Hi there,
>
> I am using Excel 2007, the Ducth version.
> I have created a workbook with macros, partly recorded.
> In one of the I am using some code like
>
> With ActiveCell
>   If .Value < gdblNormValue Then
>      .Style = "Ongeldig"
>   End If
> End With
>
> In the English version it shoul be
>
> With ActiveCell
>   If .Value < gdblNormValue Then
>      .Style = "Bad"
>   End If
> End With
>
> Is there a constant I can ue instead of the text "Bad"?
>
> Thanks
>
> Wouter 


0
Peter
3/17/2010 8:39:56 PM
Hi Peter,

It looks like you code only shows differences beteen .Name
and .NameLocal for the numeric styles
Comma
Comma [0]
Currency
Currency [0]

never the less thank for your effort so far.

Wouter
0
Wouter
3/18/2010 8:28:32 PM
Are you saying in your Dutch system you cannot do this

Set sty = Activeworkbook.Styles("Bad")

Regards,
Peter T


"Wouter HM" <wouter.magre@sogeti.nl> wrote in message 
news:091a12e3-64d8-4617-bc19-e754190da209@33g2000yqj.googlegroups.com...
> Hi Peter,
>
> It looks like you code only shows differences beteen .Name
> and .NameLocal for the numeric styles
> Comma
> Comma [0]
> Currency
> Currency [0]
>
> never the less thank for your effort so far.
>
> Wouter 


0
Peter
3/18/2010 9:37:31 PM
Hi Peter,

I am using something like

For Each rngLoop in Range("MyRange").Cells
   If rngLoop.Value < 0.7 Then
      rngLoop.Style = "Bad"
   End If
Next

In My Dutch version I get an error on "Bad" while compiling.


Wouter
0
Wouter
3/19/2010 5:01:45 PM
Can you do this -

dim sName as string

sName = ""
on error resume next
sName = activeworkbook.styles("Bad").NameLocal
on error goto 0

if len(sName) then
'code
rngLoop.Style = sName


Also, what do you get with this

with activeworkbook.styles("Ongeldig")
debug.? .Name, .NameLocal
end with

and can you do this
Set sty = activeworkbook.styles("Bad")

Regards,
Peter T


"Wouter HM" <wouter.magre@sogeti.nl> wrote in message 
news:7cb92d90-39f0-430a-8021-c6f92077382b@f8g2000yqn.googlegroups.com...
> Hi Peter,
>
> I am using something like
>
> For Each rngLoop in Range("MyRange").Cells
>   If rngLoop.Value < 0.7 Then
>      rngLoop.Style = "Bad"
>   End If
> Next
>
> In My Dutch version I get an error on "Bad" while compiling.
>
>
> Wouter 


0
Peter
3/19/2010 5:12:26 PM
Hi Peter,

Using your code:

sName is an empty string ("")

In the direct pane I get twice Ongeldig

On the Set sty = .... I get Error (Subscrip Out of Range)

Regards,
Wouter
0
Wouter
3/19/2010 5:45:00 PM
That's a shame, OK I will enquire further.

Regards,
Peter T

"Wouter HM" <wouter.magre@sogeti.nl> wrote in message 
news:574c4a38-dba7-4b63-8735-781e17321f1a@b7g2000yqd.googlegroups.com...
> Hi Peter,
>
> Using your code:
>
> sName is an empty string ("")
>
> In the direct pane I get twice Ongeldig
>
> On the Set sty = .... I get Error (Subscrip Out of Range)
>
> Regards,
> Wouter 


0
Peter
3/19/2010 5:56:53 PM
Finally this workaround suggested by Ron de Bruin should work

Built-in style names update to the current local names in cells containing 
the styles. Apply your style to some cell, it could be on a hidden sheet or 
even on a sheet in an addin.

StyleName  = ThisWorkbook.Worksheets(1).Range("A1").Style
myRange.Style = StyleName

Ron said he will probably update his International page to include a note 
about this

http://www.rondebruin.nl/international.htm

Regards,
Peter T



"Peter T" <peter_t@discussions> wrote in message 
news:OIlII24xKHA.3560@TK2MSFTNGP02.phx.gbl...
> That's a shame, OK I will enquire further.
>
> Regards,
> Peter T
>
> "Wouter HM" <wouter.magre@sogeti.nl> wrote in message 
> news:574c4a38-dba7-4b63-8735-781e17321f1a@b7g2000yqd.googlegroups.com...
>> Hi Peter,
>>
>> Using your code:
>>
>> sName is an empty string ("")
>>
>> In the direct pane I get twice Ongeldig
>>
>> On the Set sty = .... I get Error (Subscrip Out of Range)
>>
>> Regards,
>> Wouter
>
> 


0
Peter
3/20/2010 6:51:41 PM
> Ron said he will probably update his International page to include a note 
> about this

Done, see
http://www.rondebruin.nl/international.htm#Style

-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



"Peter T" <peter_t@discussions> wrote in message news:OZvda5FyKHA.1548@TK2MSFTNGP02.phx.gbl...
> Finally this workaround suggested by Ron de Bruin should work
> 
> Built-in style names update to the current local names in cells containing 
> the styles. Apply your style to some cell, it could be on a hidden sheet or 
> even on a sheet in an addin.
> 
> StyleName  = ThisWorkbook.Worksheets(1).Range("A1").Style
> myRange.Style = StyleName
> 
> Ron said he will probably update his International page to include a note 
> about this
> 
> http://www.rondebruin.nl/international.htm
> 
> Regards,
> Peter T
> 
> 
> 
> "Peter T" <peter_t@discussions> wrote in message 
> news:OIlII24xKHA.3560@TK2MSFTNGP02.phx.gbl...
>> That's a shame, OK I will enquire further.
>>
>> Regards,
>> Peter T
>>
>> "Wouter HM" <wouter.magre@sogeti.nl> wrote in message 
>> news:574c4a38-dba7-4b63-8735-781e17321f1a@b7g2000yqd.googlegroups.com...
>>> Hi Peter,
>>>
>>> Using your code:
>>>
>>> sName is an empty string ("")
>>>
>>> In the direct pane I get twice Ongeldig
>>>
>>> On the Set sty = .... I get Error (Subscrip Out of Range)
>>>
>>> Regards,
>>> Wouter
>>
>> 
> 
> 
0
Ron
3/21/2010 8:42:18 PM
Both Peter an Ron,

Many thanks for your help.

I will use Peters idea.

Regards,

Wouter
0
Wouter
3/22/2010 5:26:38 PM
Reply:

Similar Artilces:

How do I set up a metatag for google site recognition?
Google is requesting that I include a meta tag in my code. I can't figure out how to get to the actual code to do this. Publisher webs are not designed to directly edit the source code. If I understand what you are asking and if memory serves Google offers the alternative of uploading a text file to your root directory instead of the meta tag. If you have a follow up question or other questions about Publisher webs, then in the future please post in the web group and we will try to help you there: news://msnews.microsoft.com/microsoft.public.publisher.webdesign or http...

How to set the column title in a listbox from Excel
any help here? You can do it two ways. 1.) Include a header in your RowSource Range. 2.) You can put Lablels just above your ListBox to label your ListBox columns. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "V Hoang" wrote: > any help here? If ColumnHeads is set to true in the properties window, then the header row should not be included in the RowSource or ListFill range. VBA will automatically include the row above the ListFill range as a header if the ColumnHeads property is set to true. "Ryan H&q...

Advanced Search Criteria
Just an FYI, since there doesn't seem to be a solution posted anywhere on the web, at least that I could find. If you want to search using dynamic dates, at least in the case of finding items older than a certain relative date, such as more than 30 days old, I used the following criteria successfully in Outlook 2007: Advanced Find > Advanced (tab) > Field: Sent Condition: On or after Value: 30 days ago ...

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

How to set Excel to open a new window for each application
When I choose a spreadsheet to open, I want it to open a new window. This used to happen but now opens in the same instance, just hiding my original spreadsheet. How to I get it back to previous settings? Tools-->Options, View tab. Check Windows in Taskbar. ************ Anne Troy www.OfficeArticles.com "Luke" <Luke@discussions.microsoft.com> wrote in message news:052E3C3A-8A1A-4EB4-BE2D-B6E09D4D247F@microsoft.com... > When I choose a spreadsheet to open, I want it to open a new window. This > used to happen but now opens in the same instance, just hiding my ori...

change the "online services" account setting
Hello, I understand that once I've completed setting up online services for an account, I can stop them from "management online services" by clicking on the bank link. However, if I started setting up online services for an account, but never completed it, the "stop online services" option does not appear for the bank. Instead of using online services, I'm logging in to the bank's site and downloading transactions and importing them into MM. In this case, in the Account List page, under the "To Review" column, a message that reads "Pro...

Sheet range reference
I have a workbook with a number of sheets, let's say named sheet01, sheet02 ......sheet10. These sheets are of the same structure and are representing some development in time. So now I would like to make a chart (or separate table) the where the X coordinate is the sheet number and the Y some cell on each of the sheets. Is this possible with a simple reference like the ranges within the same chart or do I really have to create a macro or do it by hand? Jens. Each series must reside on a single sheet. You can create a summary sheet which has something like: A1: =Sheet1!A1 A2: ...

printer security settings in registry from MFC or Win32API
Hi to all, I am trying to make a service for controlling print jobs for Win NT/2000/XP platform. I realized it would be very convenient to set the following printer security settings for USERS or Everyone Groups: Allow Print, Deny manage documents. I saw that it is possible to do that for 2000 and XP, but I did not find anything about setting Deny manage documents for NT. So, my first question is: Is it possible to set Deny manage documents for NT 4.0 in some other manner?? Later, I realized that registry value KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Print\Printers\<printe...

Setting browser to respond to hyperlinks in e-mails?
Is there a setting to select the browser that opens when a hyperlink is clicked on in an e-mail or does it default to the "default browser" in Win XP? My problem is that when I click on a hyperlink both Firefox and IE(ver:7) open that link. TIA "Pegleg" <Pegleg@usnavyret.mil> wrote in message news:e9v0c396q9k64smer9fptjj4omtmm2m31t@4ax.com... > Is there a setting to select the browser that opens when a hyperlink is > clicked on in an e-mail or does it default to the "default browser" in > Win XP? > > My problem is that when I click on a hy...

Icon sets - conditional formatting
How do I get Excel 2003 to do this? I want to be able to add increase and decrease arrow icons to a column of numbers . Thx Upgrade to Excel 2007 or see these products; http://www.ozgrid.com/excel-add-ins/mini-charts-excel.htm http://www.ozgrid.com/excel-add-ins/spark-maker.htm -- Regards Dave Hawley www.ozgrid.com "Lou" <Lou@discussions.microsoft.com> wrote in message news:37AE35EE-B3C4-46B2-98F2-8A0B9627E12D@microsoft.com... > How do I get Excel 2003 to do this? > > I want to be able to add increase and decrease arrow icons to a colum...

Still sending emails internally.
The situation . . I had two domains set-up to receive emails via exchange, I have transferred one domain out to a hosted service, when I send emails internally the emails are being delivered internally which is wrong I need them to hit the outside world instead of being delivered internally how can I achieve this . . Thanks JON Can you provide more details? Sounds like your server still thinks it's hosting the domain you've tried to offload. "jonnycool" <jonnycool@discussions.microsoft.com> wrote in message news:BC071409-0316-41A1-A2C2-C2FA5AB8AC66@microsoft....

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

Set form field on double click
I am using MS Access 2003. I have a continuous form that is run off of a query "srbScientific Review". The query's criteria is set to [Enter Last Name] in the LastName field. That part works fine. I get my continuous form that shows LastName, DocID, and Title of Document. I would like to double click on the DocID and have the following happen: 1. Open another form "publications" 2. Set the SRBId field to the DocId field that I double clicked on. PLEASE help. I think I need to bookmark, clone.... I'm very confused. As a kick in the right ...

?-Change data series range as data is entered?
I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- isofuncurves ---...

total of a range of times
This should be simple for some of you, but not for me. I am a runner, I have 4 ind. cells w/my split times. How do I get a total for the tallied times. I'm far from being experienced but am trying to learn..any and all help will be much appreciated. Thanks, Roger Roger To help you in your quest to "try to learn" I will direct you to Chip Pearson's site so's you can learn just about all you'll ever need for Time Calculations. http://www.cpearson.com/excel/datetime.htm#AddingTimes Gord Dibben Excel MVP On Sat, 13 Nov 2004 15:19:02 -0800, "Roger WG" &...

I'm trying to set up my mail for windows vista, please help set this up for me!
blueljeans@verizon.net is my email before trying to set up my mail through windows, can you help me? Barbara Gibson NEVER post or use your real email address in a public newsgroup or forum! Contact your ISP or email provider to obtain the settings you'll need to access your name@verizon.net account via POP3 [1], then start here: http://windows.microsoft.com/en-US/windows-vista/Windows-Mail-setting-up-an-account-from-start-to-finish ============= [1] Perhaps http://help.yahoo.com/l/us/verizon/mail/yahoomail/pop/pop-08.html ? Barbara Gibson wrote: > [MUNGED]@v...

How do I automatically plot different ranges of data in different.
I need to chart parameters that are a function of three variables in the 2002 version of Excel. I have used the x and y axis to represent 2 variables on an 'xy chart', but require an automatic method to represent the value of the third variable. I have tried the 'bubble-plot', but cannot get sufficient resolution between the data. Is their any way that I may plot the third variable in different colours/shades, that automatically changes for different ranges of the the third variable? THis might help you: http://www.peltiertech.com/Excel/Charts/format.html#CondChart &q...

design sets #2
Under "Design Sets" in "Master Sets" I have set up several things with the "Quadrant" design. It now comes up automatically with the headline I used. How do I change the default headline? ...

Getting the longest lengh in range of cells
I am trying to get the length of the longest cell in a range and use the array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. Is there a way in a formula to determine the longest cell length in a range, or through VBA. Thanks in advance Hi Try this formula array (please amend with last row number): {=MAX(LEN(A1:A7))} HTH Cordially Pascal "Geoff" <gh@bob.com> a �crit dans le message de news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl... > I am trying to get the length of the longest cell in a range and use the > array {=len(a1:a1)}. What happen...

Access 2000:Print settings keep changing
I have created reports and saved the margins and column settings. But, I always get a call from co-workers stating these settings are messed up. These settings will be great for a number of days, and then they get changed. I have often wondered if one of the users is doing an "oops" and resetting those certain reports back to default, or is there a bug that might be causing this? This is a small office and I really don't think this is a malicious event. On Tue, 8 May 2007 20:44:01 -0700, ctuck61 wrote: > I have created reports and saved the margins and column setti...

Select Range
Having trouble selecting several non-contiguous ranges. The spreadsheet I am working with is apprx. 300 rows by 110 columns. The sheet is subtotaled. I need to copy data and move to a summary worksheet. There is a subtotaled section entitled 'Risk'. Within this section, the only data required is Country, Customer, 3Q (orders, revenue, gross margin), and 4Q (orders, revenue, gross margin); columns B, E, AQ, AR, AS, BK, BL, and BM - respectively. The section title is in cell A162 and the data is in A163:CY200. I would prefer to select all of the data from the section at once, but ...

report date range
I have a report that will display data from an inventory transaction history form. There I would enter in the date and several other criteria. After doing so I filter the form to my selection and then print my report. I wanted the date range that is on the form to print on the report. I hvae done this in several other reports and never had a problem until now. I'm getting #NAME. The form is still open upon viewing the report. On the report I have a text box with the logic of: ="Report Date Between: " & [Forms]![frmInvTransactionHistory]![BegtDate] & " and &...

Iterate named range & clear contents
Excel 03 Hi all I have a bunch of named ranges (Insert > Name > Define) which I wan to iterate through, & then delete their contents. I'm trying this : For Each nm in ThisWorkbook.Names nm.ClearContents Next nm can someone please show me the correct way to do this. thanks paul For Each nm In ThisWorkbook.Names On Error Resume Next Range(nm.Name).ClearContents On Error GoTo 0 Next nm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in mes...

In Excell-2000 where do I set format for date?
In Excell-2000 where do I set format for date? In some tables I have it in dd/mm/yy, but in the others mm/dd/yy How do I change mm/dd/yy to dd/mm/yy? On my w2k Pro, Control Paned date format is set to dd/mm/yy yout it does not seem to affect Excel Hi If you select the cells with the dates in mm/dd/yy and go Format>Cells and in the dialog that opens, go for the Number tab, you can select the Date formats and choose whichever one suits or select Custom and type dd/mm/yy into the Type box. This will change the formatting of your dates. Hope this helps! Richard On 28 Jan, 16:34, &q...

Calculating greatest number in a range
This is a little tricky to explain, but hopefully will make sense. A series of data is produced that monitors concurrent users on a system. This is returned every five minutes of every day. This is on a work book which has a month for each tab. Data typically comes in the following, simple form: Wed Nov 26 10:50:01 GMT 2008 0 Wed Nov 26 10:55:00 GMT 2008 0 Wed Nov 26 11:00:00 GMT 2008 1 Wed Nov 26 11:05:00 GMT 2008 1 Wed Nov 26 11:10:00 GMT 2008 1 Wed Nov 26 11:15:00 GMT 2008 1 Wed Nov 26 11:20:00 GMT 2008 1 Essentially, I need a way of differentiating between indivi...