re-naming styles

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcel Marien - 24 Jan 2007 21:42 GMT

Hi,

can anybody tell me whether and if yes how it is possible to re-name a style
in VBA? I want the style-definition to stay unchanged and just change the
name.

Thanks in advance,
Marcel

Reply to this Message

Peter T - 24 Jan 2007 23:24 GMT

Hi Marcel,

AFAIK you cannot rename a style but you can add a new style "based on" some
cell, eg

With ActiveCell
.ClearFormats
.Style = "OldStyle"
End With
ActiveWorkbook.Styles.Add Name:="NewSyle", Basedon:=ActiveCell

If the idea is to replace existing cells formatted with OldStyle with
NewStyle you'll need to loop all cells in the usedrange of each sheet. Could
be time consuming if you don't know how to limit a search, though there's a
lot you can do to speed up the process. But that's another subject!

Regards,
Peter T

> Hi,
>
[quoted text clipped - 4 lines]
> Thanks in advance,
> Marcel

Reply to this Message

Marcel Marien - 25 Jan 2007 00:16 GMT

Dear Peter,

thanks a lot for your input. It seesm I have to look for a different
solution. Defining a new style and then replacing the old style is too
cumbersome and too much fraught with hazard. (I thought since in WinWord one
can simply re-name a style and Excel comes out of the same stable, so to
say, it might have been programmed according to a similar philosophy).

Greetings,
Marcel

> AFAIK you cannot rename a style but you can add a new style "based on"
> some
[quoted text clipped - 25 lines]
>> Thanks in advance,
>> Marcel

Reply to this Message

Dave Peterson - 25 Jan 2007 00:35 GMT

I don't understand why it would be that difficult to use Peter's suggestion.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myOldStyleName As String
Dim myNewStyleName As String
Dim TestStyle As Style

myOldStyleName = "asdf"
myNewStyleName = "qwer"

Set TestStyle = Nothing
On Error Resume Next
Set TestStyle = ActiveWorkbook.Styles(myOldStyleName)
On Error GoTo 0

If TestStyle Is Nothing Then
MsgBox myOldStyleName & " isn't used in this workbook"
Else
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Style = TestStyle.Name
.Parent.Styles.Add Name:=myNewStyleName, BasedOn:=myCell
TestStyle.Delete
myCell.Clear
End With
End If

End Sub

> Dear Peter,
>
[quoted text clipped - 36 lines]
> >> Thanks in advance,
> >> Marcel

Signature

Dave Peterson

Reply to this Message

Marcel Marien - 25 Jan 2007 00:59 GMT

Dear Dave,

Thank you VERY much for the program (I wouldn't have come up myself with
it).

What is so "difficult" about replacing the styles is, that
1) there are about 100 different styles
2) I want them to have meaningful names
3) I want different people to define own categories and therefore use
self-defined names

My original idea was to have all those categories described in one list and
then run a macro that utilizes the list to re-name the styles accordingly.
Well I'll have to re-think the concept once again.

Marcel

>I don't understand why it would be that difficult to use Peter's
>suggestion.
[quoted text clipped - 73 lines]
>> >> Thanks in advance,
>> >> Marcel

Reply to this Message

Dave Peterson - 25 Jan 2007 01:54 GMT

I'm not sure how you'd handle #3, but you could create a list of the old style
names (A2:Axx) and put the new style names in B2:Bxx and run a macro that loops
through those cells:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim WksList As Worksheet

Set WksList = ThisWorkbook.Worksheets("myList")

With WksList
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
.Offset(0, 2).Value _
= RenameStyles(.Value, .Offset(0, 1).Value, ActiveWorkbook)
End With
Next myCell

End Sub

Function RenameStyles(myOldStyleName As String, myNewStyleName As String, _
Optional myWorkbook As Workbook) As String

Dim myCell As Range
Dim TestStyle As Style

If myWorkbook Is Nothing Then
Set myWorkbook = ActiveWorkbook
End If

Set TestStyle = Nothing
On Error Resume Next
Set TestStyle = myWorkbook.Styles(myOldStyleName)
On Error GoTo 0

If TestStyle Is Nothing Then
RenameStyles = "Failed"
Else
With myWorkbook.Worksheets(1)
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Style = TestStyle.Name
.Parent.Styles.Add Name:=myNewStyleName, BasedOn:=myCell
TestStyle.Delete
myCell.Clear
End With
RenameStyles = "Renamed"
End If

End Function

