Macro to VB

Posted by Anthony on June 19, 2001 7:15 AM

Hello to all! I'm looking to assign buttons to perform commands on my worksheet. I'm referring more towards buttons from the control toolbox and not graphic art or hot-spots. Anyway, I've attempted to do this by creating a marcro then copying that code to the button of my choice. Is that possible? Each time I do this, I receive variuos "run time" error messages. What am I missing? Any advice would be greatly appreciated.

Thanks

Anthony

Here's the code, any suggestions?

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 6/19/2001 by Anthony Badalucco
'

'
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

Posted by mseyf on June 19, 2001 10:02 AM

Macro3 Macro Macro recorded 6/19/2001 by Anthony Badalucco '

Are you testing the macro on an empty workbook? if there is no data to print, you will get an
error.

-Mark

Posted by mseyf on June 19, 2001 10:04 AM

You can use the Large() function. If the scores for one person are in B2:Z2 then putting this function into cell C2:

=LARGE($B$1:$Z$1,COLUMN()-1)

and copying over the next 20 columns will return the top 20 scores.

Posted by Anthony on June 19, 2001 11:35 AM

I created the macro on an existing spreadsheet (with data)then I copied that code to the VB module of the command button. I still get the error message. Any suggestions?

Posted by Barrie Davidson on June 19, 2001 12:50 PM

Did you include the "Sub Macro3()" and "End Sub"? If so, you don't need these lines; erase them and your code should work okay.

Regards,
Barrie

Posted by mseyf on June 19, 2001 12:58 PM

Anthony:
is this what the code for the command button looks like?

Private Sub CommandButton1_Click()
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

if not, can you copy it here?

(also, you may want to set the TakeFocusOnClick propery to false)

-Mark

Posted by Anthony on June 19, 2001 1:30 PM

I copied mysef's code and it worked....thank you. Barrie, when I removed the "Sub Macro3()" and "EndSub", I received the folloing message: compile error: wrong number of arguments or invalid property assignment.....can you please explain.

Thanks Anthony

Posted by Barrie Davidson on June 19, 2001 2:11 PM

Anthony, I assumed that you had pasted that exact code in your code for the button leaving your button code looking like this:
Private Sub CommandButton1_Click()

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 6/19/2001 by Anthony Badalucco
'

'
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

End Sub

That's why I suggested removing "Sub Macro3()" and "EndSub".

A lesson for me (again), never assume.

Barrie :)