Visual Basic Programming on Excel...??? HOW?

Posted by B. Jackson on January 18, 2002 8:13 PM

I teach Visual Basic in high school, and have tried numerous times to place VB objects on Excel spreadsheet, but cannot get them to work. For example, if I place a command button on the form, and code it, I cannot run the application.

When I click on the command button, another pops up. Am I going about this in the wrong manner? Do I need to import a spredsheet into Visual Basic and program that way?

Thanks...

Posted by Joe Was on January 19, 2002 12:38 AM

You can use VB with Excel but mainly as a bridge to other applications much like you would use "C." Excel uses VBA: Visual Basic for Applications, which contains special methods, functions and properties not found in VB, which work directly with Excel and core VB to add custom abilities to Excel.

Try this, Right click the Sheet tab and select "View Code" then type or cut and paste this on to the code sheet.

Sub addCellText()
'This code will simply add text to the selected cell at your cursor location.
'Comment-out to run for range!
ActiveCell.Select

'This code selects a cell range rather than the active cell selection as above! (Note: " ' " comments out the code!)
'Range("E1").Select
ActiveCell.FormulaR1C1 = "This is one Comment!"
'Bold the comment.
ActiveCell.Font.Bold = True
End Sub

Then close the upper of the two close "X's" at the top right of the code screen. You will be back at your worksheet.

Click the menu: View-Toolbars-Forms. A toolbox will be placed on your worksheet, click the button tool, then drag and click the size of button you want on your worksheet. A dialog (Macros) will open, select the macro you just built from the list. Click "OK." Left click the button caption at the far Right, back space the caption out. Then Right click the button and select,"Format Controle." Select "Bold" from "Font Style," select a dark color from "Color." Click "OK." Type "Test" on the button.

Click any cell on the sheet, "X" close the "Form-Tool-Box." Then press the button!

Your VBA code will add text to your selected cell! JSW