Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

Now we come to the tricky bit... Which I hope isn't coz I've a feeling I'm

missing something simple here.

I'm changing the ColumnWidth value of a cell in a loop.

Every time I change tha value I do an ActiveSheet.Calculate.

In another cell I have the function below.

The value in the cell with the function isn't being updated!

Any ideas why??

I have screenupdating set explicitly to True.

Function fncColumnWidth(Optional spUnits As Variant)

Dim slUnits As String

If IsMissing(spUnits) Then

slUnits = ""

Else

slUnits = UCase(spUnits)

End If

Select Case slUnits

Case ""

fncColumnWidth = Range(Application.Caller.Address).ColumnWidth

Case "PTS"

fncColumnWidth = Range(Application.Caller.Address).Width

Case "INCHES"

Case "CM"

Case "MM"

fncColumnWidth = Range(Application.Caller.Address).Width *

Sheets("Conversion").Range("C2").Value

End Select

'*********

End Function

I'm having trouble understanding your explanation. There is no

ActiveSheet.Calculate in the code you've quoted.

> I'm changing the ColumnWidth value of a cell in a loop.

> Every time I change that value I do an ActiveSheet.Calculate.

> In another cell I have the function below.

Do you mean the value of the cell or the width of the cell? Changing the

width of a cell will not make it "dirty". Therefore it won't necessarily

participate in a calculate. (Calculate only recalculates what needs it.)

In your function, one of the Select legs has

Range(Application.Caller.Address).Width *

Sheets("Conversion").Range("C2").Value

and the other legs are similar, but simpler.

These will do what you expect them to do - i.e. they will use the current

values of those items when the function runs.

But is the function running?

A change to either of those two cells will not trigger the running of the

function. It will only run if the cell that includes the function call is

directly referencing a cell whose value has changed - e.g. the cell that

provides spUnits.

We probably need more information, though.

You can work around this:

add another optional parameter to your function - you don't need to use it!

where the function is referenced in the spreadsheet enter a cell reference

which will change as the second parameter - like

fncColumnWidth(MM,A1:Z999)

then the function will fire every time a cell in the range changes.

True but ...

Changing the width of a column isn't a change to the cell, so the function

won't be called merely for that.

OK.

So...

we need to have a cell with the formula =CELL("width",F1) (assuming col F is

the one being altered). Then we make the formula additional parameter refer

to this cell.

The cell can have white text if you Lisa does not want the width shown.

Didn't find what you were looking for?
Find more on 2003 - ActiveSheet.Calculate Problem
Or get search suggestion and latest updates.

Related Topics:

- Activesheet.Paste method gives error
- 2003 Match() Problem
- 2003 - Problem with dates
- Find the name of the pivot table on the activesheet
- Find the name of the pivot table on the activesheet
- Forcing Excel 2003 to open file when Excel 2007 also installed
- Changing Tab Color Excel 2003
- Office 2003 Subtotaling Issue
- VBE Missing References in Excel 2003
- XL 2003 - Precision printing
- Personal.xls Workbook Version 2003
- File formats ( Excel 2003 & Excel 97 ) ?
- Excel 2003
- Convert code from excel 2000 vba to excel 2003 vba
- Excel 2003 compatibility pack
- 2003 - Userform combobox behaviour
- Conditional Formatting in Access 2003
- 2003 - Moving to a formula address
- Excel 2003 - Looking at jpgs on the web
- Select Duplex Printing-Excel 2003
- 2003 - Setfocus on userform
- 2003 - Rounding
- 2003 - TEXT and VALUE functions
- 2003 - drawing shapes
- jswdp in windows server 2003