select active cell code

[ryan_law2000]

hey everyone,

i have this code

Private Sub Yellow_Click()
 With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
         
    End With
End Sub

Works great other then once the cell/cells are highlighted it does not select a cell. I want it to select the active cell (or bottom cell if a range is selected) after the cell is colored

 

[ryan_law2000]

It worked perfect when i used a macro assigned to a button. But i changed it to vba code and now it doesnt work.

I guess a solution could be to have the button run a macro, how would i do that?

Sheet name: Tally
Macro Name: Yellow()

 

[ChrisM]

That code should work fine as is, the question is now: how are you kicking off sub "Yellow_Click"?

If you don't tie that code to some event, then it will just sit there and never get run.

 

[ryan_law2000]

it is activated by a command button from active X controls . Is this right?

 

[ChrisM]

sure, so you have something like:

Private Sub CommandButton1_Click()
    Yellow_Click
End Sub

That should work fine. Unless, you have your button code and your Yellow code in different modules, in which case you should move the Yellow code to the same module (which should be a Sheet), or change your Yellow code from Private to Public.

 

[ryan_law2000]

still not working

I have thi in my Module1

Sub Yellow()
'
' yellow Macro
'

'
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

and this in sheet1

Public Sub Yellow_Click()
Yellow_Click
End Sub

all it does is debug and highlight
Yellow_Click under the Public Sub


What am i doing wrong?

 

[ChrisM]

Too much yellow stuff,

Should just be:

Public Sub Yellow_Click()
Yellow
End Sub

because "Yellow" is the name of your sub in the module.

 

[Rick Rothstein]

Public Sub Yellow_Click()
Yellow_Click
End Sub

all it does is debug and highlight
Yellow_Click under the Public Sub

What am i doing wrong?

You have the Yellow_Click event code calling itself. Try changing the Yellow_Click event code to this...

Public Sub Yellow_Click()
  Yellow
End Sub

where you have it call the Yellow macro instead.

 

[ryan_law2000]

Ok, Calling the macro works good other then its still not selecting the cell after.

I have a picture assigned to the macro and it works perfect, colors the cell then selects it.

When i have this Active X button with code to call the macro "yellow" then it colors the cell but does NOT select any cell.

any solutions?

 

[ChrisM]

You haven't written any code to select a cell, so there's no way to expect the macro to select a cell. All your code does is format the current selection. You need to add something like:

Sub Yellow()
'
' yellow Macro
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Selection.Offset(1,0).Select
End Sub