> Dear Dave,
>
[quoted text clipped - 94 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Marcel Marien - 27 Jan 2007 00:34 GMT

Hello Dave,

I finally managed, to try out the program you suggested. Unfortunately it
operates beyond my present "Excel horizon" and I do not understand all of
it, therefore I can not give you a very detailled feedback.

I filled the cells A2:A6 with existing style names and the cells B2:B6 with
intended new names. The program properly defined the new styles based on the
old styles and then deleted the old styles. However, it did not assign the
new style to those cells that were originally formated with the old style
before deleting the style - and since I can't fully comprehend it, I am not
sure whether it was at all designed to do so. Of course, assigning the new
stlye to the respective cells would be part of what needs to be accomplished
if I want to rename the existing styles.

In any case, thank you very much for your input. I am going to study your
program some more time and maybe I manage to figure more of it out.

best regards,
Marcel

> I'm not sure how you'd handle #3, but you could create a list of the old
> style
[quoted text clipped - 164 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 27 Jan 2007 01:36 GMT

Yep. That's a problem that I didn't think about!

I think the only way around it is to look at every cell in the usedrange of the
worksheet and keep track of the style that was used. Then apply the new style
to those cells.

The good news is that it makes the code much easier. Just change the name of
the style while the loop is running. Then after all the cells on all the sheets
are done, delete all the old names.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim WksList As Worksheet
Dim wks As Worksheet
Dim res As Variant

Set WksList = ThisWorkbook.Worksheets("myList")

With WksList
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each wks In ActiveWorkbook.Worksheets
For Each myCell In wks.UsedRange.Cells
res = Application.Match(myCell.Style.Name, myRng, 0)
If IsError(res) Then
'not on the list, do nothing
Else
myCell.Style = myRng(res).Offset(0, 1).Value
End If
Next myCell
Next wks

'now clean up those old style names
On Error Resume Next
For Each myCell In myRng.Cells
ActiveWorkbook.Styles(myCell.Value).Delete
Next myCell
On Error GoTo 0

End Sub

I think that my other response was interesting at best, but really dumb!

> Hello Dave,
>
[quoted text clipped - 189 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 27 Jan 2007 01:53 GMT

Just some English changes--not to the code. I wrote part of the message, then
changed direction. And didn't update the message.

I think the only way around it is to look at every cell in the usedrange of the
worksheet and change styles if the style matches one in your list.

The good news is that it makes the code much easier. Just change the name of
the style while the loop is running. Then after all the cells on all the sheets
are done, delete all the old styles.

(not important to anyone but me <bg>.)

> Yep. That's a problem that I didn't think about!
>
[quoted text clipped - 239 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Marcel Marien - 27 Jan 2007 03:48 GMT

Hi Dave,

Thank you sooo much! This works wonderfully and pretty fast as well.

I have one more question. All styles in question are defined in such a way
that they ignore the frame format, that is, if they are applied to a cell,
the frame format of the cell does not change. In your re-definition based on
a cell pattern, this element of the style definition is not transported
across. Can you tell me any way how to include it?

(And by the way, your first response wasn't "dumb" at all, it set the stage
for your second response. )

Thanks a lot,
Marcel

> Yep. That's a problem that I didn't think about!
>
[quoted text clipped - 45 lines]
>
> I think that my other response was interesting at best, but really dumb!

Reply to this Message

Dave Peterson - 27 Jan 2007 15:16 GMT

I had trouble running the earlier code this morning. I think that there was a
bug in it. I didn't notice it before.

But this worked ok and kept the border in my simple testing.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim WksList As Worksheet
Dim wks As Worksheet
Dim res As Variant
Dim TestStyle As Style
Dim myNewStyleName As String

Set WksList = ThisWorkbook.Worksheets("myList")

With WksList
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each wks In ActiveWorkbook.Worksheets
For Each myCell In wks.UsedRange.Cells
res = Application.Match(myCell.Style.Name, myRng, 0)
If IsError(res) Then
'not on the list, do nothing
Else
myNewStyleName = myRng(res).Offset(0, 1).Value
Set TestStyle = Nothing
On Error Resume Next
Set TestStyle = wks.Parent.Styles(myNewStyleName)
On Error GoTo 0
If TestStyle Is Nothing Then
wks.Parent.Styles.Add Name:=myNewStyleName, BasedOn:=myCell
End If
myCell.Style = myNewStyleName
End If
Next myCell
Next wks

'now clean up those old style names
On Error Resume Next
For Each myCell In myRng.Cells
ActiveWorkbook.Styles(myCell.Value).Delete
Next myCell
On Error GoTo 0

End Sub

(This is the most I've worked with styles <vbg>.)

> Hi Dave,
>
[quoted text clipped - 61 lines]
> >
> > I think that my other response was interesting at best, but really dumb!

Signature

Dave Peterson

Reply to this Message

Marcel Marien - 25 Jan 2007 00:28 GMT

Hello again,

I just came up with one more related question: Does anybody know how and
where the style-definitions are stored? Maybe it is nevertheless possible to
edit it there and change the style-name...

Marcel

> Hi Marcel,
>
[quoted text clipped - 27 lines]
>> Thanks in advance,
>> Marcel

Reply to this Message