• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1437
  • Last Modified:

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

0
ru2cool
Asked:
ru2cool
  • 3
2 Solutions
 
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:
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
 
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now