International Range style settings

[Wouter HM]

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

 

[Peter T]

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]

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

 

[Peter T]

Are you saying in your Dutch system you cannot do this

Set sty = Activeworkbook.Styles("Bad")

Regards,
Peter T

 

[Wouter HM]

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

 

[Peter T]

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]

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

 

[Peter T]

That's a shame, OK I will enquire further.

Regards,
Peter T

 

[Peter T]

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

 

[Ron de Bruin]

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

about this

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

 

[Wouter HM]

Both Peter an Ron,

Many thanks for your help.

I will use Peters idea.

Regards,

Wouter