How do I call VBA subs from different spreadsheet from code behind form?
 

Inherited existing application, VBA validation subs are trigger from
Worksheet_Change and/or Workbook_SheetSelectionChange. Change subs call
subs from separate worksheet which error when called from Code Behide Form.

Form added to allow user input into multiple spreadsheets.

How do I call VBA subs from different spreadsheet from code behind form?

clhamilton

How do I call VBA subs from different spreadsheet from code behindform?
 

I'm not sure if this is what you mean, but you can use application.run to call
procedures in other workbook projects.

Saved from a post for a similar question:

Dim pWkbk as workbook
set pwkbk = workbooks("Personal.xls")
application.run "'" & pwkb.name & "'!macronamehere", "parm1", "parm2"

or if you're returning a value from a function:

dim res as string 'or variant or long or ...
res = application.run("'" & pwkb.name & "'!macronamehere", "parm1", "parm2")

You could also create a reference to this personal.xls workbook and call it just
like it was built into excel.

Tools|references
(but give the personal.xls's project a nice unique name (not VBAProject).

ps.

If you're using a function living in personal.xls inside a cell:
=personal.xls!functionnamehere(a1,b1,c1)

or save the file as an addin (*.xla) and use it in the cell like it's built into
excel:
=functionnamehere(a1,b1,c1)




clhamilton wrote:

Inherited existing application, VBA validation subs are trigger from
Worksheet_Change and/or Workbook_SheetSelectionChange. Change subs call
subs from separate worksheet which error when called from Code Behide Form.

Form added to allow user input into multiple spreadsheets.

How do I call VBA subs from different spreadsheet from code behind form?

clhamilton

--

Dave Peterson