Control boxes in excel

[funkmonsteruk]

I'm quite new to automating excel tasks with visual basic and have previously been using recorded macros and macro buttons, however i now want to progress to using control boxes and am having trouble with codeing. I have tried recording a macro to change the interior of a selected cell and pasting the code into a control button command, but this doesn't seem to work - any sudgestions.

 

[pfan]

I have copied recorded macros to command buttons in the past and been very successful.

Is the button on the same worksheet, a form, etc?

If so, just make sure that your code is in the right spot (ex: if the button is on sheet1, then you have to have the code under sheet1 in VBA).

When you say change the interior of a cell - do mean change the fill color?

This code worked for a command button on sheet1 changing the fill color - compare it to your code:

Private Sub CommandButton1_Click()
Range("E1&quot.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

 

[SkipVought]

Hi,
1. You can use controls directly on sheets or in UserForms. There are alot of similarities.

2. Here's you you might change the interior color of the active cell to Green...

Private Sub CommandButton2_Click()
    ActiveCell.Interior.ColorIndex = 10
End Sub

hope this helps
Skip,
metzgsk@voughtaircraft.com

 

[CDN]

And here's one that highlights every cell where the cursor is

[tt]Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If

Target.Interior.ColorIndex = 19

Set OldCell = Target


End Sub


[/tt]

 

[CDN]

And here's one that highlights every cell where the cursor is

[tt]
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If

Target.Interior.ColorIndex = 19

Set OldCell = Target


End Sub
[/tt]