Cell working as a button? Cell color control

[Emerogork via OfficeKB.com]

Can I get a cell to work as if it were a button? (Or can I place a button on
a worksheet?)

I have been playing with ActiveCell but have not quite figured how to trigger
it. In VB I could create a Sub Toggle () and run its contents from a button.
Can I program a cell to run a Sub?

Also,: Why doesn't my mouse thumb wheel work when I view code?

And Then: I can change the cell color but how do I change the shade or
crosshatch value using code?

 

[Bernie Deitrick]

Can I get a cell to work as if it were a button?

Yes. You need to use the worksheet's SelectionChange event, and check the cell against the target.
For example, this code will run the macro CellA1 whenever cell A1 is selected.

'In the sheet's codemodule
'Right-Click the sheet tab and select "View Code",
'then paste in the window that appears
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
CellA1
Else
NotCellA1
End If
End Sub

'In a regular codemodule
Sub CellA1()
'Turn off events if you do anything to change a cell
'other than its formatting
MsgBox "Howdy from Cell A1"
With Range("A1").Interior
.ColorIndex = 3
.Pattern = xlLightDown
End With
End Sub

Sub NotCellA1()
'Turn off events if you do anything to change a cell
'other than its formatting
MsgBox "Howdy from NOT Cell A1"
With Range("A1").Interior
.ColorIndex = xlNone
.Pattern = xlNone
End With
End Sub

(Or can I place a button on a worksheet?)

Yes you can, and you can assign a macro to it.

And Then: I can change the cell color but how do I change the shade or
crosshatch value using code?

See the code above. Record a macro to get the desired values...

HTH,
Bernie
MS Excel MVP