Cell formatting not applied

[Craig.Leat]

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/development/137688-vba-excel-cell-format-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.

 

[Joel]

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.

 

[kounoike]

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

 

[Craig.Leat]

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

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

 

[Craig.Leat]

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

 

[kounoike]

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/2004/06/11/153891.aspx

keizi

 

[Craig.Leat]

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/2004/06/11/153891.aspx

keizi

Hi Keizi

Your link helped tremendously, thank you. I now understand Joel's
post.

Craig