command button and keyboard macros do not respond same

[Greg Sovan]

Excel 2000

my earlier post in which i said my vba code would not execute after changing to a different sheet was actually launched with a command button. it would not do anything, not even move to a specific cell

when i wrote the same macro as a keyboard keystroke launch, it worked perfectly.

why is this?

[Guido Geurs]

Is it possible to attach your sheet wuth macros(or a part of it) in Bytes?

[Greg Sovan]

the workbook is 4 mb. can i send it to you? i run AVG constantly.

[Guido Geurs]

You can attach as ZIP in Bytes (max. 5Mb): ZIP it (.xls not allowed) and click on "Go Advanced" - "Manage Attachments" - "Browse" - "Upload"

[Greg Sovan]

file

here's the file. try creating a button that will move the cell pointer from games worksheet to scoresheet worksheet and go to some miscellaneous location like B4.

[Guido Geurs]

The sheet "scoresheet " is protected!
What is the password to unprotect it ?

[Greg Sovan]

protected?

the only sheet protected was "masterbox" which I have now unprotected. also, it did not have a password.

I am uploading the sheet again, this time without the protection.

thanks for looking into this for me.

[Guido Geurs]

The protection in bb3000games3 is still on!
See attachment

[Guido Geurs]

Sorry, my mistake: it's not the protection but the fact that a command in a sheet can't gives direct code to an other sheet.
You have to mention each time the sheet or use "With... end with.
Like:

    Range("BL2").Copy
    Sheets("scoresheet").Activate
    
    ' this is where is breaks down, after it switches to "scoresheet" it will not go to EJ3
    'Move Visiting Pitcher to correct cell
    
'[EJ2].End(xlDown).Offset(0, 1).Select
With Sheets("scoresheet")
    .Range("ej2").Select
    Selection.End(xlDown).Select
    Selection.Offset(0, 1).Select
End With
    End Sub

or:

    Range("BL2").Copy
    Sheets("scoresheet").Activate
    
    ' this is where is breaks down, after it switches to "scoresheet" it will not go to EJ3
    'Move Visiting Pitcher to correct cell
    
Sheets("scoresheet").[EJ2].End(xlDown).Offset(0, 1).Select
'With Sheets("scoresheet")
'    .Range("ej2").Select
'    Selection.End(xlDown).Select
'    Selection.Offset(0, 1).Select
'End With
    End Sub

[Greg Sovan]

Thank you so much. This is how I got it to work.

Private Sub CommandButton16 _Click()
Sheets("scoresh eet").Activate
Sheets("scoresh eet").[EJ1].End(xlDown).Of fset(0, 19).Select
Sheets("scoresh eet").Range(Act iveCell, ActiveCell.Offs et(0, 9)).Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("game"). Activate
ActiveCell.Offs et(0, -6).Select
Range(ActiveCel l, ActiveCell.Offs et(0, 11)).Copy
Range("BF2").Se lect
ActiveSheet.Pas te
Range("BL2").Se lect
Application.Cut CopyMode = False
Selection.Copy
Sheets("scoresh eet").Activate
Sheets("scoresh eet").Range("EJ 1").Select
Selection.End(x lDown).Select
ActiveCell.Offs et(1, 0).Select
ActiveSheet.Pas te
Sheets("game"). Activate
Range("A1").Sel ect
Range("BQ37").S elect
End Sub