Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 "Marcel Marien" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 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 "Marcel Marien" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 "Peter T" <peter_t@discussions schrieb im Newsbeitrag ... 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 "Marcel Marien" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 Marcel Marien wrote: 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 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 "Marcel Marien" wrote in message ... 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 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 "Marcel Marien" wrote in message ... 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 Marcel Marien wrote: 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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 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 "Marcel Marien" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 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 "Marcel Marien" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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! Marcel Marien wrote: 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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 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 "Marcel Marien" wrote in message ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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.) Dave Peterson wrote: 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! Marcel Marien wrote: 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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 "Dave Peterson" schrieb im Newsbeitrag ... 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 Marcel Marien wrote: 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 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 "Marcel Marien" wrote in message ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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 "Dave Peterson" schrieb im Newsbeitrag ... 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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
re-naming styles
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.) Marcel Marien wrote: 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 "Dave Peterson" schrieb im Newsbeitrag ... 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! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
some unknown Cell styles are shown in cell styles | Excel Worksheet Functions | |||
Chart Styles | Charts and Charting in Excel | |||
Deleting styles | Excel Discussion (Misc queries) | |||
Excel Cell Styles - Quick Styles? | Excel Discussion (Misc queries) | |||
Redefining Styles | Excel Programming |