Replies

On the subject of clipboard extenders…I use ClipCache and get the same error message in Excel 2000, however it has always copied & pasted the entire selection for me despite the error messages.

Ken

Hi Andrew,

I was copying these *very useful* functions into my personal library when I noticed two typos. So in case anyone else wants to do the same I thought I’d let you know.

Function CellColorCount(fRange As Range, fCol) As Long
Application.Volatile True
Dim Rng As Range
For Each Rng In fRange
 If Rng.Interior.ColorIndex = fCol And Rng.Value  "" Then
  ColorCount = ColorCount + 1
 End If
Next
End Function

Function CellColorSum(fRange As Range, fCol) As Double
Application.Volatile True
Dim Rng As Range
For Each Rng In fRange
 If Rng.Interior.ColorIndex = fCol Then
  ColorSum = ColorSum + Rng.Value
 End If
Next
End Function

The lines reading

ColorSum = ColorSum + Rng.Value
ColorCount = ColorCount + 1

should read

CellColorSum = CellColorSum + Rng.Value
CellColorCount = CellColorCount + 1

I appreciate all the effort you put in here at the Lounge to help out those less knowledgeable like myself.

Ken

P.S. What does Application.Volatile True do?

Hans,

Thanks for pointing me in the right direction. It took me a while to figure out that GetKeyState returns bit 8 on when a button is clicked with the shift key down, and bit 1 gets toggled by the key up event. Once I got that, the rest went well.

Ken

Thanks to Andrew and Rory for their help.

Here’s the final code I’m using. Seems to be working fine.

Option Explicit

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim work As String
Dim i As Long
On Error GoTo Goodbye
work = Target.Formula
If Right(work, 1)  "-" Then Exit Sub
work = Left(work, Len(work) - 1)
If IsNumeric(work) Then
    work = "-" & work
    If Application.FixedDecimal = False Then
        Target.Formula = work
    Else
        Target.Formula = Val(work) * 0.01
    End If
End If
Goodbye:
End Sub

Ken

Thank you Andrew and Rory. I’ll get report back to you on this in a couple of days when I have a chance to try it.

Ken

Andrew, Thanks much for responding. The macro is in the workbook module, and I added the lines you suggested. However, It still changes the cell below the one I just entered. Any more ideas would be appreciated.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Long
Dim work As String

Application.EnableEvents = False
' enter your code here
work = ActiveCell.Formula

If Right(work, 1) = "-" Then
    If IsNumeric(Left(work, Len(work) - 1)) Then
        ActiveCell.Formula = "-" & Left(work, Len(work) - 1)
    End If
End If

Application.EnableEvents = True

End Sub

TIA
Ken

John,

This is a little late, but it works in XL2K. Select all the identical sheets you want to print, then select the cells you want to print on the first sheet. Press Ctrl-P or do File | Print and click the “Print Selection” option button. Print Preview then shows that all the selected sheets will print just that selected area. No need to set a print area at all.

kjk

Geoff,
When you click in the Refers to box are you in Edit Mode?
Check the status bar. If not, press F2.

Hope this is helpful.
Ken

Hi again,

Strange, what you say. After I click on the refers to box in the Define Name Dialog box, my status bar shows that F2 toggles between “Enter” and “Edit”. In the Edit mode I have no problem editing in the Refers To box.

Try this link to read about NotesPad

http://members.nbci.com/_XMCM/newbienet2/N…otespad_32.html%5B/url%5D

I’ve used it for years and think its great.

kjktoo

Here’s what I use to fix trailing negatives. I also use it to fix negatives in angled brackets which I get if I import reports from the AS400 spool file. It also skips dashed lines that I get from those reports.

I’m almost embarrassed. My code isn’t nearly as sophisticated as what I’ve seen so far, but it seems to work OK for me.

Option Explicit
Sub FixNeg()
Dim myCell As Range
Dim i As Long
Dim Work As String

For Each myCell In Selection

    myCell.Value = Trim(myCell.Value)
    
    If Right(myCell.Value, 1) = "-" And Right(myCell.Value, 2)  "--" _
            Or Right(myCell.Value, 1) = ">" Then
        Work = "-"
        For i = 1 To Len(myCell.Value) - 1
            If InStr(".0123456789", Mid(myCell.Value, i, 1)) > 0 Then
                Work = Work & Mid(myCell.Value, i, 1)
            End If
        Next i
        myCell.Value = Work
    End If
    
Next myCell

End Sub

End Sub