Hello all.

I am a complete beginner to VB code.

I have created a command button in a worksheet..and have recorded a
macro by the name of mcrCostAccounting.
Basically I want the user to click the command button and then for the
macro to run...
so the user should click on the button and then the user will end up in
another worksheet (this is what the macro basically does!)

how do i do this?

heres my attempt..

Private SubCommandButton1_Click()
GoTo mcrCostAccounting
End Sub



Thanks in advance!!


--
londonchick
------------------------------------------------------------------------
londonchick's Profile: http://www.excelforum.com/member.php...o&userid=24529
View this thread: http://www.excelforum.com/showthread...hreadid=381235

LondonChick,
Where do you have the two subroutines?
The CommandButton1_Click should be in the worksheet module and the
mcrCostAccounting should be in a standard module.

Also make sure the button you created is CommandButton1.
The easiest way to do this is to go into design mode and double click
on the button. It will take you to the VBA editor show you the code
behind that button.

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381235

hmm..Im a bit confused about these modules...
as i said im completely new to VB! I will try and explain what I mean
in regards to my problem..

When I right click on the command button created..I go to its VB code.
This code is displayed within "commandbutton1" and next to it the
"click" function is selected.

I have done nothing with the code really...
Excel automatically takes me to the vb code set in the command button 1
when i right click on it.

am i making sense?

I guess Im trying to figure out what the code would be if I wanted to
run the macro?


--
londonchick
------------------------------------------------------------------------
londonchick's Profile: http://www.excelforum.com/member.php...o&userid=24529
View this thread: http://www.excelforum.com/showthread...hreadid=381235

hmm..Im a bit confused about these modules...
as i said im completely new to VB! I will try and explain what I mean
in regards to my problem..

When I right click on the command button created..I go to its VB code.
This code is displayed within "commandbutton1" and next to it the
"click" function is selected.

I have done nothing with the code really...
Excel automatically takes me to the vb code set in the command button 1
when i right click on it.

am i making sense?

I guess Im trying to figure out what the code would be if I wanted to
run the macro?


--
londonchick
------------------------------------------------------------------------
londonchick's Profile: http://www.excelforum.com/member.php...o&userid=24529
View this thread: http://www.excelforum.com/showthread...hreadid=381235

LondonChick,
It sounds like you have the code for the command button in the
right place.
Try changing the call statement for your mcrCostAccounting macro within
that CommandButton1_Click event to:

Private Sub CommandButton1_Click()
mcrCostAccounting
End Sub

You don't need the GoTo statement in order to get the macro to run.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381235

thank u!! :)
it works now!!


--
londonchick
------------------------------------------------------------------------
londonchick's Profile: http://www.excelforum.com/member.php...o&userid=24529
View this thread: http://www.excelforum.com/showthread...hreadid=381235

Glad to hear that it works for you. Now how about understanding why it
works... (I.e. a little inpromptu training. :) )

Not sure if you have ever programmed in another language before. The fact
that you used the goto statement, indicates to me that you have. VBA has
it's differences compared to other programming languages. Although most
current references refer to the GoTo statement as an evil in todays
programming, it still has it's uses.

What you were trying to do was run/activate a function or a subroutine from
within a subroutine/function. Or in VBA terms, you were trying to CALL the
macro that was written for you.

So the line that said mcrCostAccounting, could have also been written:

Call mcrCostAccounting

For readability and code maintenance, I always put the Call at the beginning
of the command.

One time not to use a Call is when I want to use a result returned from a
function.

Okay, I've talked about functions and subroutines.

Functions (FUNCTION) return results.
Sub routines (SUB) perform actions but do not "return" values.

For example:

Public Function TwoTimesTwoFunc() as integer
TwoTimesTwo = 2*2
End Function

Public Sub TwoTimesTwoSub()
msgbox(2*2)
End Sub

The first one, returns the value of 4
The second one displays a message box with the number 4 in it.

So, I could say
Public Sub ShowResults
dim Value as integer

Value = TwoTimesTwoFunc

msgbox(Value)
Call TwoTimesTwoSub
end sub

At the end of this, Value is equal to 4 and a message box is displayed with
the value of 4
And then another message box is shown that says the number 4, also.

Just a kick start, but sounds like you are making good progress. If you
ever want to learn more you know the place to come back to. :)

"londonchick" wrote:


thank u!! :)
it works now!!


--
londonchick
------------------------------------------------------------------------
londonchick's Profile: http://www.excelforum.com/member.php...o&userid=24529
View this thread: http://www.excelforum.com/showthread...hreadid=381235