General discussion

Locked

Excel macro works, button doesn't

By news.buist ·
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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel macro works, button doesn't

by donq In reply to Excel macro works, button ...

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??

Collapse -

Excel macro works, button doesn't

by news.buist In reply to Excel macro works, button ...

Poster rated this answer

Collapse -

Excel macro works, button doesn't

by roc180 In reply to Excel macro works, button ...

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

Collapse -

Excel macro works, button doesn't

by news.buist In reply to Excel macro works, button ...

Thanks for your help Neal.......Herb

Collapse -

Excel macro works, button doesn't

by news.buist In reply to Excel macro works, button ...

This question was closed by the author

Back to Windows Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums