Reffering code to command button on diffrent sheets

[BlonTMamba]

Hi

Got a little probleme here

I want my command button on "sheet1" to copy things from "sheet2" and
paste it again in "sheet1". Excel only wants to do this when i put the
code in "ThisWorkbook", because if i put the code in sheet1 it doesnt
seem to know sheet2. But when i put my code in "ThisWorkbook" the
command button looks for its code in sheet1, not in "thisWorkbook",
where i just put it.
How can i tell tho command button to look for its code in
"thisWorkbook" ?

hope You can help me out.

Greetings

 

[Necessitysslave]

create a macro in a module

something akin to

sub copyFromAtoB()

worksheets(2).range("someRange").copy
worksheets(1).range("anotherRange").paste

end sub

Then instead of using the "comand button" from the control toolbox, use
the "button" button from the forms toolbox. This will now ask you which
macro you want to attach.

Hope this Helps

 

[Bob Phillips]

This works fine for me

Private Sub CommandButton1_Click()
Me.Range("A1").Value = Worksheets("Sheet2").Range("A1").Value
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[BlonTMamba]

hey

thanks all for replying!!!

alltough that was not exacly what i meant i learnd some new things
tho

and I just found the function i meant i will show you:

i put the routine for command button 2 (wich i called CB2) in
ThisWorkbook.
than i put the following code for the command button in sheet1:

Private Sub CommandButton2_Click()
Call [ThisWorkbook].CB2
End Sub

that was probally to easy for you to think of i just started
working with excel like this

thanks again

greets

 

[Tom Ogilvy]

Think Bob showed you how to properly structure your code and address Sheet2
from the Sheet1 code module so you don't have to pursue the "Bandaid"
approach you have come up with.

 

[BlonTMamba]

oowww....

hmm okay. well how does that one exactly work then? and why is it more
convenient than mine?

Do u put all the code in sheet 1? or just the one bob showed me?

Greets

 

[Tom Ogilvy]

If you want to address another sheet from a sheet module, you qualify it
with a sheet qualifier

All the code goes in the click event of the command button. You always have
the option to put common functions and common subroutines in a General
module (not the ThisWorkbook Module) so they can be called from anywhere
without special qualification.

The problem you were having is that in a sheet module, an unqualfied
reference to a range implicitely is a reference to the range in the
worksheet that contains the code. You solved this by moving it to the
thisworkbook module where this implicit qualification doesn't occur.
Another option, and probably preferred vice the one you chose, would have
been to move it to a general module where the implicit qualification also
doesn't occur. But the correct solution is to write the code with proper
qualifications and place it in the action event for the control that
triggers it. This has the side affect of being faster if you avoid
selecting and so forth.

Class modules such as thisworkbook, sheet modules and userform modules
should be reserved to contain code associated with events for those objects.
Organization is good practice, supports maintaining code and reduces
unexpected code behavior.

In the end, how you write your code is up to you and your employer, however.