VBScript File - Insert a button into Excel 2007

I was wondering whether or not it is possible to use a VBScript file (i.e. C:\Example.vbs) to dynamically add a macro button to an Excel spreadsheet?  I currently have a script that will inject VBA code into a workbook and then run it.  But, I would very much like to go a step further and have the script create a button which can trigger the same VBA macro later.  Any help would be greatly appreciated.  Below is a shortened example of my current code.

option explicit

dim codemod, codepane, n, q, vba, wb, xl

set xl = createobject("excel.application")
set wb = xl.workbooks.add
xl.visible = true

q = chr(34)
n = chr(13) & chr(10)

vba = _
"Sub Test()" & n & _
"	MsgBox " & q & "Hello" & q & n & _
"End Sub"

set codemod = wb.VBProject.VBComponents.Add(1)
codemod.name = "TempMod31"
set codepane = codemod.CodeModule
codepane.insertlines 1, vba
xl.Run("Test")
wscript.sleep 5000
wb.VBProject.VBComponents.Remove codemod
wb.saved = true

set codemod = nothing
set codepane = nothing
set xl = nothing
set wb = nothing

Open in new window

ru2coolAsked:
Who is Participating?
 
ru2coolAuthor Commented:
Shanan, I was able to find the solution by reviewing your example.  The code below will create a new spreadsheet, add a simple VBA macro to it, create a button in cell C3, and then link the button to the macro -

option explicit

dim height, left, top, width
dim btn, codemod, codepane, n, q, rng, vba, wb, ws, xl

const xlButtonControl = 0, xlVAlignCenter = -4108
const xlHAlignCenter = -4108

q = chr(34)
n = chr(13) & chr(10)

vba = _
"Sub Test()" & n & _
"	MsgBox " & q & "Hello" & q & n & _
"End Sub"

set xl = createobject("excel.application")
set wb = xl.workbooks.add
set ws = wb.worksheets(1)
set rng = ws.range("C3")

left = rng.left
top = rng.top
width = rng.offset(0, 1).left - left
height = rng.offset(1, 0).top - top

set codemod = wb.VBProject.VBComponents.Add(1)
codemod.name = "Module1"
set codepane = codemod.CodeModule
codepane.insertlines 1, vba
set btn = ws.shapes.addformcontrol(xlButtonControl, left, top, width, height)
btn.textframe.horizontalalignment = xlHAlignCenter
btn.textframe.verticalalignment = xlVAlignCenter
btn.textframe.characters.text = "Message"
btn.onaction = "Test"
xl.visible = true
wb.saved = true

set codemod = nothing
set codepane = nothing
set rng = nothing
set xl = nothing
set wb = nothing

Open in new window

0
 
Shanan212Commented:
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 648.75, 96.75, _
        107.25, 33.75).Select
    Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle

    Selection.ShapeRange.TextFrame2.TextRange.ParagraphFormat.Alignment = _
        msoAlignCenter
    Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
    Selection.ShapeRange.TextFrame2.TextRange.Characters.Text = "Sample1"
    Selection.ShapeRange.Name = "SampleTextBox"
    Selection.OnAction = "ExportPay"

Open in new window


^Try that. The last line assigns macro.
0
 
ru2coolAuthor Commented:
Shanan, that's close but I was hoping to create a messagebox style button.  Also, bear in mind that the button will need to be created using a .vbs file and not from the VBA code itself.
0
 
ru2coolAuthor Commented:
I've included my response as the answer to the question because the code accomplishes exactly what I had in mind originally.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.