Run VBA Code From a Textbox

[Matthew W]

Hi
I have a bit of an odd query but I have a textbox where a user can type (or
copy and paste) a macro from Word. I want this text to be inserted into my
VBA code at a specific point. Is this possible?

Thanks.

[Stefan Hoffmann]

hi Matthew,

Possible yes, e.g.:

http://www.cpearson.com/excel/vbe.aspx

But this code wouldn't run, cause the object model is different in Access.

mfG
--> stefan <--

[Matthew W]

Hi Stefan
Thanks for your help. I'm not sure if my brain is working correctly but is
it saying that I can merge code into what I have already or something else? I
basically have a gap in my code at the moment where I need the code from the
textbox inserted.

[Stefan Hoffmann]

hi Matthew,

Matthew W wrote:
> Thanks for your help. I'm not sure if my brain is working correctly but is
> it saying that I can merge code into what I have already or something else? I
> basically have a gap in my code at the moment where I need the code from the
> textbox inserted.

I would try this:

Public Sub MyCode()

'..
Call CodeFromTextBox

End Sub

Public Sub CodeFromTextBox()
' This method will be replaced.
End Sub

Then use the code from the link. First delete the method
CodeFromTextBox() then append it.

mfG
--> stefan <--

[Matthew W]

Hi Stefan
Thanks again for that. I have managed to implement half of it but I am
having problems creating a sub routine. Did you say that code wouldn't work
in Access?

Thanks.

[Stefan Hoffmann]

hi Matthew,

Matthew W wrote:
> Thanks again for that. I have managed to implement half of it but I am
> having problems creating a sub routine.

This works just fine:

---
Option Compare Database
Option Explicit

Sub AddModuleToProject()

Dim Project As VBIDE.vbProject
Dim Component As VBIDE.vbComponent
Dim CodeModule As VBIDE.CodeModule

Set Project = Application.VBE.ActiveVBProject
Set Component = Project.VBComponents.Item("Callee")
Set CodeModule = Component.CodeModule

AddProcedureToModule CodeModule

End Sub

Sub AddProcedureToModule(ACodeModule As VBIDE.CodeModule)

Const DQUOTE = """" ' one " character

Dim LineNum As Long

LineNum = ACodeModule.CountOfLines + 1
ACodeModule.InsertLines LineNum, "Public Sub SayHello()"

LineNum = LineNum + 1
ACodeModule.InsertLines LineNum, " MsgBox " & DQUOTE & "Hello
World" & DQUOTE

LineNum = LineNum + 1
ACodeModule.InsertLines LineNum, "End Sub"

End Sub
---

> Did you say that code wouldn't work in Access?

The code in a Word macro is using the Word object model. Take a closer
look at it. It can't work. You may try enclosing it into Word object, e.g.:

With myWord.ActiveWorkbook
End With

But you need to add a dot before each of the Word references:

---
Sub Macro1
Selection.TypeText Text:="test"
Selection.MoveLeft Unit:=wdWord, Count:=1, Extend:=wdExtend
Selection.Font.Bold = wdToggle
End Sub
---

should look like

---
Public Sub CodeFromTextBox(AActiveWorkbook As Object)

With AActiveWorkbook
.Selection.TypeText Text:="test"
.Selection.MoveLeft Unit:=wdWord, Count:=1, Extend:=wdExtend
.Selection.Font.Bold = wdToggle
End With

End Sub
---

after your code injection.

mfG
--> stefan <--

[Matthew W]

Thanks for your help Stefan, I've managed to get it all sorted now!

Matthew