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