Passing Arguments Between Spreadsheets

[nt180792]

Hello all,

I have two spreadsheets that i'm using for a project. One of them controls the customer end and one the staff end. When a button is clicked on the customer spreadsheet, is it possible to open up the staff spreadsheet and pass along arguments (variables)in the process, to use on the staff spreadsheet?

If so, can someone please inform me on how you would do it.

Thanks in advance,
Nick.

[Richard Buttrey]

Hi Nick,

Yes, when you call another procedure it is sufficient just to name the procedure in the macro. So for instance assuming your procedure for opening staff spreadsheets is called 'OpenStaffSpreadsheet' you would have:

Sub MyFantasticProcedure

'your code

OpenStaffSpreadsheet

'code continued

However when you pass arguments you need to preface the procedure name with 'Call' and include the arguments in brackets at the end of the name. So suppose you wanted to pass say a staff ID number 12345, and a location 'London' you would have the following

Sub MyFantasticProcedure

'your code

Call OpenStaffSpreadsheet(12345,"London")

'code continued

and your OpenStaffSpreadsheet procedure would look like:

Sub OpenStaffSpreadsheet(iStaffID as Integer, stLocation as String)

'your code which can now use the values 12345 and London by specifying
' iStaffId and stLocation e.g.

Range("A1") = stLocation
Range("C1") = iStaffID
 


End Sub

Personally I always prefer to use the Call instruction whether I am passing arguments or not. I find it easier to spot procedure names

HTH