Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formatting not applied
I have a weird cell formatting problem in Excel 2002 SP3 and Excel
2003 SP1. If I call my formatting procedure from code on Sheet1 directly, the formatting is applied. However, if I call the same procedure from a function in Module1 the formatting procedure executes, but the formatting is not applied. A Google search turned up the following, but this is not really helpful as the poor guy gave up without finding a solution. http://forums.techguy.org/developmen...ormat-not.html Snippets from my code are as follows: Microsoft Excel Objects / Sheet1: Public Sub BoldItem(row, col As Integer) With Worksheets(1) .Cells(row, col).Font.Bold = True End With End Sub Private Sub CommandButton1_Click() Call BoldItem(15, 1) End Sub I click CommandButton1 and the text in cell A15 appears bold. Hooray it works. Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) .... Call .BoldItem(row, 1) .... End With End Function The procedure BoldItem executes when called, but no bold effect is visible on my text. I'm confused. I tried to trick Excel into thinking that the call to BoldItem was coming from code on Sheet1: Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) .... Call .CommandButton1_Click .... End With End Function Alas BoldItem executes, but has no effect. Any ideas are most welcome. Craig. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formatting not applied
Functions are not designed to write data to cells. You return values to the
cells. Sometimes yoou can get functions to do things they are not suppose tto do. Functions will not allow you to activate/select another workbook or another worksheets. " wrote: I have a weird cell formatting problem in Excel 2002 SP3 and Excel 2003 SP1. If I call my formatting procedure from code on Sheet1 directly, the formatting is applied. However, if I call the same procedure from a function in Module1 the formatting procedure executes, but the formatting is not applied. A Google search turned up the following, but this is not really helpful as the poor guy gave up without finding a solution. http://forums.techguy.org/developmen...ormat-not.html Snippets from my code are as follows: Microsoft Excel Objects / Sheet1: Public Sub BoldItem(row, col As Integer) With Worksheets(1) .Cells(row, col).Font.Bold = True End With End Sub Private Sub CommandButton1_Click() Call BoldItem(15, 1) End Sub I click CommandButton1 and the text in cell A15 appears bold. Hooray it works. Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) .... Call .BoldItem(row, 1) .... End With End Function The procedure BoldItem executes when called, but no bold effect is visible on my text. I'm confused. I tried to trick Excel into thinking that the call to BoldItem was coming from code on Sheet1: Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) .... Call .CommandButton1_Click .... End With End Function Alas BoldItem executes, but has no effect. Any ideas are most welcome. Craig. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formatting not applied
I'm using Excel 2003 SP2.
I tested your code and it worked for me. the tested codes are below and run boldtest. Are you sure you are on worksheets(1)? Microsoft Excel Objects / Sheet1: Public Sub BoldItem(row, col As Integer) With Worksheets(1) .Cells(row, col).Font.bold = True End With End Sub Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) Call .BoldItem(row, 1) End With ItemNo = "unknown" End Function Sub boldtest() Call ItemNo(ActiveCell.row, 1) End Sub keizi wrote in message oups.com... I have a weird cell formatting problem in Excel 2002 SP3 and Excel 2003 SP1. If I call my formatting procedure from code on Sheet1 directly, the formatting is applied. However, if I call the same procedure from a function in Module1 the formatting procedure executes, but the formatting is not applied. A Google search turned up the following, but this is not really helpful as the poor guy gave up without finding a solution. http://forums.techguy.org/developmen...ormat-not.html Snippets from my code are as follows: Microsoft Excel Objects / Sheet1: Public Sub BoldItem(row, col As Integer) With Worksheets(1) .Cells(row, col).Font.Bold = True End With End Sub Private Sub CommandButton1_Click() Call BoldItem(15, 1) End Sub I click CommandButton1 and the text in cell A15 appears bold. Hooray it works. Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) ... Call .BoldItem(row, 1) ... End With End Function The procedure BoldItem executes when called, but no bold effect is visible on my text. I'm confused. I tried to trick Excel into thinking that the call to BoldItem was coming from code on Sheet1: Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) ... Call .CommandButton1_Click ... End With End Function Alas BoldItem executes, but has no effect. Any ideas are most welcome. Craig. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formatting not applied
On 7 Aug, 14:20, "kounoike" wrote:
I'm using Excel 2003 SP2. I tested your code and it worked for me. the tested codes are below and run boldtest. Are you sure you are on worksheets(1)? Microsoft Excel Objects / Sheet1: Public Sub BoldItem(row, col As Integer) With Worksheets(1) .Cells(row, col).Font.bold = True End With End Sub Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) Call .BoldItem(row, 1) End With ItemNo = "unknown" End Function Sub boldtest() Call ItemNo(ActiveCell.row, 1) End Sub keizi wrote in message oups.com... I have a weird cell formatting problem in Excel 2002 SP3 and Excel 2003 SP1. If I call my formatting procedure from code on Sheet1 directly, the formatting is applied. However, if I call the same procedure from a function in Module1 the formatting procedure executes, but the formatting is not applied. A Google search turned up the following, but this is not really helpful as the poor guy gave up without finding a solution. http://forums.techguy.org/developmen...cell-format-no... Snippets from my code are as follows: Microsoft Excel Objects / Sheet1: Public Sub BoldItem(row, col As Integer) With Worksheets(1) .Cells(row, col).Font.Bold = True End With End Sub Private Sub CommandButton1_Click() Call BoldItem(15, 1) End Sub I click CommandButton1 and the text in cell A15 appears bold. Hooray it works. Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) ... Call .BoldItem(row, 1) ... End With End Function The procedure BoldItem executes when called, but no bold effect is visible on my text. I'm confused. I tried to trick Excel into thinking that the call to BoldItem was coming from code on Sheet1: Modules / Module1: Private Function ItemNo(row, col As Integer) As String Dim n As Integer With Worksheets(1) ... Call .CommandButton1_Click ... End With End Function Alas BoldItem executes, but has no effect. Any ideas are most welcome. Craig. Hi Keizi Thanks for your testing. I tested with your code on Excel 2002 SP3 and it works. I then changed things slightly to more closely resemble what I am trying to do by creating a formula in cell A1 with =ItemNo(1,1). The cell displays "unknown" without bold formatting and here we have demonstrated my exact problem. The only difference between your test code and my modified version is that in the latter a formula calls the function. Do you have any further ideas? Thank you Craig |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formatting not applied
On Aug 7, 4:06 pm, wrote:
Hi Keizi Thanks for your testing. I tested with your code on Excel 2002 SP3 and it works. I then changed things slightly to more closely resemble what I am trying to do by creating a formula in cell A1 with =ItemNo(1,1). The cell displays "unknown" without bold formatting and here we have demonstrated my exact problem. The only difference between your test code and my modified version is that in the latter a formula calls the function. Do you have any further ideas? Thank you Craig I haven't been able to get my function, called from a cell, to return a value and modify a format. No worries though - Conditional Formatting came to the rescue. Craig |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formatting not applied
wrote in message
oups.com... On 7 Aug, 14:20, "kounoike" wrote: Hi Keizi Thanks for your testing. I tested with your code on Excel 2002 SP3 and it works. I then changed things slightly to more closely resemble what I am trying to do by creating a formula in cell A1 with =ItemNo(1,1). The cell displays "unknown" without bold formatting and here we have demonstrated my exact problem. The only difference between your test code and my modified version is that in the latter a formula calls the function. Do you have any further ideas? Thank you Craig Hi Craig I've never thought of you were tring to use your function as UDF. UDF can't change the structure of worksheet as Joel said. for some more details, you could find here. http://blogs.msdn.com/frice/archive/...11/153891.aspx keizi |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formatting not applied
On Aug 8, 2:55 am, "kounoike" wrote:
wrote in message oups.com... On 7 Aug, 14:20, "kounoike" wrote: Hi Keizi Thanks for your testing. I tested with your code on Excel 2002 SP3 and it works. I then changed things slightly to more closely resemble what I am trying to do by creating a formula in cell A1 with =ItemNo(1,1). The cell displays "unknown" without bold formatting and here we have demonstrated my exact problem. The only difference between your test code and my modified version is that in the latter a formula calls the function. Do you have any further ideas? Thank you Craig Hi Craig I've never thought of you were tring to use your function as UDF. UDF can't change the structure of worksheet as Joel said. for some more details, you could find here.http://blogs.msdn.com/frice/archive/...11/153891.aspx keizi Hi Keizi Your link helped tremendously, thank you. I now understand Joel's post. Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Standard formatting not being applied to negative percents | Excel Discussion (Misc queries) | |||
Formatting not applied until cell is edited | Excel Discussion (Misc queries) | |||
Determine in VBA if a cell has conditional formatting applied | Excel Programming | |||
can conditional formatting be applied in more then three instance | New Users to Excel | |||
How do I determine if conditional formatting is applied to an exc. | Excel Programming |