Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function & formatting
Jonas,
No you cannot modify format from within a function, simply return a value. Why is CF not sufficient. Is it because it only has 3 conditions? If so, you could use event code liek this Private Sub Worksheet_Change(By Target As Range) Application.EnableEvents = False On Error GoTo ws_exit: If (Not Intersect(Target, Range("colours")) Is Nothing) Then Select Case Target.Value Case "a": Target.Interior.ColorIndex = 3 Case "b": Target.Interior.ColorIndex = 4 Case "c": Target.Interior.ColorIndex = 34 Case "d": Target.Interior.ColorIndex = 35 etc. End Select End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Jonas Caspersson" wrote in message ... Hi, i have a Excel question. Is it possible to from within a user defined function (VBA) modify the cells properties, like .interior.color? This would help since the built-in function 'Conditional formatting' is of no help for my specific demands. What i want is to let the user select a function called for instance 'FormatColor' from the insert function dialog box, and then choose an refernence cell which contains the formats he wants to use. Exampel within VBA module: Function FormatCell (Ref as Range) Application.ActiveCell.Interior.Color = Ref.Interior.Color End Function The user then invokes the funktion via 'insert function - user defined funtion - FormaCell' - Picks the reference cell of chiose. This does not work - I've tried... :o( And by the way, Excel hangs! (i've tried this with Excel XP) Help, please /Jonas Caspersson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function & formatting
Bob,
Thank's I was beginning to doubt my VBA skills... god forgive ;o) The reason why CF doesn't do the job is that the formated condition is'nt linked to the cell.value it self, what i mean is if you apply a lot of CF's and then do a 'sort' the CF is applied to the absolute cells ie. if I applied CF to Cell(1,1) and the do a 'sort' the value of Cell(1,1) ends up at Cell(20,1) but the CF stays at Cell(1,1) and i want the CF to go along with the original cell. Did you get this? But thanks any how "Bob Phillips" skrev i meddelandet ... Jonas, No you cannot modify format from within a function, simply return a value. Why is CF not sufficient. Is it because it only has 3 conditions? If so, you could use event code liek this Private Sub Worksheet_Change(By Target As Range) Application.EnableEvents = False On Error GoTo ws_exit: If (Not Intersect(Target, Range("colours")) Is Nothing) Then Select Case Target.Value Case "a": Target.Interior.ColorIndex = 3 Case "b": Target.Interior.ColorIndex = 4 Case "c": Target.Interior.ColorIndex = 34 Case "d": Target.Interior.ColorIndex = 35 etc. End Select End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Jonas Caspersson" wrote in message ... Hi, i have a Excel question. Is it possible to from within a user defined function (VBA) modify the cells properties, like .interior.color? This would help since the built-in function 'Conditional formatting' is of no help for my specific demands. What i want is to let the user select a function called for instance 'FormatColor' from the insert function dialog box, and then choose an refernence cell which contains the formats he wants to use. Exampel within VBA module: Function FormatCell (Ref as Range) Application.ActiveCell.Interior.Color = Ref.Interior.Color End Function The user then invokes the funktion via 'insert function - user defined funtion - FormaCell' - Picks the reference cell of chiose. This does not work - I've tried... :o( And by the way, Excel hangs! (i've tried this with Excel XP) Help, please /Jonas Caspersson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function & formatting
Jonas,
Do you mean that cell 1 has CF but cell 20p doesn't so if you sort them and 1 ends up at 20 you lose the CF? If so, why not apply the dame CF to 1 thru 20? -- HTH Bob Phillips "Jonas Caspersson" wrote in message ... Bob, Thank's I was beginning to doubt my VBA skills... god forgive ;o) The reason why CF doesn't do the job is that the formated condition is'nt linked to the cell.value it self, what i mean is if you apply a lot of CF's and then do a 'sort' the CF is applied to the absolute cells ie. if I applied CF to Cell(1,1) and the do a 'sort' the value of Cell(1,1) ends up at Cell(20,1) but the CF stays at Cell(1,1) and i want the CF to go along with the original cell. Did you get this? But thanks any how "Bob Phillips" skrev i meddelandet ... Jonas, No you cannot modify format from within a function, simply return a value. Why is CF not sufficient. Is it because it only has 3 conditions? If so, you could use event code liek this Private Sub Worksheet_Change(By Target As Range) Application.EnableEvents = False On Error GoTo ws_exit: If (Not Intersect(Target, Range("colours")) Is Nothing) Then Select Case Target.Value Case "a": Target.Interior.ColorIndex = 3 Case "b": Target.Interior.ColorIndex = 4 Case "c": Target.Interior.ColorIndex = 34 Case "d": Target.Interior.ColorIndex = 35 etc. End Select End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Jonas Caspersson" wrote in message ... Hi, i have a Excel question. Is it possible to from within a user defined function (VBA) modify the cells properties, like .interior.color? This would help since the built-in function 'Conditional formatting' is of no help for my specific demands. What i want is to let the user select a function called for instance 'FormatColor' from the insert function dialog box, and then choose an refernence cell which contains the formats he wants to use. Exampel within VBA module: Function FormatCell (Ref as Range) Application.ActiveCell.Interior.Color = Ref.Interior.Color End Function The user then invokes the funktion via 'insert function - user defined funtion - FormaCell' - Picks the reference cell of chiose. This does not work - I've tried... :o( And by the way, Excel hangs! (i've tried this with Excel XP) Help, please /Jonas Caspersson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined function & formatting
I'ts even worse...
If I would use CF I had to apply different CF's to cell 1 to ~100 different.... Then apply 'sort' in differnt ways. This is hard to get, I know, but the Excel application I'm reffering to has a *LOT* of data that has to be presented with different coloring to get an overview, and applying CF's to all the lines & cols (approx 250 lines times 50 cols) is time consuming and i'd like to do that ONCE. To complicate things even more, the data and format keeps changing all the time, and adding that i want to sort the lines according to my needs constantly excluded the CF function. But thank's, it seems as I have to write a lot of VBA code.... ;o) "Bob Phillips" skrev i meddelandet ... Jonas, Do you mean that cell 1 has CF but cell 20p doesn't so if you sort them and 1 ends up at 20 you lose the CF? If so, why not apply the dame CF to 1 thru 20? -- HTH Bob Phillips "Jonas Caspersson" wrote in message ... Bob, Thank's I was beginning to doubt my VBA skills... god forgive ;o) The reason why CF doesn't do the job is that the formated condition is'nt linked to the cell.value it self, what i mean is if you apply a lot of CF's and then do a 'sort' the CF is applied to the absolute cells ie. if I applied CF to Cell(1,1) and the do a 'sort' the value of Cell(1,1) ends up at Cell(20,1) but the CF stays at Cell(1,1) and i want the CF to go along with the original cell. Did you get this? But thanks any how "Bob Phillips" skrev i meddelandet ... Jonas, No you cannot modify format from within a function, simply return a value. Why is CF not sufficient. Is it because it only has 3 conditions? If so, you could use event code liek this Private Sub Worksheet_Change(By Target As Range) Application.EnableEvents = False On Error GoTo ws_exit: If (Not Intersect(Target, Range("colours")) Is Nothing) Then Select Case Target.Value Case "a": Target.Interior.ColorIndex = 3 Case "b": Target.Interior.ColorIndex = 4 Case "c": Target.Interior.ColorIndex = 34 Case "d": Target.Interior.ColorIndex = 35 etc. End Select End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Jonas Caspersson" wrote in message ... Hi, i have a Excel question. Is it possible to from within a user defined function (VBA) modify the cells properties, like .interior.color? This would help since the built-in function 'Conditional formatting' is of no help for my specific demands. What i want is to let the user select a function called for instance 'FormatColor' from the insert function dialog box, and then choose an refernence cell which contains the formats he wants to use. Exampel within VBA module: Function FormatCell (Ref as Range) Application.ActiveCell.Interior.Color = Ref.Interior.Color End Function The user then invokes the funktion via 'insert function - user defined funtion - FormaCell' - Picks the reference cell of chiose. This does not work - I've tried... :o( And by the way, Excel hangs! (i've tried this with Excel XP) Help, please /Jonas Caspersson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined function - Help | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
User Defined Function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
User defined function | New Users to Excel |