Discussion Locked

Excel macro works, button doesn't

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

If I use this code in an Excel 97 macro it works. If I put it behind a button, it doesen't. I get a Runtime 1004 error - unable to set the Color property of the Font class.

The macro hides all cells with either an "x" or a "y". I would like toget it to work using a button.

Any thoughts?

Dim Row As Integer
Dim Col As Integer
For Col = 1 To 25
For Row = 1 To 100
If Cells(Row, Col).Text = "x" Then
Cells(Row, Col).Font.Color = RGB(255, 255, 255)
ElseIf Cells(Row, Col).Text = "y" Then
Cells(Row, Col).Font.Color = RGB(225, 225, 225)
Else
Cells(Row, Col).Font.Color = RGB(0, 0, 0)
End If
NextRow = 2
Next

....thanks in advance...........Herb

5 replies

by donq

Maybe this will help - maybe not?

In Access I have to tell my code WHICH database, and WHICH recordset when I write any procedure. Your button does all the right stuff - but the code may not know WHICH Excel Workbook and/or WHICH Page???

I think you are close, - also in Access we can simply enter the macro name as the record source property??? Try that??

by roc180

The problem is when you attach a button to your Excel worksheet and run code behind it the focus has to be put on the worksheet not the command button. The error 1004 was refering to the command button font. Use the below code behind your button (CommandButton1):

Private Sub CommandButton1_Click()
Dim intRow As Integer
Dim intCol As Integer

For intCol = 1 To 25
For intRow = 1 To 100
If Cells(intRow, intCol).Text = "x" Then
Range(Cells(intRow, intCol), _
Cells(intRow,intCol)).Select Application.ActiveCell.Font.ColorIndex = 3
ElseIf Cells(intRow, intCol).Text = "y" Then
Range(Cells(intRow, intCol), _
Cells(intRow, intCol)).Select
Application.ActiveCell.Font.ColorIndex = 3
Else
Range(Cells(intRow, intCol), _
Cells(intRow, intCol)).Select
Application.ActiveCell.Font.ColorIndex = 1
End If
Next
Next

End Sub

If you have any other questions let me know.

Neal Martinelli

This discussion is closed to new replies.