Alt+F11, writing macros and assigning to a button

[CousinExcel]

Hi,

Recently KevHardy asked a question with a very nice text and
Master Mike H. (who can also inititate chemical reactions in Excel when
needed I believe) has given the solution, effective, easily understandable as
always.
(It was on Feb 11 or 12).

The answer was a macro like:
"Private Sub CommandButton1_Click()
If Selection.Rows.Count > 1...

In that answer
- Alt+F11: I learned it. Then should I say insert MODULE or different ?
- Then, I inserted the 'command button' on the worksheet, okay. Right click
the 'command button, select 'Assign Macro' then I could not see the macro I
wrote ? (I could run it by copying, pasting etc., a long way).

Additional question, can I find the list of commands like
"Selection.Rows.Count" etc. somewhere in the internet.

Thanks and regards,

Cousin Excel

 

[Dave Peterson]

With a macro name like Commandbutton1_Click, this means that the commandbutton
was added from the Control toolbox toolbar.

There is another button on the Forms toolbar that looks the same (kind of), but
behaves differently.

When you use the commandbutton from the control toolbox toolbar, you don't
assign a macro. You can just doubleclick on that commandbutton and be taken to
the worksheet module (not a general module) where the code would be locacted.

When you use the button from the Forms toolbar, then you can assign a macro to
this button that exists in a General module.

There are lots of places to learn about excel macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

And if you like reference books...

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

Professional Excel Development by Stephen Bullen, Rob Bovey, John Green is
pretty advanced, but very good.

See if you can find them in your local bookstore/internet site and you can
choose what one(s) you like best.

 

[JLatham]

[Alt]+[F11], as you've seen, opens the Visual Basic for Applications Editor
(VBE).

Typically you then use Insert --> Module to create an empty code module to
copy and paste code provided here into.

But that's not always the case! There are other places that code can reside:
Class Modules: very rarely used in these forums.

UserForms: again, not used too often in these forums

Worksheet code modules - you can tell when code is intended for a worksheet
because it will normally start with a statement like
Private Sub Worksheet_event() where 'event' is an event that can be
recognized by the worksheet such as Activate, Deactivate, Change,
SelectionChange and BeforeDoubleClick.

The Workbook code module - you can tell when code is intended for a workbook
because it will normally start with a statement like
Private Sub Workbook_event() where 'event' is an event that can be
recognized by the workbook such as _Open, _BeforeClose, _BeforePrint

To get to the code modules for the workbook and worksheets, open the VBA
Project window in the VBE by pressing the [F4] key if it is not visible.
Expand the list of 'Microsoft Excel Objects' in that window. To view the
Workbook's code module, double-click on the 'ThisWorkbook' object in the
list. To view a specific worksheet's code module, double-click on the
desired worksheet name entry.

Now for your problem with the Command button code. If you used the command
button from the Control Toolbox, the code needs to go into the Worksheet code
module for the sheet that the button was placed onto.

If you had used the command button from the Forms toolbar, you could have
right-clicked it and chosen your macro from a list of available macros.

The reason the code you were given did not appear in the list of available
macros is because of the word "Private " at the beginning of its definition.
When a Sub is identified as Private, it does not appear in that list, nor
does it appear in the list of macros using Tools --> Macro --> Macros.

Bottom line: delete the code in the regular code module after copying and
pasting it into the appropriate worksheet code module and things should work
for you. There may already be a code stub for the Command button in that
module with nothing but
Private Sub CommandButton1_Click()

End Sub
in it, just paste your code over that.

Hope this helps.