Getting a return value from a form

[Andrew-R]

Posting yesterday to warn somebody about why they shouldn't use global variables reminded me of a little problem I have.

I have a macro which reads a workbook full of coded data, uses a translation table in another workbook to replace each code with its value and puts the result into a new workbook.

This macro uses a form to show a list of all open workbooks and asks the user to select the one containing the coded data and then reuses the form to ask the user to select the workbook containing the translation table.

The form is identical in both cases, other than the prompt (e.g. the first time it's called the prompt reads, "Please select the workbook containing the coded data", etc.)

This all works fine, but two little things are bugging me...

1. At the moment the prompt text is set directly by the code calling the form, e.g.

Please Login or Register  to view this content.

2. The result from the form (the workbook to be used) is passed back to the calling macro via a global variable.

Both of these issues strike me as being akward and inelegant. What I'm looking for is a way to call the form as if it were a function...

Please Login or Register  to view this content.

Is anything like this possible, or is this just another one of the limitations of VBA?

[romperstomper]

Re 1, I would certainly avoid referring to the control directly from outside the form. Add a property procedure to the form (called, say, Title) and then use that from outside the form. Internally, the procedure can then set whatever control is required, or indeed set the form's caption.
Re 2, you could simply create a function that takes a caption argument and returns a value. The function then shows the form, sets the title, and then reads a return property of the form after it is hidden (I am assuming the form is modal) before unloading it and returning the value.

[Andrew-R]

Thanks for the answers, RS. Aside from the slight niggle that I don't understand either of them they sound perfect.

[romperstomper]

Here's a demo of both principles that may make slightly more sense...

[Andrew-R]

Wow, that's very cool, thanks.

It's almost depressing seeing how much I've got left to learn

[romperstomper]

Have you read PED yet? That'll give you a real idea of what's possible...

[Andrew-R]

PED?

This sentence is only here because "PED?" is too short to be posted. Sorry for wasting your time reading it.

[romperstomper]

PED. Also variously known as "The Bible" or "So you thought you knew all about Excel?".

[Andrew-R]

Dear Santa,

For Christmas I would like...

[romperstomper]

Don't forget to ask for this and this, if you don't already own them.

[Andrew-R]

I don't have any Excel books at all, just vast experience of getting things wrong.

However, my beneficent employer has just agreed to buy a copy of PED for me. Well, at less than 2p/page how could they argue?