my VBA code no work, but Macro does?

[wolf.stalker]

hi all and thanks for your help. for the life of me, i can not figure this out. i am so lost on this, i can't even make a guess as to what's going on...

here is my goal. on sheet 2, i am currently in cell A65. I want to move to cell A1

here is my CODE i wrote for such a simple tast that is run through a command button on sheet 1...

Option Explicit
Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
Range("A1").Select
End Sub

and here is the macro i built to do the same thing...

Option Explicit
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/9/2008 by William
'
'
Sheets("Sheet2").Select
Range("A1").Select
End Sub



the macro runs perfect and does it's job...my command button however gives me "Run-time 1004....select methond of range baka baka baka

what the hell?:dunno

-wm

[shamsam1]

in this u looking for

Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
Sheet2.Range("A1").Select
End Sub

[wolf.stalker]

i have not tried that but if you look at the code i wrote and the code microshaft wrote for the macro, you will notice it's the same! i just don't understand why my code does not work.

i bring this up because i am having the same issue with other things i am working on, but this is a simple example of my problem.

[Bob Phillips]

Range A1 still refers to the commandbutton sheet which is not active then.

TRy



Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1").Select
End Sub


oe



Private Sub CommandButton1_Click()
Application.Goto Sheets("Sheet2").Range("A1")
End Sub

[wolf.stalker]

i still dont get it. let me go one step further...

i am building a new workbook and i am writting the same code as i did in the old one. i have written the code the same for each book. the old one works, the new one does not. NOTHING is different! they are both being called by a command button on a different sheet, and the code is exactly the same. when i run it in the old workbook, it works fine....the new one gives me the error!

i would be happy if both failed but having one work and one not.....it's is driving me nuts.:banghead:


.
.
.

Worksheets("Alpha").Select
Columns("A:F").ColumnWidth = 10
Columns("G:G").ColumnWidth = 12.5
Columns("H:I").ColumnWidth = 10
Columns("J:J").ColumnWidth = 11.3
Range("A1:J19").Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.Size = 8
Selection.Font.ColorIndex = 0

Range("A10:I19").Select
Selection.ClearContents
Selection.Copy

Range("A10,A45,A80,A115,A115,A150,A185,A220,A255,A290").Select
ActiveSheet.Paste
Range("A1").Select

.
.

is it possible there is an option that was on when i wrote this back in march and now is off?

[shamsam1]

in new work book check with sheet names

else past u r old and new work book,it will be easy to test

[wolf.stalker]

yup basicly did that. sheet names are correct.

for the test i did that and i have same problem.

if you noticed ...

Option Explicit
Private Sub CommandButton1_Click()

Sheets("Sheet2").Select
Range("A1").Select

End Sub

and here is the macro i built to do the same thing...

Option Explicit
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/9/2008 by William
'
'
Sheets("Sheet2").Select
Range("A1").Select

End Sub

everyone has been kind enough to provide me with work-arounds which is great, but i really really really want to know what the &*#$ is going on. i mean, i can simply just do an auto macro and then call it in VBA and it will run, but if i were to just type the same code it dosen't work. WHY??

i have rebooted my machine twice. i have moved to a different pc in my group and same result. so i am left with but one reason....maybe when my IT group patched xp, it somehow re-set a value? i mean how else does this go from working to not working?

sorry for the rant, this is just frustrating as hell.

[Bob Phillips]

As I said to you, when you invoke the commandbutton code, the sheet with the command button on it the implied worksheet. So the line Range("A1").select refers to A1 on the sheet that the command button is on, but that is not active anymore so it fails.

When you do it via a macro, the sheet is selected as before, but because the code is not in a worksheet code module, the activesheet is the implied sheet, so the Range("A1").Select works because the implied worksheet and teh activesheet are the same.

[Bob Phillips]

BTW the way, they are not workarounds, but working code. Workarounds work-around a feature, this is just coding correctly.

[Bob Phillips]

If you need further proof, Run this code in your commandbutton and your macro



Worksheets("Sheet1").Select
MsgBox Range("A1").Address(, , , True)

[david000]

For the first part of the post.

Option Explicit
Private Sub CommandButton1_Click()
Me.Range("a1").Select '<<<<<<<<<<<< if the code is in the Sheet2 module
End Sub